Problems entering a FormulaArray in VBA

user_12959111 0
Viewed 57

I am trying to enter the following formula using VBA

"=SUM(IF(FREQUENCY(IF
('Array Summary'!$A$3:$A$" & LastRow & "=$A$1,IF
('Array Summary'!$B$3:$B$" & LastRow & "=$H$13,IF
('Array Summary'!$E$3:$E$" & LastRow & "<>"""",IF
('Array Summary'!$K$3:$K$" & LastRow & "=$H$12,IF
('Array Summary'!$L$3:$L$" & LastRow & "=I12,IF
('Array Summary'!$O$3:$O$" & LastRow & "<>""Commvault"",MATCH
('Array Summary'!$E$3:$E$" & LastRow & ",'Array Summary'!$E$3:$E$" & LastRow & ",0))))))),ROW
('Array Summary'!$E$3:$E$" & LastRow & ")-ROW('Array Summary'!E3)+1),1))"

I have tried drastically reducing the formula to something like below, to then use replace

"=SUM(IF(FREQUENCY(IF(A1:A2=$A$1,
IF(B1:B2=$H$13,
IF(E1:E2<>"""",
IF(K1:K2=$H$12,
IF(L1:L2=I12,
IF(O1:O2<>""Commvault"",
MATCH(E1:E2,E1:E2,0))))))),
ROW(E1:E2)-ROW(E6)+1),1))"

But I can't get that formula added in via VBA, if I remove one of the IF statements VBA adds it in fine, however I then can't get replace to work

Any help anyone can give me is greatly appreciated

excel-formula vba excel
1 Answers

Try the following...

Dim formulaPart1 As String
Dim formulaPart2 As String
Dim formulaPart3 As String
Dim formulaPart4 As String
Dim formulaPart5 As String
Dim formulaPart6 As String
Dim formulaPart7 As String
Dim formulaPart8 As String
Dim formulaPart9 As String

formulaPart1 = "'Array Summary'!$A$3:$A$" & LastRow & "=$A$1"
formulaPart2 = "'Array Summary'!$B$3:$B$" & LastRow & "=$H$13"
formulaPart3 = "'Array Summary'!$E$3:$E$" & LastRow & "<>"""""
formulaPart4 = "'Array Summary'!$K$3:$K$" & LastRow & "=$H$12"
formulaPart5 = "'Array Summary'!$L$3:$L$" & LastRow & "=I12"
formulaPart6 = "'Array Summary'!$O$3:$O$" & LastRow & "<>""Commvault"""
formulaPart7 = "'Array Summary'!$E$3:$E$" & LastRow
formulaPart8 = "'Array Summary'!$E$3:$E$" & LastRow
formulaPart9 = "ROW('Array Summary'!$E$3:$E$" & LastRow & ")-ROW('Array Summary'!E3)+1"

With Range("B2") 'change the cell reference accordingly
    .FormulaArray = "=SUM(IF(FREQUENCY(IF(A_A_A,IF(B_B_B,IF(C_C_C,IF(D_D_D,IF(E_E_E,IF(F_F_F,MATCH(G_G_G,H_H_H,0))))))),I_I_I),1))"
    .Replace "A_A_A", formulaPart1
    .Replace "B_B_B", formulaPart2
    .Replace "C_C_C", formulaPart3
    .Replace "D_D_D", formulaPart4
    .Replace "E_E_E", formulaPart5
    .Replace "F_F_F", formulaPart6
    .Replace "G_G_G", formulaPart7
    .Replace "H_H_H", formulaPart8
    .Replace "I_I_I", formulaPart9
End With
user_65857610
Related