VBA - Long array formula

cacos · Apr 9, 2014

Hi everyone,

I'm trying to enter a long array formula through VBA, by using the method posted here

I'm struggling though, can't find a way to make it work. If there's another way around it, I'm completely open.

Code is:

Code:

Sub Array_Formula()

Dim FormulaPart1 As String
Dim FormulaPart2 As String

FormulaPart1 = "=SUM(COUNTIFS(Master!$A1:$A100,IF(Control!$A$8=""(All)"",""*?"",Control!$A$8),Master!$B1:$B100,Control!$B$32:$B$43," & _
              "Master!$C1:$C100,IF(Control!$C$8=""(All)"",""*?"",Control!$C$8),YYYY))"
             
FormulaPart2 = "Master!$D1:$D100,IF(Control!$D$8=""(All)"",""*?"",Control!$D$8),Master!$E1:$E100,IF(Control!$E$8=""(All)"",""*?"",Control!$E$8)" & _
              ",Master!$F1:$F100,IF(Control!$F$8=""(All)"",""*?"",Control!$F$8),Master!I1:I100,Control!$A55"


With Sheet1.Range("C66")
       
        .FormulaArray = FormulaPart1
        .Replace "YYYY", FormulaPart2, lookat:=xlPart
       
    End With


End Sub

Thanks!!

SirJB7 · Apr 9, 2014

Hi, cacos!

Change FormulaPart1 definition to this:

Code:

FormulaPart1 = "=SUM(COUNTIFS(Master!$A1:$A100,IF(Control!$A$8=""(All)"",""*?"",Control!$A$8),Master!$B1:$B100,Control!$B$32:$B$43," & _
              "Master!$C1:$C100,IF(Control!$C$8=""(All)"",""*?"",Control!$C$8)))+YYYY"

and Replace sentence to this:

Code:

        .Replace "+YYYY", FormulaPart2, lookat:=xlPart

Both FormulaPart1 and final formula should be valid Excel formulas, which it wasn't in your case since YYYY should be paired with something else like "YYYY,ZZZZ"; otherwise you have to take it outside the COUNTIFS & SUM scope.

Just advise if any issue.

Regards!

PS: BTW I'm just curious... such a long array formula is to predict next month inflation in Argentine?

I'd understand it in Venezuela...

NARAYANK991 · Apr 10, 2014

Hi Lucas ,

Try this :

Code:

Sub Array_Formula()
    Dim FormulaPart1 As String
    Dim FormulaPart2 As String
          
    FormulaPart2 = ",Master!$D1:$D100,IF(Control!$D$8=""(All)"",""*?"",Control!$D$8),Master!$E1:$E100,IF(Control!$E$8=""(All)"",""*?"",Control!$E$8)" & _
              ",Master!$F1:$F100,IF(Control!$F$8=""(All)"",""*?"",Control!$F$8),Master!I1:I100,Control!$A55"

    FormulaPart1 = "=SUM(COUNTIFS(Master!$A1:$A100,IF(Control!$A$8=""(All)"",""*?"",Control!$A$8),Master!$B1:$B100,Control!$B$32:$B$43," & _
              "Master!$C1:$C100,IF(Control!$C$8=""(All)"",""*?"",Control!$C$8)+0))"
            
    With Sheet1.Range("C66")
        .FormulaArray = FormulaPart1
        .Replace "+0", FormulaPart2, xlPart
    End With
End Sub

Narayan

SirJB7 · Apr 10, 2014

Hi, cacos!
Do you now have two absolutely different approaches to try to achieve your goal, don't you?

Regards!

cacos · Apr 10, 2014

Ha! That's what's so wonderful about this forum.

Thanks again for sharing your wisdom, they both work and I understand now

SirJB7 · Apr 10, 2014

Hi, cacos!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!

@SirJB7
Hi, myself!
So long...
Still astonished with the variety of approaches...?

Then we're two of us.
Regards!

cacos · Jun 18, 2014

Hi there again, I stumbled with the array via VBA problem again... I can't get this formula to work. I'd appreciate if you could give it a look:

Code:

Sub Array_Formula()

