PDA

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



Lester
11-05-2007, 04:45 AM
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...

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

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.

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

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

Lester
11-05-2007, 04:51 AM
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
11-05-2007, 05:45 AM
Lester

That formula should change the cell references as required.

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

Lester
11-05-2007, 06:18 AM
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.