Excel VBA - Runtime Error 1004

[bryan___]

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)" & ""

 

[JLGWhiz]

More liklely the '&Chr(10)&' line feeds. If you use line breaks in Excel formulas it needs to be '&Char(10)&'

 

[JLGWhiz]

I ran out of edit time on previous post.
I am not sure what you are trying to do with the line feed, but in Excel formulas the Char(10) is used to place cell values beneath each other in the return cell. Example:
Wrap text must be on
Formula in cell C3:

=A2&Char(10)&B2&Char(10)&C2

 

[bryan___]

I ran out of edit time on previous post.
I am not sure what you are trying to do with the line feed, but in Excel formulas the Char(10) is used to place cell values beneath each other in the return cell. Example:
Wrap text must be on
Formula in cell C3:

=A2&Char(10)&B2&Char(10)&C2

The line feeds are not necessary. I used the "record macro" feature to put in the formula, so I didn't have to type it into R1C1 style by hand. I got rid of those parts to reduce complications. After getting rid of the Chr(10) parts, I still get the same runtime error. Here is what I am trying to do:

Range("B1").Value = 0
Range("B2").Select

Selection.FormulaArray = "=IFERROR(IF(RC[-1]=1,""""&INDEX(R1C2:R[-1]C,s_one,IF(RC[-1]>R[-1]C[-1],R[-1]C&"".1"",IF(RC[-1]=R[-1]C[-1],s_two,s_three))),""""&0)"

Selection.Replace "s_one", "MAX(--(R1C1:R[-1]C[-1]=RC[-1])*(ROW(R1C1:R[-1]C[-1]))))+1"

Selection.Replace "s_two", "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"

Selection.Replace "s_three", "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"