I need to write this array formula into a cell, but every time I run my macro, I get a runtime error 1004. From my understanding, this is because my array formula is over 255 characters. I've tried multiple methods of replacing parts of the formula with temporary strings (to make the formula <255 characters) and replacing them afterwards, but I still get the same runtime error. I was wondering if anyone could help. Here is the code:
Range("B1").Value = 0
'Selection.FormulaArray = _
"=IFERROR(IF(RC[-1]=1," & Chr(10) & """""&INDEX(R1C2:R[-1]C,MAX(--(R1C1:R[-1]C[-1]=RC[-1])*(ROW(R1C1:R[-1]C[-1]))))+1," & Chr(10) & "IF(RC[-1]>R[-1]C[-1]," & "R[-1]C&"".1""," & Chr(10) & "IF(RC[-1]=R[-1]C[-1]," & Chr(10) & "LEFT(R[-1]C,FIND(""|"",SUBSTITUTE(R[-1]C,""."",""|"",LEN(R[-1]C)-LEN(SUBSTITUTE(R[-1]C,""."","""")))))&RIGHT(R[-1]C,LEN(R[-1]C)-FIND(""|"",SUBSTITUTE(R[-1]C,""."",""|"",LEN(R[-1]C)-LEN(SUBSTITUTE(R[-1]C,""" & "."","""")))))+1," & Chr(10) & "LEFT(R[-1]C,FIND(""|"",SUBSTITUTE(R[-1]C,""."",""|"",RC[-1]-1)))&MID(R[-1]C,FIND(""|"",SUBSTITUTE(R[-1]C,""."",""|"",RC[-1]-1))+1,FIND(""|"",SUBSTITUTE(R[-1]C,""."",""|"",RC[-1]))-FIND(""|"",SUBSTITUTE(R[-1]C,""."",""|"",RC[-1]-1)))+1))),""""&0)" & ""
Range("B1").Value = 0
'Selection.FormulaArray = _
"=IFERROR(IF(RC[-1]=1," & Chr(10) & """""&INDEX(R1C2:R[-1]C,MAX(--(R1C1:R[-1]C[-1]=RC[-1])*(ROW(R1C1:R[-1]C[-1]))))+1," & Chr(10) & "IF(RC[-1]>R[-1]C[-1]," & "R[-1]C&"".1""," & Chr(10) & "IF(RC[-1]=R[-1]C[-1]," & Chr(10) & "LEFT(R[-1]C,FIND(""|"",SUBSTITUTE(R[-1]C,""."",""|"",LEN(R[-1]C)-LEN(SUBSTITUTE(R[-1]C,""."","""")))))&RIGHT(R[-1]C,LEN(R[-1]C)-FIND(""|"",SUBSTITUTE(R[-1]C,""."",""|"",LEN(R[-1]C)-LEN(SUBSTITUTE(R[-1]C,""" & "."","""")))))+1," & Chr(10) & "LEFT(R[-1]C,FIND(""|"",SUBSTITUTE(R[-1]C,""."",""|"",RC[-1]-1)))&MID(R[-1]C,FIND(""|"",SUBSTITUTE(R[-1]C,""."",""|"",RC[-1]-1))+1,FIND(""|"",SUBSTITUTE(R[-1]C,""."",""|"",RC[-1]))-FIND(""|"",SUBSTITUTE(R[-1]C,""."",""|"",RC[-1]-1)))+1))),""""&0)" & ""