Dim FormulaPart1 As String
Dim FormulaPart2 As String

FormulaPart1 = "=SUM(IF($X$5<>""(All)"",((MMULT(N(RangeZ=$G$5),TRANSPOSE(COLUMN(RangeZ)^0))>=1)),1)*(IF($A$3=""(All)"",1,(RangeA=$A$3))" & _
"*(IF($A$5=""(All)"",1,(Range0=$A$5))*(IF($B$5=""(All)"",1,(Range1=$B$5))+YYYY))))))))"
           
FormulaPart2 = "*(IF($D$5=""(All)"",1,(Range3=$D$5))*(IF($E$5=""(All)"",1,(Range4=$E$5))*(IF($F$5=""(All)"",1,(Range5=$F$5))*(XRange=A$10)"

With Sheet1.Range("C100")
     
        .FormulaArray = FormulaPart1
        .Replace "YYYY", FormulaPart2, lookat:=xlPart
     
    End With


End Sub

Thanks!!

SirJB7 · Jun 18, 2014

Hi, cacos!

The trick of cheating Excel to accept formulas longer than 256 chars has only one major constraint: the partial formulas should be valid. I.e., FormulaPart1 needs to be a valid array formula by itself, and also the result of replacing YYYY by FormulaPart2.

I noticed that in FormulaPart1 there're 21 opening parenthesis and 25 closing, so it's at least unbalanced and hence the statement of .FormulaArray will raise an error. Besides being parenthesis balanced it must be a valid formula as well.

Regards!

PS: BTW, you're entering as frequently as I'm doing now

cacos · Jun 18, 2014

Ha but at least you help people.

Ok so I've tried every parenthesis combination...

... And still no progress

NARAYANK991 · Jun 18, 2014

Hi Lucas ,

Try this :

Code:

Sub Array_Formula()
    Dim FormulaPart1 As String
    Dim FormulaPart2 As String

    FormulaPart1 = "=SUM(IF($X$5<>""(All)"",((MMULT(N(RangeZ=$G$5),TRANSPOSE(COLUMN(RangeZ)^0))>=1)),1)*(IF($A$3=""(All)"",1,(RangeA=$A$3))" & _
"*(IF($A$5=""(All)"",1,(Range0=$A$5))*(IF($B$5=""(All)"",1,(Range1=$B$5))+YYYY))))"
       
    FormulaPart2 = "*(IF($D$5=""(All)"",1,(Range3=$D$5))*(IF($E$5=""(All)"",1,(Range4=$E$5))*(IF($F$5=""(All)"",1,(Range5=$F$5))*(XRange=A$10))))"

    With Sheet1.Range("C100")
         .FormulaArray = FormulaPart1
         .Replace "+YYYY", FormulaPart2, xlPart
    End With
End Sub

Of course , since I have no idea of what you wish to do , I cannot say that the final formula will do what you want it to do.

Narayan

cacos · Jun 19, 2014

HA! It works! Can't tell you how much I appreciate it. It's a hot mess with long arrays.

Thanks again to both of you whizzes.

Dahlia · Aug 6, 2016

Hye,
I've been trying to apply the same solution above into mine, but to no avail. Just don't know which part is wrong.. Please help to correct.

Code:

Sub MyLongArray()
Dim theFormulaPart1 As String
Dim theFormulaPart2 As String
LRow = ActiveSheet.Range("A1", Range("A1").End(xlDown)).Rows.Count
    
theFormulaPart1 = _
        "=IF(RC10="""","""",24*((NETWORKDAYS(RC12,RC11,holidays)-1)*('WH&PH'!R2C2-'WH&PH'!R1C2)+" & _
        "IF(NETWORKDAYS(RC11,RC11,holidays),MEDIAN(MOD(RC11,1),TBC"
   
theFormulaPart2 = _
    "'WH&PH'!R2C2,'WH&PH'!R1C2),'WH&PH'!R2C2)-MEDIAN(NETWORKDAYS(RC12,RC12,holidays)*MOD(RC12,1),'WH&PH'!R2C2,'WH&PH'!R1C2)))"
       
     With ActiveSheet.Range("H2")
        .FormulaArray = theFormulaPart1
        .Replace "TBC", theFormulaPart2, lookat:=xlPart
    End With

