All geek questions in one place

Excel VBA Vlookup - Unable to get Vlookup property

I have the following script and I get 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

Sorry, I get an error:

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

This is strange because all variables and ranges look great in view mode. Search is also an alphabet ... Any ideas what is going on?

+4
source share
1 answer

It can mean any number of things. It might just mean that your Data(i, 4) value was not found in LookupRange.

 Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class

equivalent 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 the clock Data(i, 4) , as well as the clock to i . See if the value in Data(i, 4) exists in your search range. See if i greater than 1, if it correctly completed several iterations of the loop.

As a side note, your code doesn't work at all, since Test is an empty option, not an array. You need a string like

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

before the for loop to make it work.

Read about the processing error here . You will need to properly handle VLOOKUP with VBA.

+5
source

Source: https://habr.com/ru/post/1499193/

More articles:


All Articles