asked on

Vlookup Two Workbooks #N/A Error

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

Tosagua

ASKER

Sorry about that, the question was submitted with zero points and should be 500.

Any assistance or insight would be appreciated.

Tosagua

Shanan212

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.

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tosagua

ASKER

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