VLookup From Closed Workbook

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,868
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I am trying to apply a vlookup formula to a closed workbookbook using this syntax ...

Code:
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

Well-known Member
Joined
Mar 23, 2004
Messages
3,868
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
OK ... I'm not done with you guys yet! New approach, adapted code .... it results in a #NAME error in Z1.

Code:
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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

EFANYoutube

Board Regular
Joined
May 19, 2017
Messages
162
missing the !

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

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,868
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
missing the !

VBA Code:
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)

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
ws_thold.Range("Z1") = "=VLookup(" & b_label & ",'D:\WSOP 2020\SupportData\[Facilities.xlsx]Facilities'!H:M,6,false)"
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,868
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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 ....
Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
What do you see in Z1 after you have run that line?
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,868
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
Forgot the extra quotes
VBA Code:
ws_thold.Range("Z1") = "=VLookup(""" & b_label & """,'D:\WSOP 2020\SupportData\[Facilities.xlsx]Facilities'!H:M,6,false)"
 
Solution

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,868
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Applause! Gremlins evicted. Thanks Fluff!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,492
Messages
5,636,636
Members
416,932
Latest member
mm07

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top