vba - 尝试使用 FormulaArray 时出现错误 1004。替换技巧不起作用

Question

背景:我得到了一个很酷的数组公式，它在 Excel 中运行得非常完美。现在我正在尝试使用 VBA 执行相同的公式。所以我在单元格中输入数组公式并用宏记录。该公式运行完美。宏记录器给我这个:

Selection.FormulaArray = _
    "=INDEX('[HOGARES ALBACETE.xlsx]21076'!C1,MATCH(MAX(IF(RIGHT('[HOGARES ALBACETE.xlsx]21076'!C1,LEN(R[-1]C)+2)=""["" &R[-1]C&""]"",'[HOGARES ALBACETE.xlsx]21076'!C2)),IF(RIGHT('[HOGARES ALBACETE.xlsx]21076'!C1,LEN(R[-1]C)+2)=""[""&R[-1]C&""]"",'[HOGARES ALBACETE.xlsx]21076'!C2),0),1)"

如果我尝试运行上面的代码，我会收到错误 1004。子程序只有这一行。没有别的了。

经过一些研究，我进入了这个:

VBA Run time error 1004: Unable to set the formulaarray property of the range class

Entering Long Array Formulas In VBA

所以我将公式分成两部分:

Dim theFormulaPart1 As String
Dim theFormulaPart2 As String
Dim MiReemplazo As String
MiReemplazo = "cacota"

theFormulaPart1 = "=INDEX('[HOGARES ALBACETE.xlsx]21076'!C1,MATCH(MAX(IF(RIGHT('[HOGARES ALBACETE.xlsx]21076'!C1,LEN(R[-1]C)+2)=""["" &R[-1]C&""]"",'[HOGARES ALBACETE.xlsx]21076'!C2))," & MiReemplazo & ",0),1)"
theFormulaPart2 = "IF(RIGHT('[HOGARES ALBACETE.xlsx]21076'!C1,LEN(R[-1]C)+2)=""[""&R[-1]C&""]"",'[HOGARES ALBACETE.xlsx]21076'!C2)"

With ActiveSheet.Range(“F2”)
        .FormulaArray = theFormulaPart1
        .Replace MiReemplazo, theFormulaPart2
    End With

我没有收到任何错误，但是 .Replace MiReemplazo, theFormulaPart2 部分什么也没做(我的意思是，替换没有发生，但代码执行了)

另外，尝试过:

ActiveSheet.Range("F2").FormulaArray = theFormulaPart1
        DoEvents
        Cells.Replace What:=MiReemplazo, Replacement:=theFormulaPart2, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

但是什么也没有。所以我有点没主意了。

此外，还检查了两个公式字符串的长度 (173,107)。我需要排序字符串吗？

我非常确定这不是问题:

1. 如果我手动输入 Excel 中的公式，则该公式有效。所以不是公式本身的问题
2. 我只是在 1 个单元格中工作，并尝试在其他工作簿的单元格中获取 1 个值，因此不是内存或资源问题。

提前致谢。

Answer 1

我敢打赌您的 Excel 未设置为使用 R1C1 引用，因此当您尝试将 R1C1 引用的字符串放入 A1 样式公式时，替换将不起作用。尝试使用:

Application.ReferenceStyle = xlR1C1
With ActiveSheet.Range("F2")
        .FormulaArray = theFormulaPart1
        .Replace MiReemplazo, theFormulaPart2
    End With
Application.ReferenceStyle = xlA1