Solved

Issues with vlookup in Excel

Posted on 2013-01-18
5
292 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

Create Professional Looking Email Signatures

Create "Professional HTML Email Signatures" with ease.
7 Day Money Back Guarantee if not 100% Satisfied.
Affordable - Try it out for 7 Days Totally Risk Free.
Installers provided for over 45 Email clients.
Both Windows & MAC Supported.
Highly Recommended!

Question has a verified solution.

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

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
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…

732 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