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

2,030 thoughts on “Long Array Formula in VBA

  1. genolyze

    Way cool! Some extremely valid points! I appreciate you writing this write-up plus the rest of the website is extremely good.

  2. udfauzi

    Excellent site you have got here.. It’s difficult to find excellent writing like yours these days. I truly appreciate people like you! Take care!!

  3. camping

    Aw, this was an incredibly nice post. Spending some time and actual effort to make a great article… but what can I say… I put things off a whole lot and never seem to get anything done.

  4. tokoonline88

    You have made some decent points there. I checked on the net to learn more about the issue and found most individuals will go along with your views on this site.

  5. daftar akun vivoslot

    Can I simply just say what a relief to find somebody that truly knows what they are discussing on the web. You definitely know how to bring an issue to light and make it important. A lot more people have to read this and understand this side of your story. It’s surprising you’re not more popular given that you definitely have the gift.

  6. download apk vivoslot

    Greetings, I do think your site may be having browser compatibility problems. Whenever I take a look at your website in Safari, it looks fine however, when opening in IE, it’s got some overlapping issues. I just wanted to provide you with a quick heads up! Apart from that, fantastic site!

  7. vivoslot.net

    I blog frequently and I really thank you for your information. Your article has truly peaked my interest. I will book mark your blog and keep checking for new details about once per week. I opted in for your RSS feed too.

  8. agen vivo slot

    I’m amazed, I have to admit. Rarely do I encounter a blog that’s equally educative and amusing, and let me tell you, you have hit the nail on the head. The issue is something that not enough folks are speaking intelligently about. I’m very happy I came across this in my hunt for something regarding this.

  9. vivo slot online

    A motivating discussion is definitely worth comment. I do believe that you need to write more on this issue, it may not be a taboo subject but usually people do not talk about these subjects. To the next! All the best!!

  10. agen vivo slot

    I’m extremely pleased to discover this web site. I want to to thank you for ones time for this fantastic read!! I definitely appreciated every little bit of it and I have you book marked to look at new information in your website.

  11. vivoslot login

    I’m amazed, I must say. Rarely do I encounter a blog that’s equally educative and interesting, and without a doubt, you have hit the nail on the head. The problem is an issue that too few folks are speaking intelligently about. I am very happy I found this during my hunt for something regarding this.

  12. TMJ treatment Houston

    Attractive component of content. I just stumbled upon your weblog
    and in accession capital to assert that I get in fact enjoyed account your weblog posts.
    Anyway I’ll be subscribing for your augment or even I success you get admission to consistently
    rapidly.

  13. dingdong togel online

    I absolutely love your website.. Pleasant colors & theme. Did you create this web site yourself? Please reply back as I’m attempting to create my own site and would like to know where you got this from or exactly what the theme is called. Cheers!

  14. dingdong online

    After looking over a few of the blog posts on your web page, I honestly appreciate your technique of blogging. I added it to my bookmark website list and will be checking back soon. Take a look at my website too and tell me what you think.

  15. dingdong12d

    After I originally commented I seem to have clicked on the -Notify me when new comments are added- checkbox and from now on every time a comment is added I recieve four emails with the exact same comment. There has to be a means you can remove me from that service? Thanks!

  16. qq

    Spot on with this write-up, I honestly believe this site needs a great deal more attention. I’ll probably be back again to read more, thanks for the information!

Comments are closed.