VBA syntax to write formula to sheet

[BDrew]

I am attempting to add this formula to the Table of Contents: sheetname("TOC") in column D for a looping routine.
=MATCH("Ω",INDIRECT("'"&TOC!B4&"'!A:A"),1)

I must change "Ω" to ChrW(937) and TOC!B4 to TOC!B & NewRow so that I can increment NewRow=NewRow+1

TOC.Range("D" & NewRow).Formula = "=MATCH(" & ChrW(937) & ",INDIRECT(" & "" &"'TOC!B" & NewRow & "'!A:A"),1)"

the problem starts at INDIRECT(" which excel VBA considers an ending quote and attempting to put more quotes around that isolates the next problem at
'TOC!B which is considered as a note and not part of the formula.

Any assistance would be appreciated.

Brendan

 

[MARK858]

Try

.Formula = _
        "=MATCH(""" & ChrW(937) & """,INDIRECT(""'""&TOC!B" & NewRow & "&""'!A:A""),1)"

 

[sericom]

.Formula = "=MATCH(""Ω"",INDIRECT(""'""&TOC!B4&""'!A:A""),1)"

 

[MARK858]

@sericom, can you actually get the Ω symbol to remain the same in the VBE as I get it changing to ? and so to directly apply it across the range rather than loop I need to do

Range("D3:D20").Formula = "=MATCH("""[COLOR="#FF0000"] & ChrW(937) & [/COLOR]""",INDIRECT(""'""&TOC!B4&""'!A:A""),1)"

 

[sericom]

You're right, it doesn't hold, I was just showing how to sort the OP quote issue.