Merging Long FormulaArray in VBA

dellzy

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

 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,732
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-questions/687125-long-array-formulas-visual-basic-applications.html
 

dellzy

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

dellzy

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

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,732
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
 

dellzy

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

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,732
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
<formula_if_1_1,""tier 0"",if(i5<formula_if_1_2,""tier="" 0"",if(i5<formula_if_1_3,""tier="" 0"",""tier3"")))"
</formula_if_1_1,""tier>
 

Forum statistics

Threads
1,077,778
Messages
5,336,249
Members
399,072
Latest member
abublitz

Some videos you may like

This Week's Hot Topics

Top