Issues with formulae in VBA

[Carty]

Hello.

I would like some help to understand an issue I'm having trying to use VBA to write a formula.

In workbookA, worksheetC, cell E31 I have a formula that references worksheetC B9 and 3 columns in worksheetB. It looks like this:

"("&TEXT(INDEX('worksheetB'!$G$1:$G$65552,MATCH(VALUE(MID($B$9,4,5)),'worksheetB'!$O$1:$O$65552,)),"0.0E+00")&"-"&TEXT(INDEX('worksheetB'!$K$1:$K$65552,MATCH(VALUE(MID($B$9,4,5)),'worksheetB'!$O$1:$O$65552,)),"0.0E+00")&")"
and the result is this:
(1.2E+03 - 2.4E+03)

If I copy this formula into workbookB worksheetC cell E31(which looks exactly like worksheetC on workbookA and B9 has the same value) and close workbookA [workbookA.xlsm] is inserted between ' and worksheet. The result is the same

In workbookA I write

[COLOR=#222222][FONT=Verdana]Workbooks.open(“workbookB”)[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Sheets("SheetC").cells(31, 5).formulaR1C1 ="=""(""&TEXT(INDEX('[WorkbookA.XLSM]worksheetB'!R1C7:R65552C7,MATCH(VALUE(MID(R9C2,4,5)),'[WorkbookA.XLSM]worksheetB'!R1C15:R65552C15,)),""0.0E+00"")&""- ""&TEXT(INDEX('[WorkbookA.XLSM]worksheetB'!R1C11:R65552C11,MATCH(VALUE(MID(R9C2,4,5)),'[WorkbookA.XLSM]worksheetB'!R65552C15,)),""0.0E+00"")&"")"""[/FONT][/COLOR]

An application or object defined error runtime 1004occurs

Also, as the formula is quite long, it goes across 3 pagewidths!
When I have written long lines of code before I have used “_”to break the code up and display it on 1 page width but this throws up an end of statement compile error.

I have probably done something stupid but cannot see where.

Paul