Converting LongFormulaArray into macro

D

dellzy

Board Regular
Joined
Apr 24, 2013
Messages
146
Hye,
Been trying to apply solution posted on the net for my long array formula. But to no avail. Just don't know which part is wrong..Please help to correct.

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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
=IF(RC10="""","""",24*((NETWORKDAYS(RC12,RC11,holidays)-1)

These highlighted cells are wrong...?
R1C10 or RC[10]
 
Upvote 0
Hye Takae,

It's not wrong actually. All of my macros work altho it was converted that way during macro recording. It was done by Excel itself, not me as I'm not an expert in vba.
Most of my macros were the recorded formulas in the worksheet. I often create the formulas in A1 style notation first and tested it until successful then I recorded it. However, I will stuck when it comes to long formulas because macro has limitation in no. of chars.

Let me publish here the formula in A1 style notation for easier reference:-

=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)))

 
Upvote 0
Try this
Rich (BB code): 
Sub Test()
 
  Const FM1 = "=IF($J2="""","""",24*((NETWORKDAYS($L2,$K2,holidays)-1)*('WH&PH'!$B$2-'WH&PH'!$B$1)+{0}-MEDIAN(NETWORKDAYS($L2,$L2,holidays)*MOD($L2,1),'WH&PH'!$B$2,'WH&PH'!$B$1)))"
  Const FM2 = "IF(NETWORKDAYS($K2,$K2,holidays),MEDIAN(MOD($K2,1),'WH&PH'!$B$2,'WH&PH'!$B$1),'WH&PH'!$B$2)"
 
  With Range("H2")
    .FormulaArray = FM1
    .Replace "{0}", FM2, LookAt:=xlPart
  End With
 
End Sub
 
Last edited:
Upvote 0
This works awesomely!
ZVI said:
Try this
Rich (BB code): 
Sub Test()
 
  Const FM1 = "=IF($J2="""","""",24*((NETWORKDAYS($L2,$K2,holidays)-1)*('WH&PH'!$B$2-'WH&PH'!$B$1)+{0}-MEDIAN(NETWORKDAYS($L2,$L2,holidays)*MOD($L2,1),'WH&PH'!$B$2,'WH&PH'!$B$1)))"
  Const FM2 = "IF(NETWORKDAYS($K2,$K2,holidays),MEDIAN(MOD($K2,1),'WH&PH'!$B$2,'WH&PH'!$B$1),'WH&PH'!$B$2)"
 
  With Range("H2")
    .FormulaArray = FM1
    .Replace "{0}", FM2, LookAt:=xlPart
  End With
 
End Sub
Click to expand...
 
Upvote 0
Hye ZVI,

I tried to apply the same pattern to my other long formula array but doesn't seem to work. Can you verify, please?

FYR, here is the formula in A1 style:-
Code: 
=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"))



Then below I tried to do the partial formulas following your pattern:-
Code: 
 Sub Test2()
  'Const FM1 = "=IF(RC10="""","""",IFERROR(IF(ISNUMBER(SEARCH(""CC_TL"",RC6))=TRUE,""Rerouted to TL"",IF(RC6=""Closed"",""Closed"",IF(R[-1]C6=""Assigned - Reroute"",""Rerouted to Creator"",{0}"
  'Const FM2 = "IF(AND(RC6<>28882,RC6<>""PREPAID_SUPPORT"",ISNUMBER(FIND(""_"",RC6))=TRUE),""Escalated"",IF(AND(AND(RC21<>RC20,MATCH(RC6,UserID,0)),MATCH(RC20,UserID,0),MATCH(RC21,UserID,0)),{1}"
  'Const FM3 = """Reassigned to Member"",IF(AND(AND(RC21=RC20,MATCH(RC6,UserID,0)),MATCH(RC20,UserID,0),MATCH(RC21,UserID,0)),""Self-Reassigned"",""Assigned"")))))),""Resolved""))"

 With Range("V2")
    .FormulaArray = FM1
    .Replace "{0}", FM2, LookAt:=xlPart
    .Replace "{1}", FM3, LookAt:=xlPart
  End With

End Sub

Thank you in advance.
DZ
 
Upvote 0
Sorry.. Please ignore the quote sign ' in front of Const. I actually run it without the quote. I forgot to delete that when I wanted to post here.
 
