Array Formula problem in VBA

[iggydarsa]

Hi all

When I enter the following formula manually (with Ctrl+Shift+Enter) it works fine:
=INDEX(tbl_List[Referral Name], MAX((tbl_List[Intake Rep]=B2)*(tbl_List[Clean Doc Count]=MAX(IF(tbl_List[Intake Rep]=B2, IF(tbl_List[Clean Doc %]=MIN(IF(tbl_List[Intake Rep]=B2, tbl_List[Clean Doc %])), tbl_List[Clean Doc Count]))))*(tbl_List[Clean Doc %]=MIN(IF(tbl_Intake[Intake Rep]=B2, tbl_List[Clean Doc %])))*(ROW(tbl_List[Referral Name]))))

but when I use it in VBA like this:
shSand.Range("C2").FormulaArray = "=INDEX(tbl_List[Referral Name], MAX((tbl_List[Intake Rep]=B2)*(tbl_List[Clean Doc Count]=MAX(IF(tbl_List[Intake Rep]=B2, IF(tbl_List[Clean Doc %]=MIN(IF(tbl_List[Intake Rep]=B2, tbl_List[Clean Doc %])), tbl_List[Clean Doc Count]))))*(tbl_List[Clean Doc %]=MIN(IF(tbl_Intake[Intake Rep]=B2, tbl_List[Clean Doc %])))*(ROW(tbl_List[Referral Name]))))"

I get the following error:
Unable to set the FormulaArray property of the Range class

What am i doing wrong?

 

[Domenic]

The FormulaArray property has a limit of 255 characters, which your formula exceeds. Hence the error. As a workaround, you can enter your formula with placeholders for parts of your formula, and then replace them with the actual text for your formula. Here's an example...

    Dim formulaPart1 As String
    formulaPart1 = "tbl_List[Intake Rep]=B2"
    
    Dim formulaPart2 As String
    formulaPart2 = "tbl_List[Clean Doc Count]=MAX(IF(tbl_List[Intake Rep]=B2, IF(tbl_List[Clean Doc %]=MIN(IF(tbl_List[Intake Rep]=B2, tbl_List[Clean Doc %])), tbl_List[Clean Doc Count])))"


    Dim formulaPart3 As String
    formulaPart3 = "tbl_List[Clean Doc %]=MIN(IF(tbl_List[Intake Rep]=B2, tbl_List[Clean Doc %]))"


    With shSand.Range("C2")
        .FormulaArray = "=INDEX(tbl_List[Referral Name], MAX((X_X_X)*(Y_Y_Y)*(Z_Z_Z)*(ROW(tbl_List[Referral Name]))))"
        .Replace "X_X_X", formulaPart1
        .Replace "Y_Y_Y", formulaPart2
        .Replace "Z_Z_Z", formulaPart3
    End With

By the way, I assumed that your meant "tbl_List", not "tbl_Intake", in part of your formula.

Hope this helps!

 

[iggydarsa]

By the way, I assumed that your meant "tbl_List", not "tbl_Intake", in part of your formula.

First of all, how did you even catch that? I am flabbergasted.
And secondly, your code worked like charm.

Previously I saw your other posts on this subject and tried to do it but failed to parse it correctly and kept getting errors.

Thank you again.

 

[Domenic]

You're very welcome, glad I could help, cheers!