Breaking long array formula in macro/vba

[dellzy]

Hi Expert,

It's really a pain when we need to breakdown the long formulas in excel into vba. Been trying to do below but I really don't understand why the XXX partial formula is unable to be displayed when I run the macro.

Here is my breakdown formulas in vba:-

Sub SLAMatrixResol()Dim FORMP1, FORMP2, FORMP3, FORMP4 As String
    
FORMP1 = "=IF(OR(RC1=""CTT"",RC1=""IM""),IF(RC38=""S1"",XXX,YYY),ZZZ)"
FORMP2 = "INDEX(SLAbiztrx,1,5),IF(RC38=""S2"",INDEX(SLAbiztrx,2,5)"
FORMP3 = "IF(RC38=""S3"",INDEX(SLAbiztrx,3,5),INDEX(SLAbiztrx,4,5)))"
FORMP4 = "IF(RC1=""IMS"",IF(RC38=""S1"",INDEX(SLAsysapp,1,5),IF(RC38=""S2"",INDEX(SLAsysapp,2,5),IF(RC38=""S3"",INDEX(SLAsysapp,3,5),INDEX(SLAsysapp,4,5)))),IFERROR(IF(RC38=""Critical"",INDEX(SLAsvcreq,1,3),IF(RC38=" & _
        """High"",INDEX(SLAsvcreq,2,3),INDEX(SLAsvcreq,3,3))),""-""))"
Application.ReferenceStyle = xlR1C1
With ActiveSheet.Range("AU2")
.FormulaArray = FORMP1
.Replace "XXX", FORMP2, lookat:=xlPart
.Replace "YYY)", FORMP3, lookat:=xlPart
.Replace "ZZZ", FORMP4, lookat:=xlPart
End With
Application.ReferenceStyle = xlA1
End Sub

Result when I run the macro above:-
=IF(OR(RC1="CTT",RC1="IM"),IF(RC38="S1",XXX,IF($AL2="S3",INDEX(SLAbiztrx,3,5),INDEX(SLAbiztrx,4,5))),IF($A2="IMS",IF($AL2="S1",INDEX(SLAsysapp,1,5),IF($AL2="S2",INDEX(SLAsysapp,2,5),IF($AL2="S3",INDEX(SLAsysapp,3,5),INDEX(SLAsysapp,4,5)))),IFERROR(IF($AL2="Critical",INDEX(SLAsvcreq,1,3),IF($AL2="High",INDEX(SLAsvcreq,2,3),INDEX(SLAsvcreq,3,3))),"-")))


I just don't get it.. what went wrong?
When I run the macro, it didn't appear any error but the XXX partial is not shown.

Can someone be kind enough to correct the part that I did wrong, please? I really kinda give up after trying one whole day today.

 

[Dryver14]

FORMP1 = "=IF(OR(RC1=""CTT"",RC1=""IM""),IF(RC38=""S1"",XXX,YYY),ZZZ)" I think "XXX","YYY"),"ZZZ")

 

[dellzy]

FORMP1 = "=IF(OR(RC1=""CTT"",RC1=""IM""),IF(RC38=""S1"",XXX,YYY),ZZZ)" I think "XXX","YYY"),"ZZZ")

Hi,
Thanks for trying to help but sorry to disappoint you that putting the double quotes in between the partial formula is definitely not the solution though. I've been doing the same style for a lot of long array formulas and it works just fine without the double quotes.

That's why I don't understand why this time, it didn't work out all for me.. huhu.. The YYY and ZZZ were successful, but i just couldn't find the root cause of XXX.

 

[Dryver14]

No worries, I'm good at jumping in feet first, the only other thing I didn't quite get was FORMP2 = "INDEX(SLAbiztrx,1,5),IF(RC38=""S2"",INDEX(SLAbiztrx,2,5)" This part before the if statement looked odd

 

[dellzy]

No worries, I'm good at jumping in feet first, the only other thing I didn't quite get was FORMP2 = "INDEX(SLAbiztrx,1,5),IF(RC38=""S2"",INDEX(SLAbiztrx,2,5)" This part before the if statement looked odd

Yeah, at first I thought it's due to that.. but when I park the IF statement like below, the result came out even worse.. it prompt with an error "Unable to set the Formulaarray property of the range class".

Sub SLAMatrixResp()
Dim FORMP1, FORMP2, FORMP3, FORMP4 As String
    
FORMP1 = "=IF(OR(RC1=""CTT"",RC1=""IM""),XXX,YYY),ZZZ)"
FORMP2 = "IF(RC38=""S1"",INDEX(SLAbiztrx,1,2),IF(RC38=""S2"",INDEX(SLAbiztrx,2,2)"
FORMP3 = "IF(RC38=""S3"",INDEX(SLAbiztrx,3,2),INDEX(SLAbiztrx,4,2)))"
FORMP4 = "IF(RC1=""IMS"",IF(RC38=""S1"",INDEX(SLAsysapp,1,2),IF(RC38=""S2"",INDEX(SLAsysapp,2,2),IF(RC38=""S3"",INDEX(SLAsysapp,3,2),INDEX(SLAsysapp,4,2)))),IFERROR(IF(RC38=""Critical"",INDEX(SLAsvcreq,1,2),IF(RC38=" & _
        """High"",INDEX(SLAsvcreq,2,2),INDEX(SLAsvcreq,3,2))),""-""))"
Application.ReferenceStyle = xlR1C1
With ActiveSheet.Range("AT2")
.FormulaArray = FORMP1
.Replace "XXX", FORMP2, lookat:=xlPart
.Replace "YYY", FORMP3, lookat:=xlPart
.Replace "ZZZ", FORMP4, lookat:=xlPart
End With
Application.ReferenceStyle = xlA1
End Sub

Actually, if it took me one whole day before I posted here, trust me, I've tried all kinds of "arrangement" in the formula parts, but everything else will led me to "Unable to set the Formulaarray property of the range class",else it'd be successful but with XXX not showing.