代码之家 › 专栏 › 技术社区 › Foxfire And Burns And Burns

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

excel-vba vba excel

2

Foxfire And Burns And Burns · 技术社区 · 6 年前

背景: 我得到了一个很酷的数组公式,它在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

但什么都没有。所以我有点不知所措。

此外,还检查了两个公式字符串的长度(173107)。我需要整理一下琴弦吗?

我很确定这不是问题:

如果我手动键入,Excel中的公式就可以工作。所以这不是公式本身的问题
我只是在一个单元格中工作,并试图在其他工作簿的单元格中获取1个值,所以这不是内存或资源问题。

事先谢谢。

2 回复 | 直到 6 年前

1

4

Rory 6 年前

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

2

0

user4039065 6 年前

Sub arrayFormulaTooBig()
    Dim ha2ndx As Long, wbha As Workbook, wbf As Workbook
    Dim sel As Range

    Set sel = Selection

    Set wbha = Workbooks("HOGARES ALBACETE.xlsx")
    Set wbf = sel.parent.parent

    'Application.Calculation = xlCalculationmanual
    'Application.ScreenUpdating = False

    'move the external worksheet to local and reduce worksheet name to minimum characters
    With wbha
        If .Worksheets.Count = 1 Then
            .Worksheets.Add after:=.Worksheets(.Worksheets.Count)
            .Worksheets(.Worksheets.Count).Name = "to be removed"
        End If
        With .Worksheets("21076")
            ha2ndx = .Index
            .Move after:=wbf.Worksheets(wbf.Worksheets.Count)
        End With
    End With

    'minimize worksheet name
    wbf.Worksheets("21076").Name = ChrW(215)

    'from 282 characters
    '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)"
    'to 137 characters
    sel.FormulaArray = _
        "=INDEX(Ã!C1,MATCH(MAX(IF(RIGHT(Ã!C1,LEN(R[-1]C)+2)=""["" &R[-1]C&""]"",Ã!C2)),IF(RIGHT(Ã!C1,LEN(R[-1]C)+2)=""[""&R[-1]C&""]"",Ã!C2),0),1)"

    With wbf
        With .Worksheets(ChrW(215))
            .Move before:=wbha.Worksheets(ha2ndx)
        End With
    End With

    'restore worksheet name
    wbha.Worksheets(ChrW(215)).Name = "21076"

    On Error Resume Next
    Application.DisplayAlerts = False
    wbha.Worksheets("to be removed").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub