Merging Long FormulaArray in VBA

D

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

 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
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-...array-formulas-visual-basic-applications.html
 
Upvote 0
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. :(
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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. :(
 
Upvote 0
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>
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Amend the VBA code to Filter Columns Instead of Rows
Replies
9
Views
134
Akuini
Akuini
K
  • Solved
How to copy data from single cells to merge cells and vice versa
2 3
Replies
24
Views
408
Alex Blakenburg
A
C
  • Question
Slow vba
Replies
1
Views
313
Logit
Logit
Ottsel
  • Solved
VBA: Custom Count and Search Function Help Needed
Replies
5
Views
315
Ottsel
Ottsel
darkwingduck1
  • Question
Modification needed on VBA code to Reverse Abbreviate Numbers.
Replies
4
Views
421
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,407
Messages
6,119,332
Members
448,888
Latest member
Arle8907

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top