Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Merging Long FormulaArray in VBA

  1. #1
    Board Regular
    Join Date
    Apr 2013
    Posts
    145
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Merging Long FormulaArray in VBA

    Hi there,

    I've been trying to use the method showed in Daily Dose of Excel Blog Archive Entering Long Array Formulas in VBA this whole day to overcome the error
    "Unable To Set The Formulaarray Property Of The Range Class - Error 1004"
    but still to no avail. I just don't know what else I've been missing here? I think if it's still about the chars limit, I have splitted the formulas to the smaller pieces already and I know this formula works fine in the sheet cell. Appreciate some help, please?

    Code:
    Sub TieringAll()
    Dim FormulaPart1 As String
    Dim FormulaPart2 As String
    Dim FormulaPart3 As String
    Dim FormulaPart4 As String
    Dim FormulaPart5 As String
    Dim FormulaPart6 As String
    Dim FormulaPart7 As String
    Dim FirstHalf As String
    Dim SecondHalf As String
        
    FormulaPart1 = "=IF(OR($D5="""",$I5="""",AND($E5=""TL"",$W5<>""-"")),"""","
    FormulaPart2 = "IF(OR(ISNUMBER(SEARCH(""TAT"",N$4))," & _
    "ISNUMBER(SEARCH(""Suspense"",N$4)),"
    FormulaPart3 = "OR(ISNUMBER(SEARCH(""Pender"",N$4))," & _
    "ISNUMBER(SEARCH(""Accuracy"",N$4)))),"
    FormulaPart4 = "IF(I5>(INDEX(PI_Chart_Details!$E$1:$E$189,"
    FormulaPart5 = "MATCH(1,(TRUE=ISNUMBER(SEARCH" & _
    "(PI_Chart_Details!$D$1:$D$189,N$4)))"
    FormulaPart6 = "*(TRUE=ISNUMBER(SEARCH" & _
    "(PI_Chart_Details!$B$1:$B$189,$B5)))"
    FormulaPart7 = "*(""Tier 1""=PI_Chart_Details!$F$1:$F$189),0)))," & _
    """Tier 0"",""TierUnknown"")))"
    FirstHalf = FormulaPart1 & FormulaPart2 & FormulaPart3
    SecondHalf = FormulaPart4 & FormulaPart5 & FormulaPart6 & FormulaPart7
    
    ActiveSheet.Range("M5:M8").FormulaArray = FirstHalf & SecondHalf
    End Sub


  2. #2
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,513
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Merging Long FormulaArray in VBA

    Hi

    If you have a long array formula (>255 characters) you have to split it.
    You must however, make sure that each part of the formula is consistent with the formula syntax rules.

    For ex., in the formula

    =IF(A1>B1,3,4)

    you cannot split it like "=IF(A1>B1," and "3,4)". None of these 2 is a valid formula per se.

    When you split a long formula you must make sure that make it in a way that ensures that you always write in the cell a valid formula.

    I posted about this question here:

    http://www.mrexcel.com/forum/excel-q...lications.html
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  3. #3
    Board Regular
    Join Date
    Apr 2013
    Posts
    145
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merging Long FormulaArray in VBA

    Hi pgc01,

    Thanks for the tips. Although I feel tough to understand on how to apply it onto my formulas, but let me try first and post it for your verification. Seems like it's a lot tougher to make the formula into vba rather than place it in the sheet cell.

  4. #4
    Board Regular
    Join Date
    Apr 2013
    Posts
    145
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merging Long FormulaArray in VBA

    Hi pgc01,

    I tried converting to the syntax as you advised. Coded as below:

    Code:
    Option Explicit
    
    Sub TieringAll_Test1()
    Dim FormulaPart1 As String
    Dim FormulaPart2 As String
    Dim FormulaPart3 As String
    Dim FormulaPart4 As String
    Dim FormulaPart5 As String
    Dim FormulaPart6 As String
    Dim FormulaPart7 As String
    Dim FormulaPart8 As String
    Dim FormulaPart9 As String
    Dim FormulaPart10 As String
    Dim FormulaPart11 As String
    Dim FormulaPart12 As String
        
    FormulaPart1 = "=IF(OR($D5="""",$I5="""",AND($E5=""TL"",$W5<>""-"")),"""","
    FormulaPart2 = "ISNUMBER(SEARCH(""TAT"",N$4))"
    FormulaPart3 = "ISNUMBER(SEARCH(""Suspense"",N$4))"
    FormulaPart4 = "ISNUMBER(SEARCH(""Pender"",N$4))"
    FormulaPart5 = "ISNUMBER(SEARCH(""Accuracy"",N$4))))"
    FormulaPart6 = "PI_Chart_Details!$E$1:$E$189"
    FormulaPart7 = "PI_Chart_Details!$D$1:$D$189"
    FormulaPart8 = "PI_Chart_Details!$B$1:$B$189"
    FormulaPart9 = "PI_Chart_Details!$F$1:$F$189"
    FormulaPart10 = "INDEX(Range1,MATCH(1,(TRUE=ISNUMBER(SEARCH(Range2))"
    FormulaPart11 = "TRUE=ISNUMBER(SEARCH(Range3))"
    FormulaPart12 = "TRUE=ISNUMBER(SEARCH(Range4))"
    
    
    With ActiveSheet.Range("M5")
    .FormulaArray = FormulaPart1 & "IF(OR(criteria1,criteria2,criteria3,criteria4),IF(I5>(FPart10)*(FPart11)*(FPart12),""Tier 0"",""TierUnknown"")))"
    .Replace "criteria1", FormulaPart2
    .Replace "criteria2", FormulaPart3
    .Replace "criteria3", FormulaPart4
    .Replace "criteria4", FormulaPart5
    .Replace "Range1", FormulaPart6
    .Replace "Range2", FormulaPart7
    .Replace "Range3", FormulaPart8
    .Replace "Range4", FormulaPart9
    .Replace "FPart10", FormulaPart10
    .Replace "FPart11", FormulaPart11
    .Replace "FPart12", FormulaPart12
    
    
    
    
    End With
    End Sub
    The good news is no more error, BUT the bad news is, the formulaarray passed onto the cell becomes as below:
    Code:
    {=IF(OR($D5="",$I5="",AND($E5="TL",$W5<>"-")),"",IF(OR(criteria1,criteria2,criteria3,criteria4),IF(I5>(FPart10)*(FPart11)*(FPart12),"Tier 0","TierUnknown")))}
    Would you be kind enough to correct this for me, please?

    Thank you in advance.

  5. #5
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,513
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Merging Long FormulaArray in VBA

    Hi

    I corrected some parts, there were parentheses wrong, missing parameters in the Search(), the replacements were out of order.
    You should use the LookAt parameter in the first replace to ensure that it does a partial replace. That value is stored for the following uses of .Replace(), so you don't have to repeat it.

    I tried to correct the Search()'s using the formula in your first post, but I couldn't do it in the third search because you changed the formula. Since I had no info I used A1, you should change it.

    I hope this is closer to what you need:

    Code:
    Sub TieringAll_Test1()
    Dim FormulaPart1 As String
    Dim FormulaPart2 As String
    Dim FormulaPart3 As String
    Dim FormulaPart4 As String
    Dim FormulaPart5 As String
    Dim FormulaPart6 As String
    Dim FormulaPart7 As String
    Dim FormulaPart8 As String
    Dim FormulaPart9 As String
    Dim FormulaPart10 As String
    Dim FormulaPart11 As String
    Dim FormulaPart12 As String
        
    FormulaPart1 = "=IF(OR($D5="""",$I5="""",AND($E5=""TL"",$W5<>""-"")),"""","
    FormulaPart2 = "ISNUMBER(SEARCH(""TAT"",N$4))"
    FormulaPart3 = "ISNUMBER(SEARCH(""Suspense"",N$4))"
    FormulaPart4 = "ISNUMBER(SEARCH(""Pender"",N$4))"
    FormulaPart5 = "ISNUMBER(SEARCH(""Accuracy"",N$4))"
    
    FormulaPart6 = "ISNUMBER(SEARCH(Range2,N$4))"
    FormulaPart7 = "ISNUMBER(SEARCH(Range3,B$5))"
    FormulaPart8 = "ISNUMBER(SEARCH(Range4,A1))"
    
    FormulaPart9 = "PI_Chart_Details!$E$1:$E$189"
    FormulaPart10 = "PI_Chart_Details!$D$1:$D$189"
    FormulaPart11 = "PI_Chart_Details!$B$1:$B$189"
    FormulaPart12 = "PI_Chart_Details!$F$1:$F$189"
    
    
    
    With ActiveSheet.Range("M5")
        .FormulaArray = FormulaPart1 & "IF(OR(criteria1,criteria2,criteria3,criteria4),IF(I5>INDEX(Range1,MATCH(1,(FPart10)*(FPart11)*(FPart12),0)),""Tier 0"",""TierUnknown"")))"
        .Replace "criteria1", FormulaPart2, LookAt:=xlPart
        .Replace "criteria2", FormulaPart3
        .Replace "criteria3", FormulaPart4
        .Replace "criteria4", FormulaPart5
        .Replace "FPart10", FormulaPart6
        .Replace "FPart11", FormulaPart7
        .Replace "FPart12", FormulaPart8
        .Replace "Range1", FormulaPart9
        .Replace "Range2", FormulaPart10
        .Replace "Range3", FormulaPart11
        .Replace "Range4", FormulaPart12
    
    End With
    End Sub
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  6. #6
    Board Regular
    Join Date
    Apr 2013
    Posts
    145
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merging Long FormulaArray in VBA

    Hi,

    I really thank you for guiding me in this. I managed to get it work BUT unfortunately I actually have additional formulas after "Tier 0" which I replaced with "TierUnknown" in earlier posts, just to test & understand how the syntax should be to get it work.
    I thought if I could understand it, then I could try applying it to the rests of the formulas but unfortunately the initial error will pop-up again & again. Would appreciate your corrections on my trials below.

    The full formula I need to enter:-
    Code:
    =IF(OR($D5="",$I5="",AND($E5="TL",$W5<>"-")),"",IF(OR(ISNUMBER(SEARCH("TAT",M$4)),ISNUMBER(SEARCH("Suspense",M$4)),OR(ISNUMBER(SEARCH("Pender",M$4)),ISNUMBER(SEARCH("Accuracy",M$4)))),IF(I5>(INDEX(PI_Chart_Details!$E$1:$E$189,MATCH(1,(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$D$1:$D$189,M$4)))*(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$B$1:$B$189,$B5)))*("Tier 1"=PI_Chart_Details!$F$1:$F$189),0))),"Tier 0",
    IF(I5>(INDEX(PI_Chart_Details!$E$1:$E$189,MATCH(1,(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$D$1:$D$189,M$4)))*(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$B$1:$B$189,$B5)))*("Tier 2"=PI_Chart_Details!$F$1:$F$189),0))),"Tier 1",
    IF(I5>(INDEX(PI_Chart_Details!$E$1:$E$189,MATCH(1,(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$D$1:$D$189,M$4)))*(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$B$1:$B$189,$B5)))*("Tier 3"=PI_Chart_Details!$F$1:$F$189),0))),"Tier 2","Tier 3"))),
    IF(I5<(INDEX(PI_Chart_Details!$E$1:$E$189,MATCH(1,(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$D$1:$D$189,M$4)))*(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$B$1:$B$189,$B5)))*("Tier 1"=PI_Chart_Details!$F$1:$F$189),0))),"Tier 0",
    IF(I5<(INDEX(PI_Chart_Details!$E$1:$E$189,MATCH(1,(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$D$1:$D$189,M$4)))*(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$B$1:$B$189,$B5)))*("Tier 2"=PI_Chart_Details!$F$1:$F$189),0))),"Tier 1",
    IF(I5<(INDEX(PI_Chart_Details!$E$1:$E$189,MATCH(1,(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$D$1:$D$189,M$4)))*(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$B$1:$B$189,$B5)))*("Tier 3"=PI_Chart_Details!$F$1:$F$189),0))),"Tier 2","Tier 3")))))
    Partially converted into vba format:-
    Code:
    Sub TieringAll_Test2()Dim FormulaPart1 As String
    Dim FormulaPart2 As String
    Dim FormulaPart3 As String
    Dim FormulaPart4 As String
    Dim FormulaPart5 As String
    Dim FormulaPart6 As String
    Dim FormulaPart7 As String
    Dim FormulaPart8 As String
    Dim FormulaPart8a As String
    Dim FormulaPart8b As String
    Dim FormulaPart9 As String
    Dim FormulaPart10 As String
    Dim FormulaPart11 As String
    Dim FormulaPart12 As String
    Dim MainFormula1 As String
    Dim MainFormula1a As String
    Dim MainFormula1b As String
    Dim LRowA As Long
    Dim LRowM As Long
    
    
    Application.ScreenUpdating = False
    LRowA = Range("A" & Rows.Count).End(xlUp).Row
    LRowM = Range("M" & Rows.Count).End(xlUp).Row + 1
        
    FormulaPart1 = "=IF(OR($D65="""",$I65="""",AND($E65=""TL"",$W65<>""-"")),"""","
    
    
    'FormulaPart1 = "=IF(OR(INDIRECT(""D""&ROW())="""",INDIRECT(""I""&ROW())="""",AND(INDIRECT(""E""&ROW())=""TL"",INDIRECT(""W""&ROW())<>""-"")),"""","
    'FormulaPart1a = "INDIRECT(""D""&ROW())="""""
    'FormulaPart1b = "INDIRECT(""I""&ROW())="""""
    'FormulaPart1c = "INDIRECT(""E""&ROW())=""TL"""
    'FormulaPart1d = "INDIRECT(""W""&ROW())<>""-"""
    
    
    FormulaPart2 = "ISNUMBER(SEARCH(""TAT"",N$4))"
    FormulaPart3 = "ISNUMBER(SEARCH(""Suspense"",N$4))"
    FormulaPart4 = "ISNUMBER(SEARCH(""Pender"",N$4))"
    FormulaPart5 = "ISNUMBER(SEARCH(""Accuracy"",N$4))"
    
    
    FormulaPart6 = "TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$D$1:$D$189,N$4))"
    FormulaPart7 = "TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$B$1:$B$189,$B65))"
    FormulaPart8 = """Tier 1""=PI_Chart_Details!$F$1:$F$189"
    FormulaPart8a = """Tier 2""=PI_Chart_Details!$F$1:$F$189"
    FormulaPart8b = """Tier 3""=PI_Chart_Details!$F$1:$F$189"
    
    
    FormulaPart9 = "PI_Chart_Details!$E$1:$E$189"
    FormulaPart10 = "PI_Chart_Details!$D$1:$D$189"
    FormulaPart11 = "PI_Chart_Details!$B$1:$B$189"
    FormulaPart12 = "PI_Chart_Details!$F$1:$F$189"
    
    
    MainFormula1 = "IF(I65>INDEX(Range1,MATCH(1,(FPart10)*(FPart11)*(FPart12),0)),""Tier0"""
    MainFormula1a = "IF(I65>INDEX(Range1,MATCH(1,(FPart10)*(FPart11)*(FPart12),0)),""Tier1"""
    MainFormula1b = "IF(I65>INDEX(Range1,MATCH(1,(FPart10)*(FPart11)*(FPart12),0)),""Tier2"""
    
    
    
    
    With ActiveSheet.Range("M" & LRowM)
        .FormulaArray = FormulaPart1 & "IF(OR(criteria1,criteria2,AND(criteria3,criteria4)),IF(I5>INDEX(Range1,MATCH(1,(FPart10)*(FPart11)*(FPart12),0)),""Tier 0"",""TierUnknown"")))"
        
        '.FormulaArray = "=IF(OR(FPart1a,FPart1b,AND(FPart1c,FPart1d)),"""",IF(OR(criteria1,criteria2,criteria3,criteria4),IF(I5>INDEX(Range1,MATCH(1,(FPart10)*(FPart11)*(FPart12),0)),""Tier 0"",""TierUnknown"")))"
        '.FormulaArray = FormulaPart1 & "IF(OR(criteria1,criteria2,AND(criteria3,criteria4)),IF(I5>MFormula1,0)),""Tier 0"",IF(I5>MFormula1a,0)),""Tier 1"",IF(I5>MFormula1b,0)),""Tier 2"",""TierUnknown"")))"
        '.FormulaArray = FormulaPart1 & "IF(OR(criteria1,criteria2,AND(criteria3,criteria4)),MFormula1,MFormula1a,MFormula1b,""TierUnknown"")))"
        '.FormulaArray = FormulaPart1 & "IF(OR(criteria1,criteria2,AND(criteria3,criteria4))," & MainFormula1 & "," & MainFormula1a & "," & MainFormula1b & ",""TierUnknown"")))"
        
        .Replace "FPart1a", FormulaPart1a, LookAt:=xlPart
        .Replace "FPart1b", FormulaPart1b
        .Replace "FPart1c", FormulaPart1c
        .Replace "FPart1d", FormulaPart1d
        .Replace "criteria1", FormulaPart2
        .Replace "criteria2", FormulaPart3
        .Replace "criteria3", FormulaPart4
        .Replace "criteria4", FormulaPart5
        .Replace "FPart10", FormulaPart6
        .Replace "FPart11", FormulaPart7
        .Replace "FPart12", FormulaPart8
        .Replace "Range1", FormulaPart9
        .Replace "Range2", FormulaPart10
        .Replace "Range3", FormulaPart11
        .Replace "Range4", FormulaPart12
        .Replace "FPart13", FormulaPart8a
        .Replace "FPart14", FormulaPart8b
        .Replace "MFormula1", MainFormula1
        .Replace "MFormula1a", MainFormula1a
        .Replace "MFormula1b", MainFormula1b
    
    
    End With
    Range("M" & LRowM & ":M" & LRowA).FillDown
    Range("M" & LRowM).Select
    Application.ScreenUpdating = True
    End Sub
    I still didn't know how I should understand & respect the syntax as all my trials based on my understanding but still failed. Appreciate your kind help. Or whoever has a solution out there, please help.

  7. #7
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,513
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Merging Long FormulaArray in VBA

    Try this and check the formula:

    Code:
    Sub Test()
    Dim sFormula As String
    Dim sFormula_IFs_1 As String, sFormula_IFs_2 As String
    Dim sFormula_IF_1_1 As String, sFormula_IF_1_2 As String, sFormula_IF_1_3 As String
    
    sFormula = "=IF(OR($D5="""",$I5="""",AND($E5=""TL"",$W5<>""-"")),"""",IF(OR(ISNUMBER(SEARCH(""TAT"",M$4)),ISNUMBER(SEARCH(""Suspense"",M$4)),OR(ISNUMBER(SEARCH(""Pender"",M$4)),ISNUMBER(SEARCH(""Accuracy"",M$4)))),Formula_IFs_1,Formula_IFs_2))"
    
    sFormula_IFs_1 = "IF(I5>Formula_IF_1_1,""Tier 0"",IF(I5>Formula_IF_1_2,""Tier 0"",IF(I5>Formula_IF_1_3,""Tier 0"",""Tier3"")))"
    sFormula_IFs_2 = "IF(I5<Formula_IF_1_1,""Tier 0"",IF(I5<Formula_IF_1_2,""Tier 0"",IF(I5<Formula_IF_1_3,""Tier 0"",""Tier3"")))"
    
    sFormula_IF_1_1 = "INDEX(PI_Chart_Details!$E$1:$E$189,MATCH(1,(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$D$1:$D$189,M$4)))*(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$B$1:$B$189,$B5)))*(""Tier 1""=PI_Chart_Details!$F$1:$F$189),0))"
    sFormula_IF_1_2 = "INDEX(PI_Chart_Details!$E$1:$E$189,MATCH(1,(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$D$1:$D$189,M$4)))*(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$B$1:$B$189,$B5)))*(""Tier 2""=PI_Chart_Details!$F$1:$F$189),0))"
    sFormula_IF_1_3 = "INDEX(PI_Chart_Details!$E$1:$E$189,MATCH(1,(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$D$1:$D$189,M$4)))*(TRUE=ISNUMBER(SEARCH(PI_Chart_Details!$B$1:$B$189,$B5)))*(""Tier 3""=PI_Chart_Details!$F$1:$F$189),0))"
    
    With Range("A1")
        .FormulaArray = sFormula
        .Replace "Formula_IFs_1", sFormula_IFs_1, LookAt:=xlPart, MatchCase:=False
        .Replace "Formula_IFs_2", sFormula_IFs_2
        .Replace "Formula_IF_1_1", sFormula_IF_1_1
        .Replace "Formula_IF_1_2", sFormula_IF_1_2
        .Replace "Formula_IF_1_3", sFormula_IF_1_3
    End With
    
    End Sub
    
    
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •