2

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?

the Tin Man
  • 158,662
  • 42
  • 215
  • 303
Tommy Z
  • 649
  • 5
  • 12
  • 18

2 Answers2

3

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
Kartik Anand
  • 4,513
  • 5
  • 41
  • 72
2

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.

Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
  • Thanks! That was a typo. I tried to make it a lot smaller to ask the question. I made the change to the Range but still have the same error. Any thoughts? – Tommy Z Jun 15 '12 at 18:16
  • And I really appreciate your help Scott! - tommy – Tommy Z Jun 15 '12 at 18:16
  • thanks almo, didn't realize that! it is not correct... yet! please feel free to chime in if you have any thoughts. – Tommy Z Jun 15 '12 at 18:37
  • Kartik above got to it before I could. He has solved your 2nd issue above. See his answer. It works. – Scott Holtzman Jun 15 '12 at 18:42