VB Code in Excel

[bamenell]

I am trying to write a macro that will look at data in a cell and determine whether it is greater or less than an amount. Then based on that input one of two formulas.
Here's is what I have come up with so far:

Sub InsertFormula()
If Range(o12) < 1000 Then
Worksheets("Dispatch").Range("o12").Formula = "=IF(O12<1000,CONCATENATE(0,MID(O12,1,1),":",MID(O12,3,2),0))"

Else
Worksheets("Dispatch").Range("o12").Formula = "=CONCATENATE(MID(O12,1,2),":",(MID(O12,3,2)))"
End If
End Sub

The formulas work as expected in the worksheet itself, but in the macro I get an error:

Expected: Line number or label or statement or end of statment.

VB highlights the end of the formula that I want inputed - :",(MID(O12,3,2)))"

What am I missing??

Thanks in advance for anyone's help.

Brent

[Martin Werner]

You have to escape the formula's " signs by doubling them!

[bamenell]

That seemed to work!
Now when I run the macro I get a Runtime error: "9" Subscript out of range.
Do I need to use a Dim statement?