Using Vlookup On Closed Workbook With VBA resulting in all #NA

[Ark68]

I am trying to populate cells of my worksheet using a vlookup formula on a closed workbook.

              For L1 = 2 To nrec
                .Cells(L1, 7) = "=vlookup(RC[-1],('d:\wsop2023\SupportData\[Facilities.xlsx]Facilities'!R2C1:R500C18),8,false)" 'label
                .Cells(L1, 8) = "=vlookup(RC[-2],('d:\wsop2023\SupportData\[Facilities.xlsx]Facilities'!R2C1:R500C18),5,false)" 'type
                .Cells(L1, 9) = "=vlookup(RC[-3],('d:\wsop2023\SupportData\[Facilities.xlsx]Facilities'!R2C1:R500C18),6,false)" 'unit
                .Cells(L1, 10) = "=vlookup(RC[-4],('d:\wsop2023\SupportData\[Facilities.xlsx]Facilities'!R2C1:R500C18),3,false)" 'class
                .Cells(L1, 11) = "=vlookup(RC[-5],('d:\wsop2023\SupportData\[Facilities.xlsx]Facilities'!R2C1:R500C18),4,false)" 'resp
                .Cells(L1, 12) = "=vlookup(RC[-6],('d:\wsop2023\SupportData\[Facilities.xlsx]Facilities'!R2C1:R500C18),7,false)" 'lit
                .Cells(L1, 13) = "=vlookup(RC[-7],('d:\wsop2023\SupportData\[Facilities.xlsx]Facilities'!R2C1:R500C18),9,false)" 'washrooms
                .Cells(L1, 14) = "=vlookup(RC[-8],('d:\wsop2023\SupportData\[Facilities.xlsx]Facilities'!R2C1:R500C18),10,false)" 'washroom code
                .Cells(L1, 15) = "=vlookup(RC[-9],('d:\wsop2023\SupportData\[Facilities.xlsx]Facilities'!R2C1:R500C18),11,false)" 'family
                .Cells(L1, 16) = "=vlookup(RC[-10],('d:\wsop2023\SupportData\[Facilities.xlsx]Facilities'!R2C1:R500C18),13,false)" 'groom
                .Cells(L1, 17) = "=vlookup(RC[-11],('d:\wsop2023\SupportData\[Facilities.xlsx]Facilities'!R2C1:R500C18),14,false)" 'active/passive
                .Cells(L1, 18) = "=vlookup(RC[-12],('d:\wsop2023\SupportData\[Facilities.xlsx]Facilities'!R2C1:R500C18),18,false)" 'address
              Next L1

This code is placing the applicable vlookup formula in the appropriate cell. The "Facilities.xlsx" workbook is closed and resides in the path indicated in the formula. The worksheet and range are both correct.
When I run this code, I get all #N/A.
The formula applied to the cell on the worksheet appears correct. For example, at cell G5, the formula is "=VLOOKUP(F2,('d:\wsop2023\SupportData\[Facilities.xlsx]Facilities'!$A$2:$R$500),8,FALSE)" This correct. Look for the value in F2 in workbook Facilities.xlsx, worksheet Facilities, range A2:R500. The value for F2 will be found in column A, and it will return a value from column H.

Any thoughts on where I may have erred?

 

[MARK858]

I have no named ranges identified in my CoreData workbook. Should I?

No, it' scope is at Workbook level...

When the Facilities workbook is open the formula in the other workbook shows

=VLOOKUP(F2,Facilities.xlsb!Project,8,FALSE)

and when the Facilities workbook is closed the same formula shows

=VLOOKUP(F2,'https://d.docs.live.net/0a8############c/Desktop/Facilities.xlsb'!Project,8,FALSE)

for me (obviously I have hidden some of the digits in the path)

 

[Ark68]

I'm not convinced I might not be doing things correct, especially knowing I'm not super Excel literate.

So, I finally have it mimicking your results.
The formula bar of my CoreData worksheet reads:
=VLOOKUP(F2,'D:\WSOP 2023\SupportData\Facilities.xlsx'!PROJECT,8,FALSE) with the Facilities.xlsx workbook closed.

With Facilities.xlsx workbook open, the formula bar of the CoreData worksheet reads:
=VLOOKUP(F2,Facilities.xlsx!PROJECT,8,FALSE)

Instead of #N/A now, I'm getting #REF.

Are we getting closer to a solution?

 

[MARK858]

You're getting the #REF error because you made the named range A2:A500 and not A2:R500 and so you have no 8th column in the range

 

[Ark68]

You're getting the #REF error because you made the named range A2:A500 and not A2:R500 and so you have no 8th column in the range

Oh yeah. Got it now.
So now that it works, how do I adapt my original problematic code?
Bad:

.Cells(L1, 7).FormulaR1C1 = "=vlookup(RC[-2],('d:\wsop2023\SupportData\[Facilities.xlsx]Facilities'!R2C1:R500C18),8,false)" 'label

My guess (which of course is wrong):

.Cells(L1, 7).FormulaR1C1 = "=vlookup(RC[-2],(PROJECT),8,false)" 'label

 

[MARK858]

With the Facilities workbook closed.

Turn on the macro recorder.
Delete the last ) in the formula
Type the ) back in
Turn off the macro recorder

The macro recorder will have produced the code you need

 

[Ark68]

You are amazing Mark!!
Thank you so much, it's working now!
Is it safe to say that I had an error in my original vlookup range that was too subtle to find? Creating the neamed range eliminated the need to identify the range in the formula?

 

[MARK858]

Is it safe to say that I had an error in my original vlookup range that was too subtle to find? Creating the neamed range eliminated the need to identify the range in the formula?

More that creating the named range meant that your ranges weren't an issue I had to consider and so could concentrate on making sure your path was correct.

Happy you have it sorted