无法克服FormulaArray中的255个字符

Question

我花了很长时间想办法解决这个问题！如果有人再看一眼，看看我的错误在哪里，我会很感激的！这个公式似乎不能代替。

这是我的密码

Sub remove()
Dim theFormulaPart1 As String
Dim theFormulaPart2 As String

theFormulaPart1 = "=IFERROR(INDEX(Curve!D:D,MATCH(1,(DATE(RIGHT(Census!$BY2,4),LEFT(Census!$BY2,2),MID(Census!$BY2,4,2))-DATE(RIGHT(Census!$BM2,4),LEFT(Census!$BM2,2),MID(Census!$BM2,4,2))=Curve!$A:$A)*(Census!$T2=Curve!$C:$C),0)),""X()"")"
theFormulaPart2 = "IFERROR(INDEX(Curve!D:D,MATCH(1,(DATE(Year(Census!$BY2),Month(Census!$BY2),Day(Census!$BY2))-DATE(Year(Census!$BM2),Month(Census!$BM2),Day(Census!$BM2))=Curve!$A:$A)*(Census!$T2=Curve!$C:$C),0)),"""")"

With ActiveSheet.Range("CD2")
.FormulaArray = theFormulaPart1
.Replace """X()"")", theFormulaPart2
End With

Answer 1

错误很简单：

.Replace """X()"")", theFormulaPart2

不会工作，因为公式后将无效。你需要：

.Replace """X()""", theFormulaPart2

;)

Answer 2

好的，谢谢@Dirk它的结果是255个字符而不是问题

第二次浏览：

• 公式太长了，把它吐出来，以便将来能读懂。
• 使用直接处理字符串的Replace函数
• 如果FormulaArray 只有一个单元格，则不需要使用
• 替换的字符串应该是"X()"而不是"X()")

下面是第二次浏览代码：

Sub remove()
    Dim theFormulaPart1 As String
    Dim theFormulaPart2 As String

    theFormulaPart1 = "=IFERROR(INDEX(Curve!D:D,MATCH(1,(DATE(RIGHT(Census!$BY2,4),LEFT(Census!$BY2,2),MID(Census!$BY2,4,2))-DATE(RIGHT(Census!$BM2,4),LEFT(Census!$BM2,2),MID(Census!$BM2,4,2))=Curve!$A:$A)*(Census!$T2=Curve!$C:$C),0)),""X()"")"
    theFormulaPart2 = "IFERROR(INDEX(Curve!D:D,MATCH(1,(DATE(Year(Census!$BY2),Month(Census!$BY2),Day(Census!$BY2))-DATE(Year(Census!$BM2),Month(Census!$BM2),Day(Census!$BM2))=Curve!$A:$A)*(Census!$T2=Curve!$C:$C),0)),"""")"


    theFormulaPart1 = Replace(theFormulaPart1, """X()""", theFormulaPart2)

    ActiveSheet.Range("CD2") = theFormulaPart1

End Sub

另一个窍门是：

• 避免使用ActiveSheet, how?