Excel Array Replace Formula - VBA

Question

I can't for the life of me figure out why the XXX and YYY are not being replaced. When I run the macro I don't get any errors, but the formula still reads as it does in FormulaPart1.

Sub Test()

Dim FormulaPart1 As String
Dim FormulaPart2 As String
Dim FormulaPart3 As String

FormulaPart1 = "=SUM(IF(ISERROR(XXX),0,(YYY)))"
FormulaPart2 = "('Forecast - Budget Report'!R[1]C[-4]:R[989]C[7]*('Forecast - Budget Report'!R[1]C[12]:R[989]C[12]=""Rental Income"")*('Forecast - Budget Report'!R[-1]C[-4]:R[-1]C[7]<=R[-3]C[-4]))"
FormulaPart3 = "('Forecast - Budget Report'!R[1]C[-4]:R[989]C[7]*('Forecast - Budget Report'!R[1]C[12]:R[989]C[12]=""Rental Income"")*('Forecast - Budget Report'!R[-1]C[-4]:R[-1]C[7]<=R[-3]C[-4])"

With ThisWorkbook.Sheets("Budget Comparison").Range("F11")
    .FormulaArray = FormulaPart1
    .Replace "XXX", FormulaPart2
    .Replace "YYY", FormulaPart3
End With

End Sub'

I appreciate the assistance here!

You may be tempting #NAME? errors with XXX and YYY but try .Replace what:="XXX", replacement:=FormulaPart2, lookat:=xlpart — user4039065
– user4039065, Commented Sep 23, 2018 at 23:59

score 3 · Accepted Answer · 2018-09-24 00:27:32Z

Both Range.Find and Range.Replace 'remember' settings that were used on the worksheet by the user. Always specify at least the minimum arguments to accomplish your goal; e.g. MATCHCASE doesn't seem important but LOOKAT does since it must be xlPart.

.Replace what:="XXX", replacement:=FormulaPart2, lookat:=xlpart
.Replace what:="YYY", replacement:=FormulaPart3, lookat:=xlpart

There are other methods to bring your .FormulaArray down below the 255 character limit.

worksheets("Forecast - Budget Report").name = "f"

ThisWorkbook.Sheets("Budget Comparison").Range("F11").FormulaArray = _
  "=SUM(IF(ISERROR((f!B12:M1000*(f!R12:R1000="Rental Income")*(f!B10:M10<=B8))),0,((f!B12:M1000*(f!R12:R1000="Rental Income")*(f!B10:M10<=B8)))))"

worksheets("f").name = "Forecast - Budget Report"

Changing the worksheet name and using xlA1 referencing brings the formula down to 142 characters from an original 343 characters.

Collectives™ on Stack Overflow

Excel Array Replace Formula - VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related