如何将单元格vlookup转换为VBA

目前,我的单元格中有一个公式:

=IFERROR(VLOOKUP(A:A,'Daily Report'!A:Z,2,FALSE),"")

=IFERROR(VLOOKUP(A:A,'Daily Report'!A:Y,7,FALSE)&", "&VLOOKUP(A:A,'Daily Report'!A:Y,8,FALSE)&", #"&VLOOKUP(A:A,'Daily Report'!A:Y,9,FALSE)&"-"&VLOOKUP(A:A,'Daily Report'!A:Y,10,FALSE)&", Singapore "&VLOOKUP(A:A,'Daily Report'!A:Y,11,FALSE),"")

如何将其转换为VBA,以便使用此公式加密整个列?

我的公式总是被使用我的Excel工作表的人所取代 .

我正在避免锁定单元格,因此查看VBA来执行此操作 .

编辑:

MACRO

Sub vlookup()
Dim LR As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row
    Range("D2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(C[-3],'Daily Report'!C[-3]:C[22],2,FALSE),"""")"
    Selection.AutoFill Destination:=Range("D2:D" & LR), Type:=xlFillDefault
End Sub

现在如何制作数据,例如09-02-18022013-03383-A当进入A列时,它将运行宏来输入正确的数据 .

回答(1)

3 years ago

如果必须使用VBA,最简单的方法是在受影响的单元格中重写公式:

首先,将其放在Worksheet的模块中 . 每当对列A进行更改时,这将导致宏触发 .

Private Sub Worksheet_Change(ByVal Target as Range)
If Not Intersect(Target,Me.Range("A:A")) Is Nothing Then
Application.EnableEvents = False   'to disable infinite loop
    InsertFormula
Application.EnableEvents = True
End If
End Sub

然后,将其放在普通的代码模块中:

Sub InsertFormula()
Dim rng as Range   'this will set the range in which you want this formula to appear
Dim cl as Range    'cell iterator within rng variable
Dim strFormula1 as String  `string to hold the formula text

set rng = Range("B2:B39")   'Change this range to the desired range
strFormula = "=IfError(Vlookup(A:A,'Daily Report'!A:Z,2,False),"")"

For Each cl in rng
    cl.Formula = strFormula
Next

End Sub

因此,以编程方式插入常规公式相当容易 .

那么问题就变成你经常强迫/覆盖这些细胞的频率?无论何时打开工作簿文件,或者工作表上的值发生更改,或者有人手动更改您不希望它们更改的单元格等,您都可以将此宏绑定到“事件” .

您的第二个公式可以用它做同样的事情,只需添加另一个Range变量(例如, Dim rng2 as Range )和另一个字符串变量来保存公式文本(例如, strFormula2 ) .

或者,你可以"rewrite the formula"纯粹在vba中 . 将 cl.Formula = strFormula 替换为 cl.Value = MyLookupFormula 并将此函数添加到包含上述子例程的代码模块中:

Function MyLookupFormula() as Variant
'Performs equivlanet to worksheet function
If Not IsError(Application.WorksheetFunction.Vlookup(Range("A:A"),Sheets("Daily Report").Range("A:Z"),2,False)) Then

myLookupFormula = (Application.WorksheetFunction.Vlookup(Range("A:A"),Sheets("Daily Report").Range("A:Z"),2,False))

Else: myLookupFormula = vbNullString
End Function

但这需要更多地了解触发此宏的频率/事件的频率,因为单元格将不具有任何公式(仅当用户请求或事件触发器时,公式/计算才在内存中执行) .