时间戳

首页 > 解决方案 > 我有一个很长的公式数组，替换函数没有填充单元格

excel - 我有一个很长的公式数组，替换函数没有填充单元格

问题描述

我正在尝试使用替换来输入我的长数组公式。我认为公式很好，但它不会填充单元格。我想替换J,K并W在formula1. 我没有收到错误消息，只有公式应该在的空列。

我尝试以 A1 参考样式输入它，但这也不起作用。

Dim formula1 As String
Dim formula2 As String
Dim formula3 As String
Dim formula4 As String
Dim origRS As Long

origRS = Application.ReferenceStyle

Application.ReferenceStyle = xlR1C1

formula1 = "=IFERROR(IFERROR(INDEX('C:\Name\Documents\[salesorders.txt]salesorders'!C17,JKW'C:\Name\Documents\[salesorders.txt]salesorders'!C9,0))),0)"
formula2 = "MATCH(RC[-17]&RC[-13]&RC[-12],'C:\Name\Documents\[salesorders.txt]salesorders'!C8&'C:\Name\Documents\[salesorders.txt]salesorders'!C34&"
formula3 = "'C:\Name\Documents\[salesorders.txt]salesorders'!C9,0)),INDEX('C:\Name\Documents\[salesorders.txt]salesorders'!C17,"
formula4 = "MATCH(RC[-17]&RC[-13]&-RC[-12],'C:\Name\Documents\[salesorders.txt]salesorders'!C8&'C:\Name\Documents\[salesorders.txt]salesorders'!C34&"


  With ActiveSheet.Range("R2:R & Lastrow")
    .FormulaArray = formula1
    .Replace What:="J", Replacement:=formula2, lookat:=xlPart
    .Replace What:="K", Replacement:=formula3, lookat:=xlPart
    .Replace What:="W", Replacement:=formula4, lookat:=xlPart

End With

Application.ReferenceStyle = origRS

标签： excelvba

解决方案

您替换部分数组公式的方法是正确的，但是，您的代码存在一些问题：

  1. With ActiveSheet.Range("R2:R & Lastrow")需要替换为("R2:R" & Lastrow). 您还需要声明Lastrow变量 ( Dim Lastrow as Long)，然后以某种方式计算它。

  2. 您还需要验证整个公式。您会注意到这formula1已经不正确 - 括号数无效并且“中间” IFERROR 没有第二个参数（这是强制性的）。当您将原始公式粘贴到 Excel 中时，它会引发错误。我怀疑您希望您的第一个公式看起来像这样：

    =IFERROR(IFERROR(INDEX('C:\Name\Documents\[salesorders.txt]salesorders'!C17,JKW&'C:\Name\Documents\[salesorders.txt]salesorders'!C9),0),0)

其余的公式formula2, 3, 4也需要检查。

  1. 第三个潜在问题是您引用的是 .txt 文件 ( salesorders.txt) - 是否正确？

  2. 一旦这一切都解决了，你面临的第四个问题就是你的replace陈述。这些必须是公式的有效（完整）部分。对我来说，最简单的方法总是创建一个有效的公式（由 Excel 识别），例如，INDEX(1000,2000)然后替换。10002000

下面是一个适合我的调整代码。不确定最终公式是否是您正在寻找的，但是 - 再次 - 它的某些部分是无效的。我剥离了整个文件路径并在最后添加了它，只是为了使分析formula1-4更容易。另外请考虑不要混合 R1C1 和“标准”单元格引用（根据 Ron 的评论） - 这应该是标准化的。

    Dim formula1 As String, formula2 As String, formula3 As String, formula4 As String
    Dim origRS As Long, LastRow As Long, i As Long
    Dim cell As Range
    Dim varOrig As Variant, varRepl As Variant

    origRS = Application.ReferenceStyle

    Application.ReferenceStyle = xlR1C1

    formula1 = "=IFERROR(IFERROR(INDEX(salesorders!C17,1000, 2000),0),0)"
    formula2 = "MATCH(RC[-17]&RC[-13]&RC[-12],salesorders!C8&salesorders!C34&salesorders!C9,0)"
    formula3 = "INDEX(salesorders!C17,MATCH(RC[-17]&RC[-13]&-RC[-12],salesorders!C8&salesorders!C34),3000)"
    formula4 = "salesorders!C9"

    LastRow = 5
    varOrig = Array(1000, 2000, 3000)
    varRepl = Array(formula2, formula3, formula4)

    For Each cell In ActiveSheet.Range("R2:R" & LastRow)
        With cell
            .FormulaArray = formula1
            For i = 0 To 2
                .Replace varOrig(i), varRepl(i)
                .Replace "salesorders!", "'C:\Name\Documents\[salesorders.txt]salesorders'!"
            Next i
        End With
    Next cell

    Application.ReferenceStyle = origRS

推荐阅读