Excel 数组替换公式 - VBA

Question

我一生都无法弄清楚为什么 XXX 和 YYY 没有被替换。当我运行宏时，我没有收到任何错误，但公式仍然像在 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'

我很感激这里的帮助!

Answer 1

Range.Find 和 Range.Replace 都“记住”用户在工作表上使用的设置。始终至少指定实现目标的最少参数；例如MATCHCASE 似乎并不重要，但 LOOKAT 很重要，因为它必须是 xlPart。

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

还有其他方法可以使您的 .FormulaArray 低于 255 个字符的限制。

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"

更改工作表名称并使用 xlA1 引用将公式从原来的 343 个字符减少到 142 个字符。