0

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)
ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Javi
  • 170
  • 3
  • 13
  • 2
    Possible duplicate of [How to error handle 1004 Error with WorksheetFunction.VLookup?](https://stackoverflow.com/questions/18063214/how-to-error-handle-1004-error-with-worksheetfunction-vlookup) – Darren Bartrup-Cook Nov 29 '17 at 17:19
  • 1
    No, this doesn't really look like a duplicate of how to handle error 1004. – Sam Hartman Nov 29 '17 at 18:02

1 Answers1

3

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!

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • Dear ashleedawg, I didn't asked and ran. I was just trying to solve the problem by myself or asking a different question, as I'm learning more about the problem. Sometimes something does not work as one expects, but does not know exactly what is the question to ask. In my case, I am not very used to program in vba, so don't know exactly to manage the problems, even to inspect a variable... Sorry about inconvenience and thanks a lot for your help. – Javi Nov 29 '17 at 18:18
  • Ok, my bad. Now you have a solution above (right?) and you know that the first step when you have an error message is to figure out what the error is trying to tell you (with the help of Google). With the excess info available, effective searching is a skill learned with practice. Believe me, I know what it's like to not know the right questions to ask, that's why I re-wrote your question, now you know. But rest assured, **any** problem you're going to have has already been discussed online numerous times so that's a good starting place. (Not just coding; _any_issue!) Good luck! – ashleedawg Nov 29 '17 at 18:33
  • 1
    A very detailed step by step description and helpful recipe. – T.M. Nov 29 '17 at 19:15