CODEWITHSUNDEEP
excelvbavlookup
Jeffrey Kramer
Jeffrey Kramer

Reputation: 1345

Excel VBA Vlookup - Unable to Get the Vlookup Property

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?

Upvotes: 5

Views: 35054

Answers (2)

vapcguy
vapcguy

Reputation: 7537

When you get this error, it means that the value you are using in the first slot of the VLookup() method, in this case the Data(i,4), is not able to be found in the LookupRange you are using in order to return anything.

Before doing the VLookup, test if the value you want to do the lookup with actually exists in your range, first - which you don't need to set in a variable, just loop through it on the fly. You can use .Find to do your search, and then you should be limiting your datasource to a single column, to look at your columns on your other sheet, which I wasn't sure if it was on this workbook or another workbook - for me, when I was doing it, it was on another one, so I named it that way, but you're just going to use whatever workbook has your sheet2:

Dim LookupRange, r As Range
Dim LookupValue As String
Dim lastRow, ctr As Integer
ctr = 1
lastRow = YourWb.Sheets("sheet").UsedRange.Rows.Count

Set LookupRange = AnotherWb.Sheets("sheet2").Range("A1:C50")

For Each Cell In YourWb.Sheets("sheet").Range("M2:M" & lastRow)  

    ' For me, the M column was where my VLOOKUPs were going
    ' Get your lookup value here by using the column it is in and the counter
    LookupValue = YourWb.Sheets("sheet").Range("A" & ctr).Value ' just use one column with your values, not A:K, here

    With LookupRange
        Set r = .Find(LookupValue, LookIn:=xlValues)
        If Not r Is Nothing Then
            Cell.Value = Application.WorksheetFunction.VLookup( _
                LookupValue, LookupRange, 16, False)
        End If
    End With
    ctr = ctr + 1

Next

For me, I was just assigning the VLookup value directly to the column of cells - there was no need to use a Variant or Array. Why would you? Just write it all back out to the column directly.

It's also where I'm not keen on the whole For i = LBound(Data, 1) To UBound(Data,1) line, anyway. You can either use the method of appending a counter or do a For Each Cell in ... your range of cells. I demonstrated both methods, above.

Upvotes: 0

AndASM
AndASM

Reputation: 10328

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.

Upvotes: 5

Related Questions