CODEWITHSUNDEEP
excelvbavlookup
PingHung Wu
PingHung Wu

Reputation: 11

Vlookup with Column as variable

I want to use vlookup, and the column number as a variable which is dependent on users input. 

Dim MONTH As Variant
MONTH = InputBox("month number ex: Apr=4")

Range("F7").Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],C[8]:C[22],3+MONTH,FALSE),0)"

The function can run through but the column doesn't really take in user's input. So I am actually searching "3+MONTH"th column.

Does anyone know how to properly solve it so i can really take user's input into the formula?

Upvotes: 1

Views: 1266

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71247

"=IFERROR(VLOOKUP(RC[-1],C[8]:C[22],3+MONTH,FALSE),0)"

That's a string literal; you need to "chop it" and insert your variable in there.

Use the string concatenation operator (&) to put the pieces together

"=IFERROR(VLOOKUP(RC[-1],C[8]:C[22]," & 3+MONTH & ",FALSE),0)"

Upvotes: 3

Related Questions