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

[MAM8433]

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.

 

[jasonb75]

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.

Workbooks(txtMacrofile).Worksheets("Provider Report").Range(txtColLetter & "9").Formula = "=VLOOKUP($a9,'[" & txtTAwbNm & "]" & intWSNum & "'!$A:$J,9,0)"

 

[MAM8433]

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]

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.