Long Array Formula in VBA (Help)

Hi,

After some help with a long array formula in VBA. I have never array formulas in VBA before but from what I've read they are limited to 255 characters. To overcome this they must be reduced in length using the "replace" function.

Using examples, I came up with the below but the code fails when I run at the ".FormulaArray = theFormulaPart1" point with the Run-time error 1004 "Unable to set the ForumulaArray property of the arrange class"

Is anybody able to shed any light as to where I'm going wrong?

Thanks in advance,
Matt

Formula:

Dim theFormulaPart1 As String

    Dim theFormulaPart2 As String

    Dim theFormulaPart3 As String

    Dim theFormulaPart4 As String



    theFormulaPart1 = "=IF(ISERROR(FIND(""RET_"",RC[1])),IF(ISERROR(FIND(""NULL"",RC[1])),LEFT(TRIM(RIGHT(RC[1],LEN(RC[1])-5)),FIND("" "",TRIM(RIGHT(RC[1],LEN(RC[1])-5)))),IF(MID(RC[1],13,1)=""1"",""X_X_X)"")"

                          

    theFormulaPart2 = "INDEX(LST_RET!C[2],MATCH(LST_RETSUBUNIT!RC[-2]&LST_RETSUBUNIT!RC[-1],LST_RET!C[-1]&LST_RET!C,0),1),""NULL"")),IF(ISERROR(FIND("" "",TRIM(RIGHT(RC[1],LEN(RC[1])+1-LEN(LEFT(RC[1],""Y_Y_Y)"")"

        

    theFormulaPart3 = "FIND(""RET_"",RC[1]))))))),TRIM(RIGHT(RC[1],LEN(RC[1])+1-LEN(LEFT(RC[1],FIND(""RET_"",RC[1]))))),TRIM(LEFT(TRIM(RIGHT(RC[1],LEN(RC[1])+1-LEN(LEFT(RC[1],FIND(""RET_"",RC[1]))))),""Z_Z_Z)"")"



    theFormulaPart4 = "FIND("" "",TRIM(RIGHT(RC[1],LEN(RC[1])+1-LEN(LEFT(RC[1],FIND(""RET_"",RC[1]))))))))))"



    With ActiveSheet.Range("C1")

    .FormulaArray = theFormulaPart1

    .Replace """X_X_X)"")", theFormulaPart2

    .Replace """Y_Y_Y)"")", theFormulaPart3

    .Replace """Z_Z_Z)"")", theFormulaPart4