Upvote 0
That formula can be a bit simplified to this one:
Rich (BB code): 
=IF($J2="","",IFERROR(IF(ISNUMBER(SEARCH("CC_TL",$F2)),"Rerouted to TL",IF($F2="Closed","Closed",IF($F1="Assigned - Reroute","Rerouted to Creator",IF(AND($F2<>28882,$F2<> "PREPAID_SUPPORT",ISNUMBER(FIND("_",$F2))),"Escalated",IF(AND(MATCH($F2,UserID,0),MATCH($T2,UserID,0),MATCH($U2,UserID,0)),IF($U2=$T2,"Self-Reassigned","Reassigned to Member")))))),"Resolved"))

Then the code can be like this:
Rich (BB code): 
Sub Test2()
 
' --> Optimized formula
'=IF($J2="",
'  "",
'  IFERROR(
'    IF(ISNUMBER(SEARCH("CC_TL",$F2)),
'      "Rerouted to TL",
'      IF($F2="Closed",
'        "Closed",
'        IF($F1="Assigned - Reroute",
'          "Rerouted to Creator",
'          IF(AND($F2<>28882,$F2<>"PREPAID_SUPPORT",ISNUMBER(FIND("_",$F2))),
'            "Escalated",
'            IF(AND(MATCH($F2,UserID,0),MATCH($T2,UserID,0),MATCH($U2,UserID,0)),
'              IF($U2=$T2,"Self-Reassigned","Reassigned to Member")
'            )
'          )
'        )
'      )
'    ),
'    "Resolved"
'  )
')
'  <-- End of the optimizes formula
 
  Const FM1 = "=IF($J2="""","""",IFERROR(IF(ISNUMBER(SEARCH(""CC_TL"",$F2)),""Rerouted to TL"",IF($F2=""Closed"",""Closed"",IF($F1=""Assigned - Reroute"",""Rerouted to Creator"",{0}))),""Resolved""))"

  Const FM2 = "IF(AND($F2<>28882,$F2<> ""PREPAID_SUPPORT"",ISNUMBER(FIND(""_"",$F2))),""Escalated"",IF(AND(MATCH($F2,UserID,0),MATCH($T2,UserID,0),MATCH($U2,UserID,0)),IF($U2=$T2,""Self-Reassigned"",""Reassigned to Member"")))"
 
 With Range("V4")
    .FormulaArray = FM1
    .Replace "{0}", FM2, LookAt:=xlPart
 End With
 
End Sub

But actually there is no need in array formula in this case, therefore code can be even more simple:
Rich (BB code): 
Sub Test3()
 
 Const FM = "=IF($J2="""","""",IFERROR(IF(ISNUMBER(SEARCH(""CC_TL"",$F2)),""Rerouted to TL"",IF($F2=""Closed"",""Closed"",IF($F1=""Assigned - Reroute"",""Rerouted to Creator"",IF(AND($F2<>28882,$F2<> ""PREPAID_SUPPORT"",ISNUMBER(FIND(""_"",$F2))),""Escalated"",IF(AND(MATCH($F2,UserID,0),MATCH($T2,UserID,0),MATCH($U2,UserID,0)),IF($U2=$T2,""Self-Reassigned"",""Reassigned to Member"")))))),""Resolved""))"
 
 Range("V4").Formula = FM
 
End Sub
'
 
Last edited:
Upvote 0
ZVI said:
That formula can be a bit simplified to this one: ...
Click to expand...
It seems not fully showed in the forum.
Click to [Reply With Quote] to see all formula or have a look on the comment in the code.

That formula looks like the below (you may copy it and paste into V4 for the testing):
Code: 
=IF($J2="",
  "",
  IFERROR(
    IF(ISNUMBER(SEARCH("CC_TL",$F2)),
      "Rerouted to TL",
      IF($F2="Closed",
        "Closed",
        IF($F1="Assigned - Reroute",
          "Rerouted to Creator",
          IF(AND($F2<>28882,$F2<> "PREPAID_SUPPORT",ISNUMBER(FIND("_",$F2))),
            "Escalated",
            IF(AND(MATCH($F2,UserID,0),MATCH($T2,UserID,0),MATCH($U2,UserID,0)),
              IF($U2=$T2,"Self-Reassigned","Reassigned to Member")
            )
          )
        )
      )
    ),
    "Resolved"
  )
)
 
