Solved: Syntax error in formula...where am I going wrong?

[Lester]

Hi
I can define this formula in the excel cell quite happily and it works fine, returning the correct value from the 'Charge Rates Look-up' worksheet...

[vba]VLOOKUP(A2&B2&D2,'Charge Rates Look-up'!$1:$100,7, False)[/vba]

However, when I try to define it in a macro (see below) to make it apply to the entire column, there is a syntax error in the .Formula statement.

[vba]Public Sub InsertLabourOrConsultancy()
Dim iLastRow As Long
With ActiveSheet
.Cells(1, "J").Value = "Labour/Consultancy"
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("J2").Resize(iLastRow - 1).Formula = _
"=VLOOKUP(A2&B2&D2,'Charge Rates Look-up'!$1:$100,7, False)"
End With
End Sub[/vba]

Anyone know what I'm doing wrong? Must be something simple!
Many thanks, Lester.

[Lester]

Actually, no...this aint gonna do what I need it to do (even if the syntax is corrected). It's gonna repeat A2&B2&D2, but i need it to move on by one each time; i.e. A3&B3&D3, then A4&B4&D4 and so on.

Can someone help me with this problem, also?!

Thanks again.

[Norie]

Lester

That formula should change the cell references as required.

And the code works fine for me with no syntax errors.

[Lester]

Lester

That formula should change the cell references as required.

And the code works fine for me with no syntax errors.

I'm clearly having a bad day (continuation of a near-hit by a car whilst cycling to work this morning).

You're absolutely right, Norie. It does work. Thanks for your efforts. Much appreciated.
Regards
Lester.