Shortening a FormulaArray in VBA

[Declamatory]

Hi Folks,

I have the following formula in VBA which is getting a run time error I think because the formula exceeds 255 characters. I understand there is a way of breaking up the formula and was wondering if anybody would be able to break up the following so it could run in VBA.

Thanks

Range("B17").Select
    Selection.FormulaArray = _
        "=IF(SUM(COUNTIFS(Dealing!C[2],{""A800F"",""C800F""},Dealing!C[21],""Fund Switch Purchase""))>0,""Estimate"",IF(OR(SUMIF(Dealing!C[2],""A800F"",Dealing!C[5])<>0,SUMIF(Dealing!C[2],""C800F"",Dealing!C[5])<>0),""Estimate"",IF(ABS(R[-3]C+R[-3]C[3])=0,""No dealing for ""&TEXT(R[-6]C[-1],""dd/mm/yyyy""),""Confirmed"")))"

 

[Declamatory]

To clarify I think was about creating strings. I found something here https://stackoverflow.com/questions...cel-vba-using-the-replace-method-as-a-workaro

but I'm still not sure what I'm doing or how to apply it to my formula.

Thanks

 

[FormR]

Hi, that formula does not need committing as an array formula. So you can just use:

Range("B17").FormulaR1C1 = "=IF(....