Solved

Vlookup Two Workbooks #N/A Error

Posted on 2013-06-11
4
243 Views
Last Modified: 2013-06-11
The two workbooks are "PFA.xlsm", which holds the macro, and "Weekly Reports.xls", which holds the data. Both workbooks are open and stored on the I drive.

The macro is run on "Weekly Reports" to lookup a number and return a name.

The macro is:

Sub RefLookUp()
'
' RefLookUp Macro
'
Range("A:A").Select
     Range("A2:A" & Cells(65336, "B").End(xlUp).Row).Select
     Selection.Formula = "=VLOOKUP(G2,'[I:\WHSE\BDG\Curt\PFA.xlsm]SheetAPF'!$A$2:$B$65336,2,FALSE)"

End Sub

After running the macro #N/A is returned and the value in Cell A2 is:

=VLOOKUP(G2,'[I:\WHSE\BDG\Curt\[PFA.xlsm],SheetAPF]PFA.xlsm],SheetAPF'!$A$2:$B$65336,2,FALSE)
Weekly-Report.xls
PFA.xlsm
0
Comment
Question by:Tosagua
  • 2
4 Comments
 

Author Comment

by:Tosagua
ID: 39237873
Sorry about that, the question was submitted with zero points and should be 500.

Any assistance or insight would be appreciated.

Tosagua
0
 
LVL 13

Expert Comment

by:Shanan212
ID: 39237889
1. Try this macro

Sub RefLookUp()
'
' RefLookUp Macro
'
Range("A2:A60000").Select
     
     Selection.Formula = "=VLOOKUP(G2,'[PFA.xlsm]SheetAPF'!$A$2:$B$65336,2,FALSE)"

End Sub

Open in new window


2. Both workbooks must be open to get results.
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39237898
Hi

Sub RefLookUp()
'
' RefLookUp Macro
'
     Range("A2:A" & Cells(65336, "B").End(xlUp).Row).Select
     Selection.Formula = "=VLOOKUP(G2,'[PFA.xlsm]APF'!$A$2:$B$65336,2,FALSE)"

End Sub

Open in new window

The APF Sheet is adressed wrong (only APF)

Regards
0
 

Author Closing Comment

by:Tosagua
ID: 39237994
Rgonzo1971,

Works perfectly.

The prior solution returned a "Run-time error '1004: Application-defined or object-defined error".

I appreciate both responses.

Thank you very much.

Tosagua
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

680 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