End Sub

Thanks in advance.
DZ

NARAYANK991 · Aug 6, 2016

Dahlia said:
Hye,
I've been trying to apply the same solution above into mine, but to no avail. Just don't know which part is wrong.. Please help to correct.

Thanks in advance.
DZ

Hi ,

First , post the formula , in A1 style notation , as you want it entered in the correct worksheet cell.

Only when this is known can it be decided whether the formula you want entered is correct or wrong ; first , a valid Excel formula has to be accepted in a worksheet cell in A1 style notation ; thereafter , we can change over to R1C1 style notation and see how the formula is transformed , and only after that can we think of splitting the formula so that it can be entered using VBA code.

Narayan

Dahlia · Aug 6, 2016

Hello Narayan,

This is the formula in A1 style notation:-

Code:

=IF($J2="","",24*((NETWORKDAYS($L2,$K2,holidays)-1)*('WH&PH'!$B$2-'WH&PH'!$B$1)+IF(NETWORKDAYS($K2,$K2,holidays),MEDIAN(MOD($K2,1),'WH&PH'!$B$2,'WH&PH'!$B$1),'WH&PH'!$B$2)-MEDIAN(NETWORKDAYS($L2,$L2,holidays)*MOD($L2,1),'WH&PH'!$B$2,'WH&PH'!$B$1)))

Actually,it was from the A1 style notation first, and I tested it on the worksheet many times to make sure it's working successfully before I record it to be a macro. I often do this because I'm not an expert in vba. However, it become a problem to me when I have to do a long formula when the macro has limitation of characters.

I hope anyone can help me because I have a few more similar to this on the same worksheet. I thought from one successful pattern, I can use it to apply to my other long array formulas too.

Thanks in advance.
DZ

NARAYANK991 · Aug 7, 2016

Hi ,

Thanks for the formula ; I entered it in a worksheet cell in A1 style , and then changed to R1C1 style ; the formula has now become :

=IF(RC10="","",24*((NETWORKDAYS(RC12,RC11,holidays)-1)*('WH&PH'!R2C2-'WH&PH'!R1C2)+IF(NETWORKDAYS(RC11,RC11,holidays),MEDIAN(MOD(RC11,1),'WH&PH'!R2C2,'WH&PH'!R1C2),'WH&PH'!R2C2)-MEDIAN(NETWORKDAYS(RC12,RC12,holidays)*MOD(RC12,1),'WH&PH'!R2C2,'WH&PH'!R1C2)))

I find that the length of this formula is 257 characters ; I will post the code for splitting this up , but in the meantime , you can use a simple approach.

Change the name of the named range holidays to hols ; this will immediately bring down the length of the above formula to within Excel's limits.

You can now enter the same formula in both the styles , and confirm that not only does Excel accept it , but the results are also correct.

This will help us to verify whether the code for splitting is correct.

Narayan

NARAYANK991 · Aug 7, 2016

Hi ,

See if this is correct.

Code:

Sub MyLongArray()
    Dim FormulaPart1 As String, FormulaPart2 As String
'    Dim lastrowdata As Long

'    lastrowdata = ActiveSheet.Range("A1", Range("A1").End(xlDown)).Rows.Count
  
  
    FormulaPart1 = _
        "=IF(RC10="""","""",24*((NETWORKDAYS(RC12,RC11,holidays)-1)*('WH&PH'!R2C2-'WH&PH'!R1C2)+" & _
        "IF(NETWORKDAYS(RC11,RC11,holidays),MEDIAN(MOD(RC11,1),'WH&PH'!R2C2,'WH&PH'!R1C2),'WH&PH'!R2C2)-XXXXX))"

    FormulaPart2 = "MEDIAN(NETWORKDAYS(RC12,RC12,holidays)*MOD(RC12,1),'WH&PH'!R2C2,'WH&PH'!R1C2)))"
    
    Application.ReferenceStyle = xlR1C1
  
    With ActiveSheet.Range("H2")
        .FormulaArray = FormulaPart1
        .Replace "XXXXX))", FormulaPart2, lookat:=xlPart
    End With

    Application.ReferenceStyle = xlA1
