CODEWITHSUNDEEP
vbaexcel-2007vlookup
Tommy Z
Tommy Z

Reputation: 649

Why is VLookup in VBA failing with runtime error 1004?

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

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

VBA Code looks like this:

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.

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

Upvotes: 2

Views: 13441

Answers (2)

Kartik Anand
Kartik Anand

Reputation: 4609

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

Instead declare SIDate as a Range, and it will work

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

Upvotes: 3

Scott Holtzman
Scott Holtzman

Reputation: 27239

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.

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

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.

Upvotes: 2

Related Questions