Long Array Formula in VBA

[Dahlia]

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.

 

[NARAYANK991]

Hi ,

When ever you need a formula to be implemented in VBA , always post the correct , working worksheet cell formula in A1 style notation.

Only by comparing what you want with what you get using code can we revise the code.

Revising the code so that a formula gets entered in a worksheet cell is no guarantee that that is the formula which you want entered in a worksheet to fulfill your requirement.

Narayan

 

[Dahlia]

alright then.

This is what I expected :-

=IF(OR($A2="CTT",$A2="IM"),IF($AI2="S1",INDEX(SLAbiztrx,1,2),IF($AI2="S2",INDEX(SLAbiztrx,2,2),IF($AI2="S3",INDEX(SLAbiztrx,3,2),INDEX(SLAbiztrx,4,2)))),IF($A2="IMS",IF($AI2="S1",INDEX(SLAsysapp,1,2),IF($AI2="S2",INDEX(SLAsysapp,2,2),IF($AI2="S3",INDEX(SLAsysapp,3,2),INDEX(SLAsysapp,4,2)))),IFERROR(IF($AI2="Critical",INDEX(SLAsvcreq,1,2),IF($AI2="High",INDEX(SLAsvcreq,2,2),INDEX(SLAsvcreq,3,2))),"-")))

This is what I got so far:-

=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 hope the above is sufficient reference.

 

[NARAYANK991]

Hi ,

Thank you for posting the worksheet cell formula.

Please mention the cell in which this same formula will be entered.

Narayan

 

[Dahlia]

Ur welcome.

It should starts at cell BF2 and shud go down all the way until the last row with data.

 

[Dahlia]

Just incase you wonder on some of dynamic named range, those are from another worksheet on separate columns which I use OFFET to dynamically allow the range to expand when the data is added.

 

[NARAYANK991]

Hi ,

Please confirm that the following formula which you posted , should go in cell BF2 :

=IF(OR($A2="CTT",$A2="IM"),IF($AI2="S1",INDEX(SLAbiztrx,1,2),IF($AI2="S2",INDEX(SLAbiztrx,2,2),IF($AI2="S3",INDEX(SLAbiztrx,3,2),INDEX(SLAbiztrx,4,2)))),IF($A2="IMS",IF($AI2="S1",INDEX(SLAsysapp,1,2),IF($AI2="S2",INDEX(SLAsysapp,2,2),IF($AI2="S3",INDEX(SLAsysapp,3,2),INDEX(SLAsysapp,4,2)))),IFERROR(IF($AI2="Critical",INDEX(SLAsvcreq,1,2),IF($AI2="High",INDEX(SLAsvcreq,2,2),INDEX(SLAsvcreq,3,2))),"-")))

Narayan

 

[NARAYANK991]

Hi ,

Try this :

Sub SLAMatrixResol()
    Dim FORMP1 As String, FORMP2 As String

    FORMP1 = "=IF(OR(RC1=""CTT"",RC1=""IM""),IF(RC35=""S1"",INDEX(SLAbiztrx,1,2),IF(RC35=""S2"",INDEX(SLAbiztrx,2,2),IF(RC35=""S3"",INDEX(SLAbiztrx,3,2),INDEX(SLAbiztrx,4,2)))),IF(RC1=""IMS"",IF(RC35=""S1"",INDEX(SLAsysapp,1,2),IF(RC35=""S2"",INDEX(SLAsysapp,2,2),TRUE))))"
    FORMP2 = "IF(RC35=""S3"",INDEX(SLAsysapp,3,2),INDEX(SLAsysapp,4,2)))),IFERROR(IF(RC35=""Critical"",INDEX(SLAsvcreq,1,2),IF(RC35=""High"",INDEX(SLAsvcreq,2,2),INDEX(SLAsvcreq,3,2))),""-"")))"

    Application.ReferenceStyle = xlR1C1

    With ActiveSheet.Range("BF2")
         .FormulaArray = FORMP1
         .Replace "TRUE))))", FORMP2, lookat:=xlPart
    End With

    Application.ReferenceStyle = xlA1
End Sub

Narayan

 

[Dahlia]

