vlookup formula with variable range in it

0
votes

I am trying to add variables into a vlookup formulated function

The one shown below is able to run 

Cells(17, LastCol + 1).Formula = "=vlookup(" & Sheets("Sheet1").Range("C17").Address(False, False) & ",'Temp'!B:C,2,False)"

But what I am trying to do is to add varibles to replace B:C found in the formula which will look like this

Cells(17, LastCol + 1).Formula = "=vlookup(" & Sheets("VPC4").Range("C17").Address(False, False) & ",'Temp'!Cells(Row1,2):Cells(Row2,3),2,False)"

where 

Row1 = Sheets("Temp").Cells.Find(What:="test", SearchDirection:=xlUp, SearchOrder:=xlByRows).Row
Row2 = Sheets("Temp").Cells.Find(What:="test1", SearchDirection:=xlUp, SearchOrder:=xlByRows).Row

enter image description here

It isn't performing what i am expected and it showed $name instead. I need the vba to be a formulated output therefore, .formula was used. Please advise.

1
excelvba

1 Answers

1
votes

You need to include Row1 and Row2 variable values in this way:

Cells(17, LastCol + 1).Formula = "=vlookup(" & Sheets("VPC4").Range("C17").Address(False, False) & ",'Temp'!" & _
                                  range(Cells(Row1,2),Cells(Row2,3)).address & _
                                                         ",2,False)"