为什么VBA中的VLookup失败并显示运行时错误1004? [英] Why is VLookup in VBA failing with runtime error 1004?

问题描述

电子表格"Sheet3"如下所示:

Spreadsheet "Sheet3" looks like this:

            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

也称为"F2"的单元格实际上是1991年7月1日单元格的副本和粘贴.

Also Cell "F2" is literally a copy and paste of 1/7/1991 cell.

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

我收到无法获取WorkSheet Function类的VLOOKUP属性"错误.

I am getting the "Unable to get the VLOOKUP property of the WorkSheet Function class" error.

我在这里想念什么?列A的顺序正确.他们是日期. Excel向我要什么?

What am I missing here? Column A is in the right order. They are dates. What does Excel want from me?

推荐答案

您要让vLookup返回2列范围，而不是1列范围.更改BRange ="A2:B9"，以使您的vLookup获得标准普尔(S& P)值.

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.

或者，您可以将范围更改为A2:C9，然后在vLookup中将2更改为3，以获得DJ平均值.

Alternatively, you can change the range to A2:C9 and change the 2 to a 3 in your vLookup and get the DJ average.

简而言之，vLookup只能返回对范围内最大列数的列引用.它可以返回3列范围内的第1、2、0r第3列引用，但不能返回第4列，因为没有第4列.

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.

这篇关于为什么VBA中的VLookup失败并显示运行时错误1004?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！