CODEWITHSUNDEEP
excelvba
gurpreet kaur
gurpreet kaur

Reputation: 23

how to make vlookup range with variables?

line 1: ActiveCell = "=vlookup(a2:a18,sheet1!a2:l150,match(b1,sheet1!a2:l2,0),0)"


line 2: ActiveCell=  "=vlookup("a2:a"&i,sheet1!a2:l150,match(b1,sheet1!a2:l2,0),0)"

Value i is a variable, say i= 7.

I want syntax in line 2 to work but getting some syntax error. Please help.

Upvotes: 1

Views: 1550

Answers (2)

Simon1979
Simon1979

Reputation: 2108

ActiveCell = "=vlookup(a2:a" & i & ",sheet1!a2:l150,match(b1,sheet1!a2:l2,0),0)"

Is there a reason you are looking up a range? Never done that myself, should you be using the below?

ActiveCell = "=vlookup(a" & i & ",sheet1!a2:l150,match(b1,sheet1!a2:l2,0),0,0)"

Upvotes: 0

Santosh
Santosh

Reputation: 12353

It should be 

ActiveCell = "=vlookup(A2:A" & i & ",sheet1!a2:l150,match(b1,sheet1!a2:l2,0),0)"

Upvotes: 1

Related Questions