Excel VBA Vlookup - 无法获取 Vlookup 属性
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18471266/
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 Vlookup - Unable to Get the Vlookup Property
提问by Jeffrey Kramer
I have the following script and I'm getting a VLOOKUP error:
我有以下脚本，但出现 VLOOKUP 错误：
Dim DataRange, LookupRange As Range
Dim Data, Test As Variant
Set DataRange = Sheets("sheet").Range("A1:K12000")
Set LookupRange = sheets("sheet2").Range("A1:C50")
Data = DataRange.Value
For i = LBound(Data, 1) To UBound(Data,1)
ReDim Preserve Test(1 To 3, 1 To i)
test(1, i) = Application.WorksheetFunction.VLookup(Data(i, 4), LookupRange, 3, 0)
'Other stuff works fine
Next i
Unfortunately, I get the error stating:
不幸的是，我收到错误说明：
"Unable to get the VLookup property of the WorksheetFunction class"
This is strange because all of the variables and ranges look fine in watch mode. The lookup is also alphabetic... Any ideas what's going on?
这很奇怪，因为所有变量和范围在监视模式下看起来都很好。查找也是按字母顺序排列的......知道发生了什么吗？
回答by AndASM
That can mean any number of things. It may simply mean your Data(i, 4)value is not found in LookupRange.
这可能意味着任何数量的事情。这可能只是意味着Data(i, 4)在 LookupRange 中找不到您的值。
Run-time error '1004':
Unable to get the VLookup property of the WorksheetFunction class
is equivilent to getting #N/AFrom =vlookup("A",A1:B3,2,false)
相当于#N/A从=vlookup("A",A1:B3,2,false)
Set a breakpoint on the line
在行上设置断点
test(i) = Application.WorksheetFunction.VLookup(Data(i, 4), LookupRange, 3, 0)
and set a watch on Data(i, 4)as well as a watch on i. See if the value in Data(i, 4)exists in your lookup range. See if iis greater than 1, if it has run some iterations of the loop correctly.
并设置手表Data(i, 4)和手表i。查看 in 的值是否Data(i, 4)存在于您的查找范围内。查看是否i大于 1，是否正确运行了一些循环迭代。
As a side note your code wont run anyway since Testis an empty variant not an array. You need a line like
作为旁注，您的代码无论如何Test都不会运行，因为它是一个空变体而不是数组。你需要一条像
ReDim Test(LBound(Data, 1) To UBound(Data, 1))
before the for loop for it to work.
在 for 循环之前让它工作。
Read up on error handling here. You'll need that to handle VLOOKUP correctly from VBA.
在这里阅读错误处理。您需要它来从 VBA 正确处理 VLOOKUP。
