VBA error, Unable to set the FormulaArray property of the Range class

[vrsharma]

Hi All,

Please help me solving this error. I have read several post on this error but still I am unable still unable to find solution.
This error occurs since below array formula have extended more than 255 char.
Please help me how should I break this formula to make my code work. I am using Excel 2016 version. Thanks in advance.


Sub Macro2()


Range("C16").Select


Selection.FormulaArray = _ "=IF(ROWS(Template!R16C:RC)>R8C6,"""",INDEX(THICK!R3C1:R1000C[-2],SMALL(IF(THICK!R3C1:R1000C1>=Template!R7C3,IF(THICK!R3C1:R1000C1<=Template!R8C3,IF(THICK!R3C1:R1000C1>=Template!R9C3,IF(THICK!R3C1:R1000C1<=Template!R10C3,ROW(THICK!R3C1:R1000C1)-ROW(THICK!R3C1)+1)))),ROWS(Template!R16C:RC))))"
Selection.AutoFill Destination:=Range("C16:C21")

Range("C16:C21").Select
Range("B14").Select


End Sub

 

[Domenic]

Try...

Sub Macro2()

    Dim formulaPart As String
    
    formulaPart = "SMALL(IF(THICK!$A$3:$A$1000>=Template!$C$7,IF(THICK!$A$3:$A$1000<=Template!$C$8," & _
                        "IF(THICK!$A$3:$A$1000>=Template!$C$9,IF(THICK!$A$3:$A$1000<=Template!$C$10," & _
                        "ROW(THICK!$A$3:$A$1000)-ROW(THICK!$A$3)+1)))),ROWS(Template!C$16:C16))"


    With Range("C16")
        .FormulaArray = "=IF(ROWS(Template!C$16:C16)>$F$8,"""",INDEX(THICK!$A$3:A$1000,X_X_X))"
        .Replace "X_X_X", formulaPart, xlPart
        .AutoFill Destination:=Range("C16:C21")
    End With


End Sub

Hope this helps!

 

[vrsharma]

Thank you so much its worked. You saved my life.