vbscript to run formula down on button click

[projectile]

Trying to create button, so when clicked, it will automatically run formula down column L , if column A contains text. However Excel is complaining about the IF statement I'm trying to run down.

Doen't like the line in RED

Using Excel 2003.



Private Sub runFormula_Click()
Dim LastRow As Long
With ActiveSheet

LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

.Range("L" & LastRow).Resize(1, 3).FormulaR1C1 = "=IF(LEFT(F6,2)="--","NO_SWIPE",IF(C6=105,IF(F6<=SHIFTS!$C$5,"on_time",F6-SHIFTS!$C$5),IF(C6=106,IF(F6<=SHIFTS!$C$6,"on_time",F6-SHIFTS!$C$6),IF(C6=99,IF(F6<=SHIFTS!$C$7,"on_time",F6-SHIFTS!$C$7),IF(C6=102,IF(F6<=SHIFTS!$C$8,"on_time",F6-SHIFTS!$C$8),IF(F6<=SHIFTS!$C$4,"on_time",F6-SHIFTS!$C$4))))))"
.Range("L" & LastRow).Resize(1, 1).Name = "Is late?"

End With
End Sub


any ideas?

 

[JoeMo]

Your formula is in A1 notation, not R1C1 so use Range.Formula rather than Range.FormulaR1C1. Also, you need to double all quote marks so "abc" should be ""abc"".

 

[Andrew Poulsom]

This compiles OK:

.Range("L" & Lastrow).Resize(1, 3).Formula = "=IF(LEFT(F6,2)=""--"",""NO_SWIPE"",IF(C6=105,IF(F6<=SHIFTS!$C$5,""on_time"",F6-SHIFTS!$C$5),IF(C6=106,IF(F6<=SHIFTS!$C$6,""on_time"",F6-SHIFTS!$C$6),IF(C6=99,IF(F6<=SHIFTS!$C$7,""on_time"",F6-SHIFTS!$C$7),IF(C6=102,IF(F6<=SHIFTS!$C$8,""on_time"",F6-SHIFTS!$C$8),IF(F6<=SHIFTS!$C$4,""on_time"",F6-SHIFTS!$C$4))))))"

Not that your formula is in A1 reference style so you should be using the Formula property, not the FormulaR1C1 property.

 

[projectile]

thanks, that worked.