VBA - Long array formula

[sobuj53]

I have attached a workbook and tutorial folder to illustrate the process

Hi,
Thanks for your hard work. Although in my case it seems I just couldn't get around the barrier. I've tried your code but ended up with the same error (Run time error 1004). My formula is, {=IFERROR(IF(COUNTIFS($D$6:D6,D6,$C$6:C6,C6)>1,INDEX($I$6:I6,SMALL(IF($D$6:D6=D6,IF($C$6:C6=C6,ROW($C$6:C6)-ROW(INDEX($C$6:C6,1,1))+1)),COUNTIFS($D$6:D6,D6,$C$6:C6,C6)-1)),0),0)}

and after parting it becomes,
FormulaPart1 = "=IFERROR(IF(COUNTIFS(R6C4:R[5]C[-4],R[5]C[-4],R6C3:R[5]C[-5],R[5]C[-5])>1,INDEX(R6C9:R[5]C[1],SMALL(IF(R6C4:R[5]C[-4]=R[5]C[-4],IF(R6C3:R[5]C[-5X)))),Y))"

FormulaPart2 = "]=R[5]C[-5],ROW(R6C3:R[5]C[-5])-ROW(INDEX(R6C3:R[5]C[-5],1,1))+1)),COUNTIFS(R6C4:R[5]C[-4],R[5]C[-4],R6C3:R[5]C[-5],R[5]C[-5])-1)),0),0)"

I'm not sure what's wrong here . I'd really appreciate if any one can help me out on this.

Thanks in advance.

 

[NARAYANK991]

Hi ,

Please post the cell reference where this formula will be entered.

Narayan

 

[Charlie2day]

Hi Everyone,

I've tried the previous code posted here and it works perfectly, however when I try it with my formulas, it enters FormulaPart1, but doesn't replace anything.

I've struggled for so many days now and I just can't get it right. I'd really appreciate any help.

Please see my code below.

Sub ArrayFormCalc()

Dim FormulaPart1 As String
Dim FormulaPart2 As String
Dim FormulaPart3 As String
Dim S1 As Worksheet

Set S1 = Sheets("Sheet1")

FormulaPart1 = "=IFERROR(((INDEX(INDIRECT(RC5&""_""&TEXT(R5C,""mmm"")),MATCH(RC6,INDIRECT(RC5&""_""&TEXT(R5C,""mmm"")&""_Shift""),0)" & _
              ",MATCH(R5C,INDIRECT(RC5&""_""&TEXT(R5C,""mmm"")&""_Date""),0))))-xxxxx,"""")"

FormulaPart2 = "SUMIFS(DT_Cur_Day_Hrs,DT_Equip,RC7,DT_Site,RC5,DT_Strt_Date,R5C,DT_Shift,RC6,DT_Cat,""Engineering Downtime"")" & _
              "+SUMIFS(DT_Nxt_Day_Hrs,DT_Equip,RC7,DT_Site,RC5,DT_End_Date,R5C,DT_Shift,RC6,DT_Cat,""Engineering Downtime""))/yyyy"

FormulaPart3 = "(INDEX(INDIRECT(RC5&""_""&TEXT(R5C,""mmm"")),MATCH(RC6,INDIRECT(RC5&""_""&TEXT(R5C,""mmm"")&""_Shift""),0)," & _
              "MATCH(R5C,INDIRECT(RC5&""_""&TEXT(R5C,""mmm"")&""_Date""),0))*100)"

Application.ReferenceStyle = xlR1C1

With S1.Range("J2")
    .FormulaArray = FormulaPart1
    .Replace "xxxxx", FormulaPart2, xlPart
    .Replace "yyyy", FormulaPart3, xlPart
End With

Application.ReferenceStyle = xlA1

End Sub