Vlookup syntax with variables

[chad1222]

Can anyone help me fix this variable syntax with VLOOKUP?
it gives me type mismatch.

Sub calculate()

col = 3
row = 29

For row = 29 To 70
    If Sheet14.Cells(row, col).Value > 0 Then
        col = col + 2
        
        Sheet14.Cells(row, col).Formula = "=VLOOKUP" & Sheet14.Cells(row, col) & ",AI2:AJ30,2,TRUE)"
    Else
    
    End If
    
Next row

End Sub

is there another way i can do this with incrementing variables?
Chad

 

[just_jon]

Untested --

Sheet14.Cells(row, col).Formula = "=VLOOKUP(Sheet14!" & Sheet14.Cells(row, col).Address() & ",AI2:AJ30,2,TRUE)"

 

[BuddieB]

Try this:

        Sheet14.Cells(row, col).Formula = "=VLOOKUP" & Sheet14.Cells(row, col).address & ",AI2:AJ30,2,TRUE)"

 

[chad1222]

Thanks guys, it got it to work playing with one of those .
Chad