Using VLOOKUP with cell references

[JamesFletcher]

Hi All,

I have a problem with a peice of code and I'm sure it's just the syntax that I'm getting wrong.

The following code is just a test to input a Vlookup formula to reference the cell next to itself. The adjacent cell will change which is why I can't just put in a static cell reference...

Sub Test_Vlookup()
Dim rowPosition As Integer
rowPosition = 30
Cells(rowPosition, 5).Value = "=VLOOKUP(" & Cells(rowPosition, 6).Value & "),'Sheet2'!$A$2:$F$18,4,False)"
End Sub

Please help!

[AlvaroSiza]

Remove the .Value from the left hand side of the equation. Excel is confused as the right side is a string.

[JamesFletcher]

I've tried that but I get the same error (Application-defined or object-defined error)

Sub Test_Vlookup()
Dim rowPosition As Integer
rowPosition = 30
Cells(rowPosition, 5) = "=VLOOKUP(" & Cells(rowPosition, 6).Value & "),'Sheet2'!$A$2:$F$18,4,False)"
End Sub

[JamesFletcher]

I managed to fix it...I was clearly over-complicating things again!

Sub Test_Vlookup()
Dim rowPosition As Integer
rowPosition = 30
Cells(29, 5).Value = "=VLOOKUP(F" & rowPosition & ",'Sheet2'!$A$2:$F$18,4,False)"
End Sub