reference myinput in vlookup formula

[agnesz]

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...

[Bob Phillips]

[vba]

Dim MyInput
Dim sFormula As String

MyInput = InputBox("Enter PLans tab name")

sFormula = "=IF(ISERROR(VLOOKUP(RC17,'[PLans.xls]" & MyInput & "'!R2C22:R1022C36,4,FALSE)),0," & _
"VLOOKUP(RC17,'[PLans.xls]" & MyInput & "'!R2C22:R1022C36,4,FALSE))"
Range("Y2").FormulaR1C1 = sFormula
[/vba]

[agnesz]

THANK YOU THANK YOU THANK YOU!