Array Formula within Macro not running

[ScabbyDog]

I keep getting the "Unable to set FormulaArray property of the Range class error" error when trying to run the below macro. It's an array formula. Is it because it's over 256 characters? Any way of fixing it so it runs?

Sub Ftest()

Sheets("Seniors").Select

Range("ES3").Select
    Selection.FormulaArray = "=IFERROR(IF(IFERROR(MATCH(1,IF(RC[599]:RC[678]<>0,IF(RC[599]:RC[678]<>"""",1)),0),99)<"

[YasserKhalil]

Hi ScabbyDog
Here's a sample of what you can do (split the formula into two parts) .Here's an example and you can change to suit your array formula

Sub Test()
    Dim Part1 As String
    Dim Part2 As String
    
    '=IF(COUNTIFS(A13:A51,">="&$D$3,A13:A51,"<" & $E$3,C13:C51,$E$5)>0,IF($D$5="Last",MAX(IF(A13:A51>=$D$3,IF(A13:A51<$E$3,IF(C13:C51=$E$5,A13:A51)))),MIN(IF(A13:A51>=$D$3,IF(A13:A51<$E$3,IF(C13:C51=$E$5,A13:A51))))),"Not Found")
    '--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Part1 = "=IF(COUNTIFS(A13:A51,"">=""&$D$3,A13:A51,""<"" & $E$3,C13:C51,$E$5)>0," & "X_X_X())"
                         
    Part2 = "IF($D$5=""Last"",MAX(IF(A13:A51>=$D$3,IF(A13:A51<$E$3,IF(C13:C51=$E$5,A13:A51)))),MIN(IF(A13:A51>=$D$3,IF(A13:A51<$E$3,IF(C13:C51=$E$5,A13:A51))))),""Not Found"")"
       
    With Sheets("Seniors").Range("ES3")
        .FormulaArray = Part1
        .Replace "X_X_X())", Part2
    End With
End Sub

[ScabbyDog]

Hi ScabbyDog
Here's a sample of what you can do (split the formula into two parts) .Here's an example and you can change to suit your array formula

Sub Test()
    Dim Part1 As String
    Dim Part2 As String
    
    '=IF(COUNTIFS(A13:A51,">="&$D$3,A13:A51,"<" & $E$3,C13:C51,$E$5)>0,IF($D$5="Last",MAX(IF(A13:A51>=$D$3,IF(A13:A51<$E$3,IF(C13:C51=$E$5,A13:A51)))),MIN(IF(A13:A51>=$D$3,IF(A13:A51<$E$3,IF(C13:C51=$E$5,A13:A51))))),"Not Found")
    '--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Part1 = "=IF(COUNTIFS(A13:A51,"">=""&$D$3,A13:A51,""<"" & $E$3,C13:C51,$E$5)>0," & "X_X_X())"
                         
    Part2 = "IF($D$5=""Last"",MAX(IF(A13:A51>=$D$3,IF(A13:A51<$E$3,IF(C13:C51=$E$5,A13:A51)))),MIN(IF(A13:A51>=$D$3,IF(A13:A51<$E$3,IF(C13:C51=$E$5,A13:A51))))),""Not Found"")"
       
    With Sheets("Seniors").Range("ES3")
        .FormulaArray = Part1
        .Replace "X_X_X())", Part2
    End With
End Sub

Mhmm not working for me. For some reason I can't post the whole array formula here without a firewall intervening! Will attach sample book below.

[ScabbyDog]

My next post will contain sample workbook with the array formula inside.

[YasserKhalil]

Can you post a sample workbook ...?
No need for duplicate threads (this is forbidden : Cross-Post)

[ScabbyDog]

Final-Sample1.xlsm

Sample attached with code in macro. If someone can get it to run so that it puts the array formula in the cell then that's all I need!

[YasserKhalil]

May be the Test sub will do the task

[ScabbyDog]

Unfortunately not Yasser. Needs to be an array and all that does is return #VALUE!

[ScabbyDog]

Not even sure what that Evaluate formula you sent me does Yasser?

[ScabbyDog]

Anyone else able to help me out with the sample workbook?

[YasserKhalil]

Your sample is not the required .. I need a sample workbook with the array formula in the spreadsheet itself to try to offer help