I want to use variables I declared in the actual formula of the print cell. For instance, this code works fine without errors - HOWEVER, I want to use variables "rownum" and "colnum" as cell coordinates to find the lookup value, i.e the first argument in the vlookup equation.
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
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(16, 12).Value
Sheet9.Cells(y, x).Resize(resizeval, 1).Formula ="=VLOOKUP(Sheet9.Cells(rownum,colnum),Sheet8!" _ ' these are the lines that need addressing
& Sheets("Price Data").Range("A12").CurrentRegion.Address & ",Sheet1!L$29,FALSE)" ' the current region bit is working fine
End Sub
What literally ends up put in the cell: " =VLOOKUP(Sheet9.Cells(rownum,colnum),Sheet8!$A$11:$I$44,Sheet1!L$29,FALSE) " ^which ends up being a #NAME error
How do I fix this so the excel cell terminology recognizes code terminology? I.e, "=vlookup(Sheet9.Cells(rownum,colnum),Sheet8! ... "
I want the vlookup value to be the cell on Sheet 9 given by the coordinates [rownum,colnum] - and not have the formula literally say that!
Thank you Everyone! Billy
