Breaking long array formula in macro/vba

dellzy

Board Regular
Joined
Apr 24, 2013
Messages
146
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.
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
FORMP1 = "=IF(OR(RC1=""CTT"",RC1=""IM""),IF(RC38=""S1"",XXX,YYY),ZZZ)" I think "XXX","YYY"),"ZZZ")
 

dellzy

Board Regular
Joined
Apr 24, 2013
Messages
146
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

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
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

Board Regular
Joined
Apr 24, 2013
Messages
146
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".

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

Watch MrExcel Video

Forum statistics

Threads
1,108,973
Messages
5,525,986
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top