End Sub

Narayan

Dahlia · Aug 7, 2016

Hi Narayan,

Am so thankful to you! It works just great. I have followed the same pattern for another 2 more of my long formula arrays and work great too!

However, I still stumbled on this one. Seems like I need to break into 3. I tried number of times to break down and run it but doesn't work neither. Hopefully, you can help me on this one too. I suspect it's on the close brackets in the end. I noticed that I somehow need to add more brackets when I break down into parts. But am not quite sure how many I should put. In which part I can know how many brackets to add?

Anyway, here is my 3rd formula array that is still fail (A1 style):-

=IF($J2="","",IFERROR(IF(ISNUMBER(SEARCH("CC_TL",$F2))=TRUE,"Rerouted to TL",IF($F2="Closed","Closed",IF($F1="Assigned - Reroute","Rerouted to Creator",IF(AND($F2<>28882,$F2<>"PREPAID_SUPPORT",ISNUMBER(FIND("_",$F2))=TRUE),"Escalated",IF(AND(AND($U2<>$T2,MATCH($F2,UserID,0)),MATCH($T2,UserID,0),MATCH($U2,UserID,0)),"Reassigned to Member",IF(AND(AND($U2=$T2,MATCH($F2,UserID,0)),MATCH($T2,UserID,0),MATCH($U2,UserID,0)),"Self-Reassigned","Assigned")))))),"Resolved"))

Would appreciate your expertise on this one last piece for my Report module to fully work on macros.

Thanks in advance.
DZ

NARAYANK991 · Aug 7, 2016

Hi ,

See if this works :

Code:

Sub MyLongArray()
    Dim FormulaPart1 As String, FormulaPart2 As String
 
    FormulaPart1 = _
        "=IF(RC10="""","""",IFERROR(IF(ISNUMBER(SEARCH(""CC_TL"",RC6)),""Rerouted to TL"",IF(RC6=""Closed"",""Closed"",IF(R[-1]C6=""Assigned - Reroute"",""Rerouted to Creator"",IF(AND(RC6<>28882,RC6<>""PREPAID_SUPPORT"",ISNUMBER(FIND(""_"",RC6))),""Escalated"",xxxxx)))),yyyy))"

    FormulaPart2 = "IF(AND(AND(RC21<>RC20,MATCH(RC6,UserID,0)),MATCH(RC20,UserID,0),MATCH(RC21,UserID,0)),""Reassigned to Member"",IF(AND(AND(RC21=RC20,MATCH(RC6,UserID,0)),MATCH(RC20,UserID,0),MATCH(RC21,UserID,0)),""Self-Reassigned"",""Assigned"")))))),""Resolved""))"
   
    Application.ReferenceStyle = xlR1C1
 
    With ActiveSheet.Range("H2")
        .FormulaArray = FormulaPart1
        .Replace "xxxxx)))),yyyy))", FormulaPart2, lookat:=xlPart
    End With

    Application.ReferenceStyle = xlA1
End Sub

Narayan

Dahlia · Aug 7, 2016

Superb, Narayan! It's awesomely works!!

You're my saviour! Thank you sooo much!!

Dahlia · Aug 26, 2016

Hi,
I have another long formula array.

A1 notation style:
=IF($C2="New Record","",IFERROR(IF($H2<>"",$G2,IFERROR(IF($M2="","",IF(MAX(IF($A3:INDEX($A3:$A$17992,MATCH(9.99999999999999E+307,$M3:$M$17992))=$A2,$M3:INDEX($M3:$M$17992,MATCH(9.99999999999999E+307,$M3:$M$17992))))=0,INDEX($G3:$G$17992,MATCH(1,("New Record"=$C3:$C$17992)*($A2=$A3:$A$17992),0)),MAX(IF($A3:INDEX($A3:$A$17992,MATCH(9.99999999999999E+307,$M3:$M$17992))=$A2,$M3:INDEX($M3:$M$17992,MATCH(9.99999999999999E+307,$M3:$M$17992)))))),$G2)),$G2))

