Issues with vlookup in Excel

I am trying to to a vlookup in another (opened) workbook.
I have done ths many times with great success.
Since yesterday, I have been working on this. The original sub and stuff were doing some pretty fancy stuff so, I kept trying to go for the basics.

Here is the code I am currently using:
Sub test()


For i = 1 To 20
MsgBox WorksheetFunction.VLookup(i, Workbooks("d:\desktop\testvlookup.xlsm").Sheets("Sheet1").Range("A:B"), 2, False)

Next

End Sub

Open in new window


I keep getting a subscript error.

If I try the same command from the same workbook (even different pages) with out the
           Workbooks("d:\desktop\testvlookup.xlsm")

Open in new window


 it will work.

I try and paste "d:\desktop\testvlookup.xlsm" in the run command and it says the book is open, so there is not a typo, and the file etc exists and is in fact open.
This is a very clean test workbook with "sheet1" having colA filled with numers 1-26 and column b filled with A-Z


What am I missing???
Office 2010. 32k  under Win7 64bit


Thanks
Bruce
BrujAsked:
Who is Participating?
 
redmondbConnect With a Mentor Commented:
Hi, Bruce.

Drop the "d:\desktop\", i.e.
MsgBox WorksheetFunction.VLookup(i, Workbooks("testvlookup.xlsm").Sheets("Sheet1").Range("A:B"), 2, False)

Regards,
Brian.
0
 
nutschConnect With a Mentor Commented:
Try this instead

Workbooks("testvlookup.xlsm")

Open in new window


Since it's opened, the path is throwing things off.

Thomas
0
 
redmondbCommented:
Hi, Thomas.

Specifying the path wouldn't work for a closed file either.

Regards,
Brian.
0
 
BrujAuthor Commented:
DOH!!!!!
I have been working and working on this! I feel like an idiot.... Sum times ya just cant see the obvious!


Thanks guys!
Bruce
0
 
redmondbCommented:
Thanks, Bruce. We've all been there!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.