Excel 2013 on W10: VBA Vlookup in a different work

Question

I'm developing an application which uses specification codes to vlookup those codes in another spreadsheet, and return vendor numbers from the second spreadsheet to the first, listing them in the same column with the specification code.

ActiveCell.FormulaR1C1 = Application.WorksheetFunction.VLookup(specsec, [Vendorspec.xlsx!vid], 4)

in the above code line:

• specsec is the specification code, of the form XX XX XX.XX
• The name of the vlookup target file is "VendorSpec.xlsx". In this worksheet, each code is a unique entry in column 1, with the first of several vendor numbers in column 4. Future code will cycle through the columns, returning all subsequent vendor IDs for the current code.

The code line produces error "Run-time error '1004': Unable to get the vlookup property of the worksheet function class".

Can anyone suggest a fix?

Thank you.

Answer 1

Your lookup is simply failing, and the error message is utterly misleading.

It's not that VBA couldn't find the WorksheetFunction.VLookup member, it's just that your VLookup raised an error.

You need to either:

• Handle that runtime error with an On Error GoTo statement

Or

• Use the late-bound version Application.VLookup, which doesn't give you IntelliSense, but instead of throwing a runtime error when the lookup fails, it will return "Error 2042" and you can test whether the lookup failed or not by wrapping it in IsError.

---

Type 42 in cell A1 of the active sheet. Then in the immediate pane:

?iserror(application.VLookup(42,Range("A:B"),1,false))

returns False

?iserror(application.VLookup(43,Range("A:B"),1,false))

returns True

?application.WorksheetFunction.VLookup(42,Range("A:B"),1,false)

returns 42

?application.WorksheetFunction.VLookup(43,Range("A:B"),1,false)

raises a runtime error:

That message would be better worded as "VLookup function failed to find specified lookup value in specified lookup range", or something like that.

---

The reason your lookup is failing is the same any VLOOKUP might fail for: verify your lookup_value actually exists in your lookup_range. Watch out for leading and/or trailing spaces, and "text-formatted" columns. In other words, assuming you want to throw a runtime error when the lookup fails, it's a data problem, not a code problem.