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]

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.

 

[Domenic]

It looks like Column F contains your lookup values. Can you post a few examples of the lookup values?

And the same thing for your lookup range in Facilities.xlsx!A2:A500. Can you post a few examples?

Better still, it would be even more helpful if you could post some sample data for both your lookup values and lookup table using the xl2BB add-in.

 

[Ark68]

Here is a sample of the lookup values. Because it's sensitive personal data, I have had to censor it.

WSOP 23_v23.04.24 .xlsm
	F
1	Facility/Equipment/Instructor
2	Bxxxxel - Bxxxxel xxxxd #1
3	Bxxxxel - Bxxxxel xxxxd #3
4	Bxxxxel - Bxxxxel xxxxd #4
5	Bxxxxel - xxxmoxx
6	Bxx - Loxxx xxxxd
7	Bxx - Upxxx xxxxd
8	Cexxxxrae PS - xxxxxxd #1
9	Cexxxxrae PS - xxxxxxd #2
10	Cxxxxnnial PS - xxxxxxd #1
11	Cxxxxnnial PS - xxxxxxd #2
12	Cxxxxrvaxxxx xxxxows xxxxd
13	Hxxxxxxe - Hxxxxtaxxxx xxxx
14	Hxxxxxxe - Loxxx xxxxxxd
15	Hxxxxxxe - Loxxx xxxxxxd
16	Hxxxxxxe - Upxxx xxxxxxd
17	Hxxxxxxe - Upxxx xxxxxxd
CoreData_08-Jul

And the lookup range ...

Facilities.xlsx
	A
1	A FIELD (do not alter)
2	xxxurn xxxxxxd
3	Bxxxxel - xxxxxxxd
4	Bxxxxel - Bxxxxel xxxxd #1
5	Bxxxxel - Bxxxxel xxxxd #2
6	Bxxxxel - Bxxxxel xxxxd #3
7	Bxxxxel - Bxxxxel xxxxd #4
8	Bxxxxel - Kxxxxis/Bxxxxel Sidxxxxk Txxxl
9	Bxxxxel - Txxxxs
10	Bxxxx xxxxh xxxxd
11	Bxx - Loxxx xxxxd
12	Bxx - Upxxx xxxxd
13	Cexxxxrae PS - xxxxxxd #1
14	Cexxxxrae PS - xxxxxxd #2
15	Cxxxxnnial PS - xxxxxxd #1
16	Cxxxxnnial PS - xxxxxxd #2
17	Cxxxxrvaxxxx xxxxows xxxxd
18	Crexxxxde - xxxxd #1 (North)
19	Crexxxxide - xxxxd #2 (South)
20	Crexxxxde - xxxxd #3 (East)
21	xxxxbrixxx - xxxxd
22	xxxxbrix - xxxxxxd
23	Emxxxx PS xxxxxxd
24	Hxxxxxxe - Hoxxxtalxxx Axxa
25	Hxxxxxxe - Loxxx xxxxxxd
26	Hxxxxxxe - Upxxx xxxxxxd
Facilities

Not sure if it will help or not, but it's the best I can do. 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.