VLookup From Closed Workbook

[Ark68]

I am trying to apply a vlookup formula to a closed workbookbook using this syntax ...

grm_crew1 = application.WorksheetFunction.VLookup(b_label,'D:\WSOP 2020\SupportData\[Facilities.xlsx]Facilities'!range("H:M"),6,false)

grm_crew is defined as string, b_label is string. The vlookup is being applied to columns H:M in the sheet ("Facilities"), in the workbook("Facilities.xlsx"), at the path ("D:\WSOP 2020\SupportData\") specified.

I am getting an "Expected: expression" error with the first apostrophe identified.

Looking to the kind folk here to help me identify where I went wrong.

 

[Ark68]

OK ... I'm not done with you guys yet! New approach, adapted code .... it results in a #NAME error in Z1.

ws_thold.Range("Z1") = "=VLookup(b_label,('D:\WSOP 2020\SupportData\[Facilities.xlsx]Facilities'!H:M),6,false)"

I'm guessing I'm missing something very subtle.

 

[EFANYoutube]

missing the !

ws_thold.Range("Z1") = "=VLookup(b_label,('D:\WSOP 2020\SupportData\[Facilities.xlsx]!Facilities'!H:M),6,false)"

 

[Ark68]

missing the !

ws_thold.Range("Z1") = "=VLookup(b_label,('D:\WSOP 2020\SupportData\[Facilities.xlsx]!Facilities'!H:M),6,false)"

Hmmmm ... didn't seem to help. #NAME (error 2029)

ws_thold.Range("Z1") = "=VLookup(b_label,('D:\WSOP 2020\SupportData\[Facilities.xlsx]!Facilities'!H:M),6,false)"

 

[Fluff]

Try

ws_thold.Range("Z1") = "=VLookup(" & b_label & ",'D:\WSOP 2020\SupportData\[Facilities.xlsx]Facilities'!H:M,6,false)"

 

[Ark68]

Hmmm ... gremlins still at play here ...

This is what is in cell Z1 after running the code ...
=VLOOKUP(Auburn Diamond,'d:\WSOP 2020\SupportData\[Facilities.xlsx]Facilities'!H:M,6,FALSE)

My code to put it there ....

ws_thold.Range("Z1") = "=VLookup(" & b_label & ",'D:\WSOP 2020\SupportData\[Facilities.xlsx]Facilities'!H:M,6,false)"

I checked my data. "Auburn Diamond" is found in Facilities worksheet at Cell H2. The Result of the vlookup should be CWP as found in cell M2.

 

[Fluff]

What do you see in Z1 after you have run that line?

 

[Ark68]

What do you see in Z1 after you have run that line?

#NAME?
And in the formula bar I see the formula as posted above.

 

[Fluff]

Forgot the extra quotes

ws_thold.Range("Z1") = "=VLookup(""" & b_label & """,'D:\WSOP 2020\SupportData\[Facilities.xlsx]Facilities'!H:M,6,false)"

 

[Ark68]

Applause! Gremlins evicted. Thanks Fluff!

 

[Fluff]

Glad we could help & thanks for the feedback.