How to write VBA vlookup formula with variable workbook and sheet names

MAM8433

New Member
Joined
May 18, 2011
Messages
36
I hope you can help.

I’m trying to write VBA code for a vlookup formula that contains 4 variables and collections/objects.

Essentially, I need it to do this:

Workbooks(‘a’).Worksheets("Provider Report").Range(‘b’ & "9").Formula = "=VLOOKUP($a9, ‘[workbooks(‘c’)]Worksheets(‘d’) '!$A:$J,9,0)"

Where
  • ‘a’ = name of file that needs the vlookup
  • ‘b’ = column letter of the first available column
  • ‘c’ = the name of the workbook with the table array
  • ‘d’ = the worksheet number that contains the table array
So far, I have written the code that stores:

  • ‘a’ to txtMacrofile
  • ‘b’ to txtColLetter
  • ‘c’ to txtTAwbNm
  • ‘d’ to intWSNum
I can’t figure out the correct syntax to designate the table array workbook and worksheet and include the aliases, apostrophes and brackets.

Here's my latest stab at it:

Workbooks(txtMacroFile).Worksheets("Provider Disruption Report").Range(txtCoLetter & "9").Formula = _

"=VLOOKUP(a9,'[workbooks(txtExportfilename).Worksheets(PPO_WSN)] '!$A:$j,9,0)"


While this code runs, a file selection dialog appears asking me to “Update Values workbooks(txtTAwbNm).worksheets(intWSNum.”

Thanks in advance for any guidance or solutions you can offer.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,609
Office Version
  1. 365
Platform
  1. Windows
You need to join the strings so that the formula reads in excel format, not vba format.
By entering vba keywords and variables between the double quotes of the formula, you are actually entering those keywords into the formula rather than using them as vba keywords.
To use a keyword or variable within a formula you need to close the double quotes and join (concatenate) it in sections.
Rich (BB code):
Workbooks(txtMacrofile).Worksheets("Provider Report").Range(txtColLetter & "9").Formula = "=VLOOKUP($a9,'[" & txtTAwbNm & "]" & intWSNum & "'!$A:$J,9,0)"
 

MAM8433

New Member
Joined
May 18, 2011
Messages
36
Thank you, Jason75!!! Your explanation led me to conclude that I should have been storing worksheet name instead of worksheet number. So with a minor tweak to your code -- I dumped intWSNum and replaced it with a worksheet name variable -- IT WORKED! Again, thank you for your time; you saved this VBA journeyman hours of further frustration. Onward!
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,609
Office Version
  1. 365
Platform
  1. Windows
I actually missed the fact that it was sheet number instead of name, you can still do it with number. The 'best' method depends on user habits (if any of the following apply).

A name that is written directly into vba will not work if the name of the sheet is changed in excel.
Sheet number will not work correctly if the sheet are re-ordered in excel. Sheet number 1 is always the tab closest to the left.

Each sheet has a vba codename (google) which never changes. You can change the name in the vba editor but any changes made in excel will not cause it to look at the wrong sheet like the other methods.

Getting the text name by using Sheet1.Name is the most reliable method.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,483
Messages
5,636,604
Members
416,929
Latest member
Nitil

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