agnesz
05-29-2007, 09:48 AM
hi there -
this is my first time posting a thread, but i've been an avid visitor to this site as its SUPER HELPFUL. I'm attempting to include in vba a vlookup formula which needs to reference the lookup range to a different tab name every week.
I want there to be a user prompt which requests the week number and then uses that week number in the vlookup formula as the tab name. Below is what I currently have, but it bugs out on me every time.
Dim MyInput
MyInput = InputBox("Enter PLans tab name")
Range("Y2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC17,'[PLan.xls] & MyInput & "'!R2C22:R1022C36),4,FALSE)),0,(VLOOKUP(RC17,indirect("'[PLan.xls] & MyInput & "'!R2C22:R1022C36)'!R2C22:R1022C36,4,FALSE)))"
Any help would be greatly appreciated...
this is my first time posting a thread, but i've been an avid visitor to this site as its SUPER HELPFUL. I'm attempting to include in vba a vlookup formula which needs to reference the lookup range to a different tab name every week.
I want there to be a user prompt which requests the week number and then uses that week number in the vlookup formula as the tab name. Below is what I currently have, but it bugs out on me every time.
Dim MyInput
MyInput = InputBox("Enter PLans tab name")
Range("Y2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC17,'[PLan.xls] & MyInput & "'!R2C22:R1022C36),4,FALSE)),0,(VLOOKUP(RC17,indirect("'[PLan.xls] & MyInput & "'!R2C22:R1022C36)'!R2C22:R1022C36,4,FALSE)))"
Any help would be greatly appreciated...