PDA

View Full Version : reference myinput in vlookup formula



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

Bob Phillips
05-29-2007, 02:15 PM
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

agnesz
05-30-2007, 10:40 AM
THANK YOU THANK YOU THANK YOU!