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?

 

[Domenic]

I did check the length of the data string in the lookup values to rule out the possibility of a trailing space or something. The length came back matching what was really there.

And how about the length of the text string in your lookup range/database?

And if we compare a lookup value with its matching value in the lookup range with the following formula does it return TRUE?

=F2='d:\wsop2023\SupportData\[Facilities.xlsx]Facilities'!A4

 

[Ark68]

Hi Domenic,
Typing in that formula (=F2='D:\wsop2023\SupportData\[Facilities.xlsx]Facilities'!A4) into G2 for example results in a TRUE result. After I typed in the formula and pressed enter I was prompted with the dialogue to open the facilities.xlsx file.
Could the issue be that the facilities.xlsx file is closed when I'm trying to use my original code? I thought I could do a vlookup on a closed workbook.
I don't think it's an issue with the data because all vlookups work when I manually enter the formula outside of trying to apply the formula via VBA.

 

[MARK858]

I need to place the looked up value in column 8, based on a reference in the data to column F

If you are in column 8 (H) then RC[-1] is referring to column 7 (G) not F, you need RC[-2] if you want column F (I'm a bit confused as you should be able to see that it is referring to the wrong column [if that is the case] when you look at the formula the code has put in the cell)

 

[Ark68]

Hi Mark,
Thank you for making that observation. That was a typo on my part. The formula is going into cell G2. The lookup value is in F2.
I think something is amouk with the range reference. The code puts the formula in the cell, and the systax is all correct. If I manually change the lookup value reference, say to F3, and press enter, I am prompted to open the facilities file, for which then the correct result appears. If I manually change that value back to F@, same thing. Prompt to open facilities file, result works.

 

[MARK858]

On the Facilities workbook, Facilities Sheet select A2:R500
Go to the Name Box on the Formula Bar, type Project and hit enter.

On your CoreData sheet in G2 put the formula

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

Save and close the Facilities workbook

The formula in G2 should have changed to the full path.
Check the path is correct in comparison to your code .

 

[Ark68]

Hi Mark, thank you for your support.
Selected all the cells in Facilities A2:A500 and named Project. Saved and closed.
Copied and pasted your formula in G@, pressed enter. I was prompted with dialogue to open a file, so I opened the facilities.xlsx file. The result was #ref.
The formula in G2 did not change.

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

 

[MARK858]

That is not the order I posted, you put the formula in before you do the save and close

 

[Ark68]

That didn't work either unfortunately.

 

[MARK858]

That didn't work either unfortunately.

Strange, just tried it again (I did test last night before posting) and it works fine for me.
Is your named range Project showing correctly in Name Manager in the Facilities workbook?

It should look something like the images below

 

[Ark68]

Well, if it's a mystery to an MVP, I feel like I'm doomed lol. Here are the same snaps taken from name manager from the facilities.xlsx workbook. I have no named ranges identified in my CoreData workbook. Should I?