Array Formula not working in EXCEL VBA

[Malick]

Hi,
I am struggling to find a solution for an Array Formula for the past 7 days. Hope anyone can help me fix this issue. I know it has a limitation of 255 characters for which I googled and found a solution of breaking the formula in to 2-3 strings and rejoining it, but it is not working. I am constantly getting a message "Unable to set the FormulaArray propoerty of the range class". If I try to enter a very short formula, for example, '=Average(N2:O2)' it works but the below formula is giving error. Hope someone can help me fix this issue. Thanks.

Dim theFormulaPart1 As String    Dim theFormulaPart2 As String
    Dim theFormulaPart3 As String
    Dim theFormulaPart4 As String
    theFormulaPart1 = "=IFERROR(IF(AND(Table1[ @[           Data Consistency] ],NOT(SIGN(Table1[ @[           Data missing] ])))," & _
    "X_X_X())"
    
   theFormulaPart2 = "SUMPRODUCT((IF((OFFSET(" & ColumnLetter & "" & _
    " 2,0,0,1,nbCol" & CBS & "))=""?"",0,1))," & _
    "Lst_MaxWeight_" & CBS & "," & _
    "Y_Y_Y())"
                          
    theFormulaPart3 = "SIGN(TRANSPOSE(FREQUENCY(MATCH(Lst_Indic_" & _
    "" & CBS & "," & _
    "Lst_Indic_" & CBS & ",0)," & _
    "Z_Z_Z())"
                          
    theFormulaPart4 = "COLUMN(OFFSET($S$8,0,0,1,nbCol" & CBS & "-1))" & _
                    "-COLUMN($" & ColumnLetter & "$8)+1)))),""""),""ND"")"
    With Worksheets("Sheet1").ListObjects("Table1").ListColumns(CBS & " Row Score Max").DataBodyRange.Cells(1)
            .FormulaArray = theFormulaPart1
            .Replace "X_X_X())", theFormulaPart2
            .Replace "Y_Y_Y())", theFormulaPart3
            .Replace "Z_Z_Z())", theFormulaPart4
            
            '.FormulaArray = .FormulaR1C1
     '       .Select
     '       DoEvents
     '       Application.SendKeys "{F2}^+~"
    End With

The formula is correct, if I enter it with .Formula property it get inserted and works as desired.

 

[pgc01]

Hi

I did not test the code but I started looking at the code I saw an error in the first part of the formula. I see:

6 opening parentheses
5 closing parentheses

So I'd say it will error out at the first .FormulaArray assignment statement

theFormulaPart1 = "=IFERROR[B][COLOR=#a52a2a]([/COLOR][/B]IF[B][COLOR=#a52a2a]([/COLOR][/B]AND[COLOR=#a52a2a][B]([/B][/COLOR]Table1[@[Data Consistency]],NOT[COLOR=#a52a2a][B]([/B][/COLOR]SIGN[COLOR=#a52a2a][B]([/B][/COLOR]Table1[@[Data missing]][B][COLOR=#0000cd])))[/COLOR][/B],X_X_X[B][COLOR=#a52a2a]([/COLOR][COLOR=#0000cd]))[/COLOR][/B]"

P. S. it would be helpful when you get an error to post the relevant information, like
- what is the line that is highlighted when the error occurs
- what is the error message you get
- the relevant data values
- etc.

 

[Malick]

Thanks PGC for your reply. Sorry I missed out putting all information in my post.
1. The sixth closing parentheses is in my "theFormulaPart4" variable, as it closes the IFERROR function. is this a mistake?
2.

[COLOR=#333333] .FormulaArray = theFormulaPart1[/COLOR]

This line is highlighted when the error is generated, which is not very helpful.
3. The error code is : Runtime error: 1004
[h=1]Unable to set FormulaArray property of the Range class[/h]4. CBS and Columnletter are my variables populated from my combo box and the newly added column (.ListColumns(CBS & " Row Score Max")) in my Table1 , respectively.

 

[pgc01]

1. The sixth closing parentheses is in my "theFormulaPart4" variable, as it closes the IFERROR function. is this a mistake?

Hi

Yes, that is a mistake.

When you break the formula in several parts you must do it in such a way that all the partial formulas respect the formula syntax.

If any of the partial formulas result in an invalid formula it will not be accepted.

In the case you posted the first formula will already make the code break (because this formula taken alone is invalid).


I posted an explanation and an example of this formula array issue here in post 2.

http://www.mrexcel.com/forum/excel-...array-formulas-visual-basic-applications.html


Please check it and see if it helps.

 

[Malick]

Hi PGC,

Thank you for sharing your thread, that was really helpful and helped sort out the issue. Thanks again.

 

[pgc01]

You're welcome. Thanks for the feedback.