Why is VLookup in VBA failing with runtime error 1004?

Question

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?

Kartik Anand Kartik Anand · Accepted Answer · 2012-06-15T18:33:10

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

Why is VLookup in VBA failing with runtime error 1004?

2 Answers