Using VLOOKUP with cell references

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!