Converting LongFormulaArray into macro

[dellzy]

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

 

[dellzy]

Hi ZVI,
No worries. Since you have given me much more simpler codes that give the same results, I prefer to use your simpler code and overwrite mine. Thank you so much for helping me out, Sir!! Really appreciate it!!

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

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