为什么 VBA 中的 VLookup 失败并显示运行时错误 1004？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11055687/
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
Why is VLookup in VBA failing with runtime error 1004?
提问by Tommy Z
Spreadsheet "Sheet3" looks like this:
电子表格“Sheet3”如下所示：
S&P 500 DJIA
1/1/1991 795.4476 2973.09
1/2/1991 786.3856 2947.1
1/3/1991 775.4636 2905.19
1/4/1991 773.5364 2896.8
1/7/1991 760.2996 2847.9
1/8/1991 759.0029 2832.81
1/9/1991 750.8416 2788.67
1/10/1991 758.1719 2820.8
Also Cell "F2" is literally a copy and paste of 1/7/1991 cell.
此外，单元格“F2”实际上是 1/7/1991 单元格的复制和粘贴。
VBA Code looks like this:
VBA 代码如下所示：
Sub badlook3()
Dim BenchSI As Variant
Dim BRange As Range
Dim SIDate As Date
Set BRange = Worksheets("Sheet3").Range("A2:C9")
MsgBox BRange.Address
SIDate = Worksheets("Sheet3").Range("F2").Value
BenchSI = Application.WorksheetFunction.VLookup(SIDate, BRange, 2, True)
End Sub
I am getting the "Unable to get the VLOOKUP property of the WorkSheet Function class" error.
我收到“无法获取 WorkSheet 函数类的 VLOOKUP 属性”错误。
What am I missing here? Column A is in the right order. They are dates. What does Excel want from me?
我在这里缺少什么？A 列的顺序正确。它们是日期。Excel 想要我做什么？
采纳答案by Scott Holtzman
You are asking vLookup to return on a 2 column range, against a 1 column range. Change BRange = "A2:B9" to make your vLookup pick up the S&P Value.
您要求 vLookup 返回 2 列范围，针对 1 列范围。更改 BRange = "A2:B9" 以使您的 vLookup 获取标准普尔值。
Alternatively, you can change the range to A2:C9 and change the 2 to a 3 in your vLookup and get the DJ average.
或者，您可以将范围更改为 A2:C9，并将 vLookup 中的 2 更改为 3，然后获得 DJ 平均值。
In short, vLookup can only return a column reference to the greatest amount of columns in a range. It can return the 1st, 2nd, 0r 3rd column reference in a 3 column range, but not the 4th, because there is no 4th column.
简而言之，vLookup 只能返回对范围内最大数量的列的列引用。它可以返回 3 列范围内的第 1、2、0r 第 3 列引用，但不能返回第 4 列，因为没有第 4 列。
回答by Kartik Anand
The problem is with using SIDate as Date(Visual Basic date type)
My guess would be that visual basic date type and excel date type do not match, that's why you're getting an error
问题是使用 SIDate 作为日期（Visual Basic 日期类型）
我猜想是Visual Basic 日期类型和 excel 日期类型不匹配，这就是为什么你会收到错误
Instead declare SIDate as a Range, and it will work
而是将 SIDate 声明为Range，它将起作用
Here's the code:
这是代码：
Sub badlook3()
Dim BenchSI As Variant
Dim BRange As Range
Dim SIDate As Range
Set BRange = Worksheets("Sheet3").Range("A2:C9")
MsgBox BRange.Address
Set SIDate = Worksheets("Sheet3").Range("F2")
BenchSI = Application.WorksheetFunction.VLookup(SIDate, BRange, 2, True)
End Sub
