Unable to use INDIRECT formula in VBA due to apostrophe in formula

Question

I am trying to write the following formula in VBA however, since the formula contains an apostrophe, the part after the apostrophe gets commented out. Can anyone help please?

Dim LastcolGen As Long, lastrowfilter As Long 

Range(Cells(6, 10), Cells(lastrowfilter, LastcolGen)).Formula = _
 "IF(J$4="FactTrancheValue",INDEX(INDIRECT(J$4),ROW()-4,MATCH(J$5,INDIRECT("'"&J$4&"'!"&"A1:JJ1"),0)),INDEX(INDIRECT(J$4),MATCH(INDIRECT(J$3&ROW()),INDIRECT("'"&J$4&"'!"&"A1:A999999"),0),MATCH(J$5,INDIRECT("'"&J$4&"'!"&"A1:JJ1"),0)))" 
Range(Cells(6, 10), Cells(lastrowfilter, LastcolGen)).Select 
Selection.Copy 
Selection.PasteSpecial Paste:=xlPasteValues

Thanks, AB

Answer 1

Try this (untested)

Range(Cells(6, 10), Cells(lastrowfilter, LastcolGen)).Formula = _
    "=IF(J$4=""FactTrancheValue"",INDEX(INDIRECT(J$4)," & _
    "ROW()-4,MATCH(J$5,INDIRECT(""'""&J$4&""'!""&""A1:JJ1""),0))," & _
    "INDEX(INDIRECT(J$4),MATCH(INDIRECT(J$3&ROW())," & _
    "INDIRECT(""'""&J$4&""'!""&""A1:A999999""),0)," & _
    "MATCH(J$5,INDIRECT(""'""&J$4&""'!""&""A1:JJ1""),0)))"

(edited according to your update)

When setting that formula to a single cell I get no errors from VBA (though the formula itself returns #REF!)

If this doesn't work then make sure your Range() reference is correct.