Long Array Formula in VBA

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:-

Code:
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.

By: Dahlia

Leave a Reply

Your email address will not be published. Required fields are marked *