I broke them down to become 4 parts :-

Code:

    FPart1 = "=IF(RC3=""New Record"","""",IFERROR(IF(RC8<>"""",RC7,IFERROR(IF(RC13="""","""",IF(XXXXX,YYYYY,ZZZZZ)),RC7)),RC7))"
    FPart2 = "MAX(IF(R[1]C1:INDEX(R[1]C1:R5C1,MATCH(9.99999999999999E+307,R[1]C13:R5C13))=RC1,R[1]C13:INDEX(R[1]C13:R5C13,MATCH(9.99999999999999E+307,R[1]C13:R5C13))))=0"
    FPart3 = "INDEX(R[1]C7:R5C7,MATCH(1,(""New Record""=R[1]C3:R5C3)*(RC1=R[1]C1:R5C1),0))"
    FPart4 = "MAX(IF(R[1]C1:INDEX(R[1]C1:R5C1,MATCH(9.99999999999999E+307,R[1]C13:R5C13))=RC1,R[1]C13:INDEX(R[1]C13:R5C13,MATCH(9.99999999999999E+307,R[1]C13:R5C13))))))"
       
Application.ReferenceStyle = xlR1C1
   
With ActiveSheet.Range("L2")
.FormulaArray = FPart1
.Replace "XXXXX", FPart2, lookat:=xlPart
.Replace "YYYYY", FPart3, lookat:=xlPart
.Replace "ZZZZZ))))", FPart4, lookat:=xlPart
End With

Application.ReferenceStyle = xlA1
Selection.AutoFill Destination:=Range("L2:L5")
Range("L2:L5").Select
Range("L2").Select
End Sub

When I run the macro, I have no error but in the cell, it came out like this:-
=IF(RC3="New Record","",IFERROR(IF(RC8<>"",RC7,IFERROR(IF(RC13="","",IF(MAX(IF($A3:INDEX($A3:$A$5,MATCH(9.99999999999999E+307,$M3:$M$5))=$A2,$M3:INDEX($M3:$M$5,MATCH(9.99999999999999E+307,$M3:$M$5))))=0,INDEX($G3:$G$5,MATCH(1,("New Record"=$C3:$C$5)*($A2=$A3:$A$5),0)),ZZZZZ)),RC7)),RC7))

I have tried to change the brackets, but all to no avail. All my trials will returned error.

Please help to correct my code.

Thanks in advance.
DZ

Dahlia · Aug 27, 2016

I managed to solve this. Thanks.

YasserKhalil · Aug 27, 2016

Why the array formula can't be put directly to the cell ?? Is this because it is long ..
What are the characters limit to the cell for formula?
And is there another way to solve the problem without dividing the formula into two parts?

NARAYANK991 · Aug 27, 2016

YasserKhalil said:
Why the array formula can't be put directly to the cell ?? Is this because it is long ..
What are the characters limit to the cell for formula?
And is there another way to solve the problem without dividing the formula into two parts?

Hi ,

See this link for more details :

http://dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

Narayan

YasserKhalil · Aug 27, 2016

Thank you very much Mr. Narayan for the link ..
In fact I have developed existing code that enables me to have the formula copied so as to be able to use it in VBE

I make use of your code in Post # 18 to develop it and make use of UDF for Mr. Karedog to split the array formula into two parts ..
Here's the link :
http://www.excelforum.com/excel-programming-vba-macros/1153419-split-string-to-two-equal-parts.html

I have spent some time to develop the code that would make life easier

Here's the steps that illustrate my approach
-----------------------------------------------

To Be Continued ...

YasserKhalil · Aug 27, 2016

I have attached a workbook and tutorial folder to illustrate the process

VBA - Long array formula

Member

Excel Rōnin

Excel Ninja

Excel Rōnin

Member

Excel Rōnin

Member

Excel Rōnin

Member

Excel Ninja

Member

Member

Excel Ninja

Member

Excel Ninja

Excel Ninja

Member

Excel Ninja

Member

Member

Member

Well-Known Member

Excel Ninja

Well-Known Member

Well-Known Member

Attachments