VLOOKUP & variable worksheet names
 

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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

VLOOKUP & variable worksheet names
 

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

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

--
Regards,
Tom Ogilvy


"Paul Kendall" wrote in message
...
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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

VLOOKUP & variable worksheet names
 

Thanks!! I really appreciate your help.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/