Issues inserting formula into cell via VBA

[Range]

I'm having an expexted end of statement issues trying to insert the below formula into a cell via VBA - any ideas?

ACTUAL FORMULA

=IF(ISERROR(SEARCH("??:??",A2)),IF(ISERROR(MATCH(TEXT(C1+1/1440,"""*""hh:mm""*"""),A:A,0)),"",C1+1/1440/(MATCH(TEXT(C1+1/1440,"""*""hh:mm""*"""),A:A,0)-MATCH(TEXT(C1,"""*""hh:mm""*"""),A:A,0))),MID(A2,SEARCH("??:??",A2),5)+0)

MY VBA ATTEMPTED CONVERSION

  ActiveCell.FormulaR1C1 = _       
                "=IF(ISERROR(SEARCH(""??:??"",A2)),IF(ISERROR(MATCH(TEXT(C1+1/1440,""""*"""hh:mm"""*""""),A:A,0)),"""",C1+1/1440/(MATCH(TEXT(C1+1/1440,""""*"""hh:mm"""*""""),A:A,0)-MATCH(TEXT(C1,""""*"""hh:mm"""*""""),A:A,0))),MID(A2,SEARCH(""??:??"",A2),5)+0)"

 

[sheetspread]

You're not using R1C1, does dropping that from the formula property help?

Otherwise try it without the = in the quotes to see if you can print part of the string


This worked (or at least printed something) when I tried it:

Sub formpr()
Range("A1").Formula = "=IF(ISERROR(SEARCH(""??:??"",A2)),IF(ISERROR(MATCH(TEXT(C1+1/1440,""""*""hh:mm""*""""),A:A,0)),"""",C1+1/1440/(MATCH(TEXT(C1+1/1440,""""*""hh:mm""*""""),A:A,0)-MATCH(TEXT(C1,""""*""hh:mm""*""""),A:A,0))),MID(A2,SEARCH(""??:??"",A2),5)+0)"
End Sub

 

[Range]

It really is quite peculiar indeed.

You're not using R1C1, does dropping that from the formula property help?
Nope - same problem.

Otherwise try it without the = in the quotes to see if you can print part of the string
The string is pasted as value fine but not as a formula. I suspect is has something to do with "hh:mm" which I've amended to ""hh:mm"".... but still getting my brain frazzled.

 

[sheetspread]

Does the code I posted print what you want?