如何将单元格查找转换为 VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14947998/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert cell vlookup to VBA
提问by user1935007
Currently, I am having a formula in my cell:
目前，我的单元格中有一个公式：
=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),"")
How do I convert it to VBA so the entire column will be encrypted with this formula?
如何将其转换为 VBA，以便使用此公式对整个列进行加密？
My formula is always replaced by the guys using my excel sheet.
我的公式总是被使用我的 excel 表的人所取代。
I am avoiding locking the cell, hence looking at VBA to perform this action.
我避免锁定单元格，因此查看 VBA 来执行此操作。
Edited:
编辑：
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
Now how to make data e.g. 09-02-18022013-03383-A when enter to column A, it will run the macro to input the rightful data.
现在如何制作数据，例如09-02-18022013-03383-A，当进入A列时，它将运行宏以输入正确的数据。
回答by David Zemens
If you must use VBA, the simplest way would be to just re-write the formula in the affected cells:
如果必须使用 VBA，最简单的方法是在受影响的单元格中重写公式：
First, place this in the Worksheet's module. This will cause the macro to fire EVERY time a change is made to the column A.
首先，将它放在工作表的模块中。这将导致每次对 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
Then, place this in an ordinary code module:
然后，把它放在一个普通的代码模块中：
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
So, programmatically inserting a normal formula is fairly easy.
因此，以编程方式插入普通公式相当容易。
The question then becomes how often do you want to force/overwrite these cells? You can tie this macro to "events" like, whenever the workbook file is opened, or whenever a value on the sheet changes, or whenever someone manually changes the cells you don't want them to change, etc.
那么问题就变成了你想要强制/覆盖这些单元格的频率吗？您可以将此宏与“事件”相关联，例如，每当打开工作簿文件时，或者每当工作表上的值发生更改时，或者每当有人手动更改您不希望它们更改的单元格时，等等。
Your second formula you could do the same thing with it, just add another Range variable (e.g., Dim rng2 as Range) and another string variable to hold the formula text (e.g., strFormula2).
你的第二个公式你可以用它做同样的事情，只需添加另一个 Range 变量（例如，Dim rng2 as Range）和另一个字符串变量来保存公式文本（例如，strFormula2）。
Alternatively, you could "rewrite the formula" purely in vba. Replace cl.Formula = strFormulawith cl.Value = MyLookupFormulaand add this function to the code module containing the subroutine above:
或者，您可以纯粹在 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
But that requires knowing more about how often/what events will trigger this macro, since the cells will not have any formula (the formula/computation is performed in memory ONLY when requested by user or an event trigger).
但这需要更多地了解触发此宏的频率/事件，因为单元格没有任何公式（公式/计算仅在用户或事件触发器请求时才在内存中执行）。
