CODEWITHSUNDEEP
excelvbaarray-formulas
1Charlie
1Charlie

Reputation: 11

Range.replace method not replacing anything

I've tried and tested this method with more simple formulas and it worked perfectly, however when I try it with my new longer formulas, it enters FormulaPart1 into range("J2"), but doesn't replace the text with FormulaPart2 and FormulaPart3. The code also doesn't give any errors.

Basically, the formula calculates availability % which is 

((Available Hours - Engineering Downtime)/ Available Hours) * 100

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

Upvotes: 0

Views: 210

Answers (3)

1Charlie
1Charlie

Reputation: 11

It seems that the issue was indeed in the syntax of the formula when splitting it up to be replaced.

The code below contains the correct split for the formula.

Sub ArrayFormCalc()

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

Application.ReferenceStyle = xlR1C1

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*100,"""")"

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

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



End Sub

Upvotes: 1

Rory
Rory

Reputation: 34075

Unless your application is set to use R1C1 references, you're trying to put R1C1 references into an existing A1 style formula which won't work. Either change the application to R1C1 style, add the formulas and then put it back, or change the two replacement parts to use A1 style references.

Upvotes: 1

Rob
Rob

Reputation: 101

I can't identify the exact issue, but a possibility is that you are writing an invalid formula into the array before attempting to replace things. I suggest using the replace formula upfront on the string and only paste the final result into the FormulaArray. https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/replace-function

Upvotes: 1

Related Questions