Vlookup only using first lookup value when resizing formula

[billyheanue]

Hi Everyone,
I have rownum and colnum set to a value -- these are row and column coordinates for the FIRST lookup value. However, when I resize the formula to do the next value (which would have coords [rownum +1, colnum], the formula is still doing the vlookup with the same lookup value.

Does anyone know how to incorporate the lookup value's row number increasing by 1 each time, when the formula is resized?

Thank you everyone!

Private Sub CommandButton8_Click()
Dim rownum As Long
Dim colnum As Long
Dim x As Long
Dim y As Long
Dim colindexval As Double
Dim resizeval As Double
Dim fVLOOKUP As String




rownum = Sheet1.Cells(28, 21).Value
colnum = Sheet1.Cells(27, 21).Value
x = Sheet1.Cells(20, 21).Value
y = Sheet1.Cells(21, 21).Value
resizeval = Sheet1.Cells(19, 12).Value
colindexval = Sheet1.Cells(29, 12).Value


fVLOOKUP = "=VLOOKUP(@1,'@2'!@3,@4,FALSE)"


fVLOOKUP = Replace(fVLOOKUP, "@1", Sheet9.Cells(rownum, colnum).Value)
fVLOOKUP = Replace(fVLOOKUP, "@2", Sheets("Price Data").Name)
fVLOOKUP = Replace(fVLOOKUP, "@3", Sheets("Price Data").Range("A12").CurrentRegion.Address)
fVLOOKUP = Replace(fVLOOKUP, "@4", colindexval)


Sheet9.Cells(y, x).Resize(resizeval, 1).Formula = fVLOOKUP


    
End Sub

 

[Marcelo Branco]

See if this works

Instead of this code line

fVLOOKUP = Replace(fVLOOKUP, "@1", Sheet9.Cells(rownum, colnum).Value)

try

fVLOOKUP = Replace(fVLOOKUP, "@1", Sheet9.Cells(rownum, colnum).Address(False, True))

Hope this helps

M.

 

[billyheanue]

That works great. Using Address in that case is much better than using Value -- thanks!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.

 

[billyheanue]

One more thing Marcelo,

Do you know how to make the new range (resizeval, 1) the active cells? I am trying to set it as the current selection and then have the formats pasted from the column to the left of it

 

[billyheanue]

I tried

fVLOOKUP = "=VLOOKUP(@1,'@2'!@3,@4,FALSE)"


fVLOOKUP = Replace(fVLOOKUP, "@1", Sheet9.Cells(rownum, colnum).Address(False, True))
fVLOOKUP = Replace(fVLOOKUP, "@2", Sheets("Price Data").Name)
fVLOOKUP = Replace(fVLOOKUP, "@3", Sheets("Price Data").Range("A12").CurrentRegion.Address)
fVLOOKUP = Replace(fVLOOKUP, "@4", colindexval)


Sheet9.Cells(y, x).Resize(resizeval, 1).Formula = fVLOOKUP
Range(Sheet9.Cells(y, x), (Sheet9.Cells(y, x).End(xlDown))).Select

and got err1004: Method 'Range' of object'_Worksheet' failed

 

[Marcelo Branco]

Just activate Sheet9 before the Select

Sheet9.Activate

M.

 

[billyheanue]

Just activate Sheet9 before the Select

Sheet9.Activate

M.

Can you elaborate more, on which line?

 

[Marcelo Branco]

Before

Range(Sheet9.Cells(y, x), (Sheet9.Cells(y, x).End(xlDown))).Select

M.