Excel VBA 查找功能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28719845/
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
Excel VBA hlookup function
提问by tadalendas
I am trying to use the excel VBA HLookup function. I have tried it two ways and both produce error. Could anyone explain me what is that I am doing wrong?
我正在尝试使用 excel VBA HLookup 函数。我已经尝试了两种方法，都产生错误。谁能解释一下我做错了什么？
First try:
第一次尝试：
lookupValue = Worksheets(1).Name & "!A1"
tableArray = Worksheets(3).Name & "!$A:$" & Col_letter & ""
Worksheets("Comparison").Cells(1, 2).Value = "=HLookup(" & lookupValue _
& ";" & tableArray & ";1;FALSE)"
Second try:
第二次尝试：
tda = Worksheets(1).Cells(1, 1).Value ' I also tried using tda without .Value
Table = Worksheets(3).Range(Cells(1, 1))
Worksheets("Comparison").Cells(1, 2).Value = WorksheetFunction. _
HLookup(tda, Table, 1, False)
回答by Rory
For your first one, you need to use US regional settings, so a comma separator, and you should really enclose the sheet names in single quotes in case they contain spaces or look like special names (e.g. dates):
对于您的第一个，您需要使用美国区域设置，因此使用逗号分隔符，并且您应该将工作表名称用单引号括起来，以防它们包含空格或看起来像特殊名称（例如日期）：
lookupValue = "'" & Worksheets(1).Name & "'!A1"
tableArray = "'" & Worksheets(3).Name & "'!$A:$" & Col_letter & ""
Worksheets("Comparison").Cells(1, 2).Formula = "=HLookup(" & lookupValue _
& "," & tableArray & ",1,FALSE)"
and for the second you have to use a range object for the table argument:
对于第二个，您必须为 table 参数使用 range 对象：
tda = Worksheets(1).Cells(1, 1).Value ' I also tried using tda without .Value
Set Table = Worksheets(3).Range(Worksheets(3).Cells(1, 1), Worksheets(3).Cells(1, Col_letter))
Worksheets("Comparison").Cells(1, 2).Value = WorksheetFunction. _
HLookup(tda, Table, 1, False)
I have assumed Table is declared as Variant, Object or Range.
我假设 Table 被声明为 Variant、Object 或 Range。
Note you will still get a run-time error if there is no match for your lookup value.
请注意，如果您的查找值不匹配，您仍然会收到运行时错误。
回答by Pedro Braz
what you're doing there usually works, which is creating a string with the function you want to call and inputting it.
您在那里所做的通常有效，即使用您要调用的函数创建一个字符串并输入它。
I don't think that's the safest way, since it would not work if you run that macro from an Excel with a different language.
我认为这不是最安全的方法，因为如果您从使用不同语言的 Excel 运行该宏，它将无法正常工作。
the proper way to call an Excel function from VBA call is for example:
例如，从 VBA 调用中调用 Excel 函数的正确方法是：
cells(1,2) = Application.WorksheetFunction.VLookup(123,Range("A1:C100"),3,FALSE)
anyway if you'd rather use this string approach the problem in the first try is that
无论如何，如果您更愿意使用这种字符串方法，那么第一次尝试的问题是
"=HLookup(" & lookupValue _
& ";" & tableArray & ";1;FALSE)"
results in the string:
结果在字符串中：
=HLookup(Sheet1!A1;Sheet3!$A:$B;1;FALSE)
note that you're using semicolons where you're supposed to use commmas.
请注意，您在应该使用逗号的地方使用了分号。
the problem in the second try is that the .Rangeproperty takes a string as input, so you cant Range(Cells(1,1))you should do something like .Range("A1:A3")
第二次尝试的问题是该.Range属性需要一个字符串作为输入，所以你不能Range(Cells(1,1))做类似的事情.Range("A1:A3")
hope it helps !
希望能帮助到你 ！