Last edited:
Upvote 0
Hi ZVI,
Wow!! Your expertise is not only giving the solutions that works but also simplifying it. You are really awesome & superb, Sir! Thank you so much! It works awesomely!! ;)
ZVI said:
That formula can be a bit simplified to this one:
Rich (BB code): 
=IF($J2="","",IFERROR(IF(ISNUMBER(SEARCH("CC_TL",$F2)),"Rerouted to TL",IF($F2="Closed","Closed",IF($F1="Assigned - Reroute","Rerouted to Creator",IF(AND($F2<>28882,$F2<> "PREPAID_SUPPORT",ISNUMBER(FIND("_",$F2))),"Escalated",IF(AND(MATCH($F2,UserID,0),MATCH($T2,UserID,0),MATCH($U2,UserID,0)),IF($U2=$T2,"Self-Reassigned","Reassigned to Member")))))),"Resolved"))

Then the code can be like this:
Rich (BB code): 
Sub Test2()
 
' --> Optimized formula
'=IF($J2="",
'  "",
'  IFERROR(
'    IF(ISNUMBER(SEARCH("CC_TL",$F2)),
'      "Rerouted to TL",
'      IF($F2="Closed",
'        "Closed",
'        IF($F1="Assigned - Reroute",
'          "Rerouted to Creator",
'          IF(AND($F2<>28882,$F2<>"PREPAID_SUPPORT",ISNUMBER(FIND("_",$F2))),
'            "Escalated",
'            IF(AND(MATCH($F2,UserID,0),MATCH($T2,UserID,0),MATCH($U2,UserID,0)),
'              IF($U2=$T2,"Self-Reassigned","Reassigned to Member")
'            )
'          )
'        )
'      )
'    ),
'    "Resolved"
'  )
')
'  <-- End of the optimizes formula
 
  Const FM1 = "=IF($J2="""","""",IFERROR(IF(ISNUMBER(SEARCH(""CC_TL"",$F2)),""Rerouted to TL"",IF($F2=""Closed"",""Closed"",IF($F1=""Assigned - Reroute"",""Rerouted to Creator"",{0}))),""Resolved""))"

  Const FM2 = "IF(AND($F2<>28882,$F2<> ""PREPAID_SUPPORT"",ISNUMBER(FIND(""_"",$F2))),""Escalated"",IF(AND(MATCH($F2,UserID,0),MATCH($T2,UserID,0),MATCH($U2,UserID,0)),IF($U2=$T2,""Self-Reassigned"",""Reassigned to Member"")))"
 
 With Range("V4")
    .FormulaArray = FM1
    .Replace "{0}", FM2, LookAt:=xlPart
 End With
 
End Sub

But actually there is no need in array formula in this case, therefore code can be even more simple:
Rich (BB code): 
Sub Test3()
 
 Const FM = "=IF($J2="""","""",IFERROR(IF(ISNUMBER(SEARCH(""CC_TL"",$F2)),""Rerouted to TL"",IF($F2=""Closed"",""Closed"",IF($F1=""Assigned - Reroute"",""Rerouted to Creator"",IF(AND($F2<>28882,$F2<> ""PREPAID_SUPPORT"",ISNUMBER(FIND(""_"",$F2))),""Escalated"",IF(AND(MATCH($F2,UserID,0),MATCH($T2,UserID,0),MATCH($U2,UserID,0)),IF($U2=$T2,""Self-Reassigned"",""Reassigned to Member"")))))),""Resolved""))"
 
 Range("V4").Formula = FM
 
End Sub
'
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Formula Array Run-Time Error 1004
Replies
3
Views
870
DanteAmor
DanteAmor
B
  • Question
Unable to set the FormulaArray property of the Range class
Replies
2
Views
647
breynolds0431
B
G
  • Question
Excel Array Formula with VBA (nothing happens)
Replies
2
Views
315
Ghesselink
G
P
  • Question
VBA formula array over 255 characters
Replies
6
Views
4K
pablo_max3045
P
V
  • Question
VBA Array Formulas Running Very Slowly
Replies
2
Views
637
SpillerBD
S

Forum statistics

Threads
1,215,576
Messages
6,125,633
Members
449,242
Latest member
Mari_mariou

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