VBA输入单元格中的数组公式的问题与预期不符 [英] Issue with Array formula entered into cells by VBA is not behaving as expected

问题描述

在使用VBA将数组公式插入列中并向下复制该公式所引用的工作表中具有数据的行时，我遇到了问题.

I am having issues with using VBA to insert an array formula into a column and copy downwards for as many rows that have data in the worksheet that the formula is referencing.

如果我使用CSE，然后再向下拖动，则该公式将按预期工作.然后，我使用宏记录器捕获了CSE，并将公式转换为VBA，这就是失败的地方.

If I use CSE and then afterwards drag down, the formula works as expected. I then used the macro recorder to capture the CSE and to convert the formula to VBA and this is where it fails.

我尝试输入的公式尝试了尽可能多的变体，但无济于事.

I have tried as many variations on entering the formula as I can find but nothing works.

在名为" Pack "的工作表中输入到单元格C2 中的数组公式；查看名为" FolderDataImport "的工作表的单元格A1 ；如果存在数据，则它将执行一些任务.

The array formula which is entered into cell C2 in a worksheet called "Pack" looks at Cell A1 of a worksheet called "FolderDataImport" and if data is present it then performs a couple of tasks.

只要输入工作表中名为" Pack "的工作表中对应的单元格，输入此公式的VBA应该将其复制到 C列中.只要存在数据就可以.

This VBA that enters this formula should copy it down Column C as long as the corisponding cell in worksheet called "Pack" for as long as there is data present.

因此，当我使用数组公式运行代码时，发生的事情是它不会自动更改单元格引用(就像您将公式向下拖动到列A1，A2等)，而只是复制了相同的单元格始终沿列引用.

So what happens when I run the code with the array formula is that it does not auto change the cell references (as if you were dragging the formula down the column A1, A2, and so on) and just copied the same cell reference all of the way down the column.

是否还将单元格引用从FolderDataImport！ A1 更改为FolderDataImport！ A1048571 ?

It also changes the Cell reference from FolderDataImport!A1 to FolderDataImport!A1048571?

Sub InsertFormulasPack()
    Dim SWs As Worksheet, TWs As Worksheet
    Dim Lr As Long
    `...
    Set SWs = Worksheets("FolderDataImport")
    Set TWs = Worksheets("Pack")
    Lr = SWs.Range("A" & SWs.Rows.Count).End(xlUp).Row
    `...
 
    ' Column C Formula
        TWs.Range("C2:C" & Lr + 1).FormulaArray = _
        "=MID(FolderDataImport!R[-7]C[-2],FIND(""-"",FolderDataImport!R[-7]C[-2])+2,MIN(FIND({""["",""(""},FolderDataImport!R[-7]C[-2]))-FIND(""-"",FolderDataImport!R[-7]C[-2])-3)"
    
    '...

End Sub

推荐答案

数组公式可以采用两种形式:

An Array Formula can take two forms:

• 如果选择一个范围，请使用CSE输入公式，它将是应用于所选范围的单个数组公式.(这是您的VBA代码正在尝试的操作)
• 如果您选择一个单元格，请使用CSE输入公式，然后向下拖动，每个单元格将包含一个单独的数组公式(这似乎是您想要实现的)

要实现第二种选择，可以使用此技术

To achieve the second option, this technique can be used

With TWs.Range("C2:C" & Lr + 1)
    'create a standard formula
    .FormulaR1C1 = "=MID(FolderDataImport!R[-1]C[-2],FIND(""-"",FolderDataImport!R[-1]C[-2])+2,MIN(FIND({""["",""(""},FolderDataImport!R[-1]C[-2]))-FIND(""-"",FolderDataImport!R[-1]C[-2])-3)"
    
    'convert to array formula
    .FormulaArray = .FormulaR1C1
End With

注意:

1. 您的代码中有问题，您在单元格 C2 中使用了 R [-7] C [-2] .第2-7行环绕到工作表的末尾，因此引用第1048571行.我在演示中使用了 R [-1] C-2] ，您必须对其进行调整才能获得抵消您真正想要的.
2. 没有示例数据，我不能确定给出的公式是否正确，但是它确实演示了如何输入数组公式

1. there is an issue in your code, you using R[-7]C[-2] in cell C2. Row 2 - 7 wraps to the end of the sheet, thus refers to row 1048571. I used R[-1]C-2] in my demo, you'll have to adjust it to get the offset you actually want.
2. Without sample data I can't be sure this gives the correct formula, but it does demonstarte how to enter array formulas

这篇关于VBA输入单元格中的数组公式的问题与预期不符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！