Solved

Issues with vlookup in Excel

Posted on 2013-01-18
5
291 Views
Last Modified: 2013-01-18
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
0
Comment
Question by:Bruj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 26

Accepted Solution

by:
redmondb earned 250 total points
ID: 38795246
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
 
LVL 39

Assisted Solution

by:nutsch
nutsch earned 250 total points
ID: 38795247
Try this instead

Workbooks("testvlookup.xlsm")

Open in new window


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

Thomas
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38795252
Hi, Thomas.

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

Regards,
Brian.
0
 

Author Closing Comment

by:Bruj
ID: 38795267
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
 
LVL 26

Expert Comment

by:redmondb
ID: 38795335
Thanks, Bruce. We've all been there!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question