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.
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.