Wowww!!! Never thought it'd be that easy.. it's like just cutting the cake into half from the middle. huhu... And as usual, you owes gave me the exact solution that am looking for, Mr. Narayan.. Tq sooooo much!! you're my saviour !!

 

[Dahlia]

Hi,
I have another one that needs help. I run this and came out error "Type mismatch" and the yellow highlights at the row ".Replace "AAA", FORMP5, lookat:=xlPart".

Sub SLAresult1()
Dim FORMP1, FORMP2, FORMP3, FORMP4, FORMP5 As String

FORMP1 = "=IFERROR(IF(ISNUMBER(SEARCH(""business"",RC[-4]))=TRUE,XXX,YYY),""n/a"")"
FORMP2 = "IF(ISNUMBER(SEARCH(""day"",RC[-4]))=TRUE,IF(RC[-13]<=LEFT(RC[-4],(FIND("" "",RC[-4],1)-1))*24,""Within SLA"",""Smelly Fish"")"
FORMP3 = "IF(ISNUMBER(SEARCH(""hour"",RC[-4]))=TRUE,IF(RC[-13]<=LEFT(RC[-4],(FIND("" "",RC[-4],1)-1)),""Within SLA"",""Smelly Fish""),ZZZ))))"
FORMP4 = "IF(ISNUMBER(SEARCH(""min"",RC[-4]))=TRUE,IF(RC[-13]<=LEFT(RC[-4],(FIND("" "",RC[-4],1)-1))/60,""Within SLA"",""Smelly Fish"")))),AAA"
FORMP5 = "IF(ISNUMBER(SEARCH(""day"",RC[-4]))=TRUE,IF(RC[-14]<=LEFT(RC[-4],(FIND("" "",RC[-4],1)-1))*24,""Within SLA"",""Smelly Fish"")," & _
"IF(ISNUMBER(SEARCH(""hour"",RC[-4]))=TRUE,IF(RC[-14]<=LEFT(RC[-4],(FIND("" "",RC[-4],1)-1)),""Within SLA"",""Smelly Fish"")," & _
"IF(ISNUMBER(SEARCH(""min"",RC[-4]))=TRUE,IF(RC[-14]<=LEFT(RC[-4],(FIND("" "",RC[-4],1)-1))/60,""Within SLA"",""Smelly Fish"")))))"
   
Application.ReferenceStyle = xlR1C1
With ActiveSheet.Range("AR2")
.FormulaArray = FORMP1
.Replace "XXX", FORMP2, lookat:=xlPart
.Replace "YYY", FORMP3, lookat:=xlPart
.Replace "ZZZ", FORMP4, lookat:=xlPart
.Replace "AAA", FORMP5, lookat:=xlPart
End With
Application.ReferenceStyle = xlA1
End Sub

A1 notation style should be like this in cell "AR2":-

=IFERROR(IF(ISNUMBER(SEARCH("business",AN2))=TRUE,IF(ISNUMBER(SEARCH("day",AN2))=TRUE,IF(AE2<=LEFT(AN2,(FIND(" ",AN2,1)-1))*24,"Within SLA","Smelly Fish"),IF(ISNUMBER(SEARCH("hour",AN2))=TRUE,IF(AE2<=LEFT(AN2,(FIND(" ",AN2,1)-1)),"Within SLA","Smelly Fish"),IF(ISNUMBER(SEARCH("min",AN2))=TRUE,IF(AE2<=LEFT(AN2,(FIND(" ",AN2,1)-1))/60,"Within SLA","Smelly Fish")))),IF(ISNUMBER(SEARCH("day",AN2))=TRUE,IF(AD2<=LEFT(AN2,(FIND(" ",AN2,1)-1))*24,"Within SLA","Smelly Fish"),IF(ISNUMBER(SEARCH("hour",AN2))=TRUE,IF(AD2<=LEFT(AN2,(FIND(" ",AN2,1)-1)),"Within SLA","Smelly Fish"),IF(ISNUMBER(SEARCH("min",AN2))=TRUE,IF(AD2<=LEFT(AN2,(FIND(" ",AN2,1)-1))/60,"Within SLA","Smelly Fish"))))),"n/a")