VLOOKUP & variable worksheet names

  • Thread starter Thread starter Paul Kendall
  • Start date Start date
P

Paul Kendall

I am using a VLOOKUP function in my macro, which calls upon a workbook
called "master.xls" and a worksheet called "Worksheet ABC" as follows:

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC4,'[master file.xls]Worksheet
ABC'!R2C1:R65536C7,7,FALSE)"

However, I am trying to avoid hard-coding the workbook name and the
worksheet name in the VLOOKUP formula. I was thinking about using
variables for the workbook and worksheet name (which would be assigned
different values as my macro iterates through a loop), but I'm not sure
what the code would look like using the VLOOKUP function.

Does anyone know how I might generalize this script to avoid hardcoding
the workbook and worksheet names above? My apologies if the question
is too elementary.

Thanks in advance to the kind soul willing to help out!!

-Paul
 
sBk = "master file.xls"
sSh = "WorksheetABC"

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC4,'[" & sBk & "]" & _
sSh & "'!R2C1:R65536C7,7,FALSE)"
 
Back
Top