Vba and VLookup: inserting the formula vs reading the result of the formula

0
votes

I am working in Excel vba and want to read the result of a VLookup formula.

When I insert the formula itself into a cell...

Range(Cells(1, 1), Cells(1, 1)).FormulaR1C1 = "=VLookup(" & ent & ", " & MyName & ", " & fc & ", FALSE)"

...everything works. But when I try to read the same formula and keep the result into a variable...

MyResult=Application.WorksheetFunction.VLookup(ent, MyName , fc, False)

..., id does not work and reports an Error no. 1004.

Any ideas? Just for clarification, these are the arguments of the formula: the range is defined with a name in another workbook. This is not a problem when inserting the formula in "A1" cell.

et = 4529
ep = 1
fc = 3
ent = 9992
MyRoute = "C:\WORK\MyEstates\"
MyBook = MyRoute + CStr(et) + "\" + CStr(et) + "-data.xlsx"
MyName = "'" + MyBook + "'!D_" + CStr(ep)
1
vbaexcelvlookup
Possible duplicate of How to error handle 1004 Error with WorksheetFunction.VLookup? – Darren Bartrup-Cook
No, this doesn't really look like a duplicate of how to handle error 1004. – Sam Hartman

1 Answers

3
votes

Application.WorksheetFunction.VLookup needs a range as the second parameter. It doesn't know which worksheet or cells to look in.

This is what you're calling it with: 

Application.WorksheetFunction.VLookup(9992, "'C:\WORK\MyEstates\4529\4529-data.xlsx'!D_1", 3, false)

Try something more like:

Application.WorksheetFunction.VLookup (9992, worksheets("Sheet1").range("A1:b10"),3,FALSE)

If you need to use a variable then make sure you declare it as a Range.

(By the way, that was the first hit on a Google search.),

EDIT:

I realized afterwards what you're trying to do! :)

You shared lots of information but it was so convoluted I couldn't understand what the issue was, and the strange syntax (like Range(Cells(1, 1), Cells(1, 1))) and open-ended description (like Everything works) made it more confusing.

Before you get the answer, I might as well rewrite the question for you, so you get a better idea how to make it easier on everyone next time. Rewriting your question took me half the time it did to understand it, plus you would have answered your question yourself if you had just Googled the Error.

Error 1004 on WorksheetFunction.VLookup to external named range

Hi!

I have a VLookup formula which returns the expected result from a named range in another workbook when I assign it to a cell using this VBA:

Range("A1").FormulaR1C1 = _
    "=VLookup(9992, '*C:\myPath\fileName.xlsx'!namedRange*, 3, FALSE)"

I can't get the same VLookup formula to work with Application.WorksheetFunction like this:

myResult = Application.WorksheetFunction.VLookup _
    (9992, "'C:\myPath\fileName.xlsx'!namedRange, 3, FALSE)"

VBA stops on the line above with this error:

Run-time error '1004':

Unable to get the VLookup propery of the WorksheetFunction class

Next, I looked up the error and the very first Google search result was an existing answer from Stack Overflow with the solution to my problem.

So, I.......

.

I guess that's as far as I can go with the example question since the process of adding a couple links would have answered the question.

Here's your fix:

myResult = Application.WorksheetFunction.VLookup _
    (9992, "'C:\WORK\MyEstates\4529\4529-data.xlsx'!D_1", 3, false)

Please read over How to create a Minimal, Complete, and Verifiable example and How to Ask. Thanks for your time!