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)

 

[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

 

[Domenic]

It looks like you have an extra bracket, for example try...

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

Hope this helps!

 

[Domenic]

By the way, I would suggest that you specify the FormulaR1C1 property of the Range object for clarity...

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

Hope this helps!

 

[Ark68]

Thank you Domenic,
I'm running into an "Application defined or object defined error" with the bracket removed. Without it, I have two opening, and one closing bracket, so not sure if that would be the problem. Putting back in the bracket rids of the error, but results again in the #NA.

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

 

[offthelip]

The way I solve these problems when writing formula using VBA; is to get rid of the equals sign so it just writes a string to the cell. Then go to the cell update the cell ( Hit F2) and put the equals sign back in, and excel will hopefully tell you what is wrong

 

[Ark68]

Thanks for the suggestion. If I followed you correctly, I changed the line of code to

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

, removing the"=" preceeding "vlookup"
When run, this put the formula in the call as a string value. F2 didn't do anything.
Just for giggles, I put an = back in front of vlookup to make it a formula, hit F2, nothing happened, then return and nothing happened but RC[-1] was highlighted as if wanting to accept a value? I may not have did this correctly.

If I manually enter the formula into the cell, I get the correct result.

=VLOOKUP(F2,Facilities.xlsx!$A$2:R500,8,FALSE)

Perhaps RC[-1] is referring to the wrong cell? I'm not real familiar with using R1C1 method. I need to place the looked up value in column 8, based on a reference in the data to column F

 

[Domenic]

Sorry, my mistake. I didn't notice the matching bracket toward the beginning of the formula. The syntax seems correct. But you say that it returns #N/A.

When the error occurs, what is the lookup value? And what type of values does Facilities.xlsx!A2:A500 contain?

 

[Ark68]

Silly question ... how can I determine what the lookup value is being seen as vs. what I think it should be? Like I said, when the code runs, it places the right formula into the cell with the correct syntax, which would be pointing to the correct cell for the lookup value. The cell value being looked up is text, and does reside in the reference database. A2:A500 contains text values that should match values being looked up.