尝试使用FormulaArray时出现错误1004.替换把戏不起作用 [英] ERROR 1004 when trying to use FormulaArray. Replace trick does not work

问题描述

背景:我得到了一个很酷的数组公式，它在Excel中非常有效.现在，我尝试使用VBA做相同的公式.因此，我在单元格中键入了数组公式，并使用宏进行了记录.该公式非常完美.宏记录器让我知道了这一点:

BACKGROUND: I got a cool Array Formula and it works perfect in Excel. Now I'm trying to do the same formula, but with VBA. So I typed the Array Formula in a cell and recorded with a macro. The formula works perfect. The macro recorder gets me this:

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.没什么.

If I try to run the code above, I get error 1004. The sub has just that line. Nothing else.

经过一番研究，我发现了这一点:

After some researching I got into this:

VBA运行时错误1004:无法设置范围类的FormulaArray属性

在VBA中输入长数组公式

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

So I splitted the formula into 2 parts:

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没有执行任何操作(我的意思是，替换没有发生，但是代码已执行)

And I get no errors, but the part .Replace MiReemplazo, theFormulaPart2 does nothing (I mean, the replace does not happen, but the code executes)

也尝试过:

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

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

But nothing. So I'm kinda out of ideas.

还要检查两个公式字符串的长度(173,107).我需要整理器字符串吗?

Also, checked the lenght of both formulas strings (173,107). Do I need sorther strings?

我认为不是问题，

1. 如果我手动键入，则Excel中的公式将起作用.公式本身不是问题
2. 我只是在1个单元格中工作，并试图在其他工作簿的单元格中获得1个值，所以这不是内存或资源的问题.

谢谢.

推荐答案

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

I'd bet your Excel is not set to use R1C1 referencing, so the replace won't work as you're trying to put an R1C1 referenced string into an A1 style formula. Try using:

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

这篇关于尝试使用FormulaArray时出现错误1004.替换把戏不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！