Excel VBA Vlookup - Unable to Get the Vlookup Property

Question

I have the following script and I'm getting a VLOOKUP error:

Dim DataRange, LookupRange As Range
Dim Data, Test As Variant

Set DataRange = Sheets("sheet").Range("A1:K12000")
Set LookupRange = sheets("sheet2").Range("A1:C50")
Data = DataRange.Value

For i = LBound(Data, 1) To UBound(Data,1)
  ReDim Preserve Test(1 To 3, 1 To i)
  test(1, i) = Application.WorksheetFunction.VLookup(Data(i, 4), LookupRange, 3, 0)
  'Other stuff works fine
Next i

Unfortunately, I get the error stating:

"Unable to get the VLookup property of the WorksheetFunction class"

This is strange because all of the variables and ranges look fine in watch mode. The lookup is also alphabetic... Any ideas what's going on?

Answer 1

That can mean any number of things. It may simply mean your Data(i, 4) value is not found in LookupRange.

Run-time error '1004':

Unable to get the VLookup property of the WorksheetFunction class

is equivilent to getting #N/A From =vlookup("A",A1:B3,2,false)

Set a breakpoint on the line

test(i) = Application.WorksheetFunction.VLookup(Data(i, 4), LookupRange, 3, 0)

and set a watch on Data(i, 4) as well as a watch on i. See if the value in Data(i, 4) exists in your lookup range. See if i is greater than 1, if it has run some iterations of the loop correctly.

As a side note your code wont run anyway since Test is an empty variant not an array. You need a line like

ReDim Test(LBound(Data, 1) To UBound(Data, 1))

before the for loop for it to work.

Read up on error handling here. You'll need that to handle VLOOKUP correctly from VBA.