使用 VBA 将 ArrayFormula 设置为多个 Excel 单元格 [英] Set ArrayFormula to many Excel cells using VBA

问题描述

我有一个输出单个值的数组公式，我想为一大堆单元格提供相同的数组公式.问题是当我将数组公式分配给范围时，它以这样一种方式解释公式，因为它们都共享对数组公式的单个调用的输出，而不是每个都输出一个单独的值.

I have an array formula that outputs a single value, and I want to give a whole bunch of cells this same array formula. The problem is when I assign the array formula to the range, it interprets the formula in such a way as them all sharing the output of a single call to the array formula, rather than each of them outputting a separate value.

为了向您展示我的意思，我使用了以下代码:

To show you what I mean, I'm using the following code:

With MarginalData
    .Range(.Cells(2, 1), .Cells(13, .UsedRange.Columns.Count)).FormulaArray = pullFormula
End With

我想要的，是一个看起来像这样的结果:

What I want, is a result that looks like this:

这就是我在范围内的每个单元格中分别输入数组公式时的样子.

That is what it looks like when I enter the array formula separately in every cell in the range.

但是我得到的是这样的:

第一个单元格中数组公式的输出在所有列中重复 - 它们都共享相同的输出.

The output of the array formula in the first cell is repeated in all the columns - they all share the same output.

如何以编程方式分配数组公式，就像每个单元格都单独分配一样?

How can I programatically assign the array formula as though each cell had it assigned separately?

公式为:

{=INDEX(BatchResults,MATCH(TTID&CHAR(1)&ROW()-1,BatchResultsTTIDS&CHAR(1)&BatchResultsLayers,0),MATCH(A$1,BatchTTIDData!$1:$1,0))}

{=INDEX(BatchResults,MATCH(TTID&CHAR(1)&ROW()-1,BatchResultsTTIDS&CHAR(1)&BatchResultsLayers,0),MATCH(A$1,BatchTTIDData!$1:$1,0))}

它必须作为数组公式放入，因为它不是在单个列上执行匹配，而是在两个连接的列上执行匹配.列的串联必须作为数组返回，因此公式必须作为数组公式输入.

It must be put in as an array formula because it performs a match not on a single column, but on two concatenated columns. The concatenation of the columns must be returned as an array, hence the formula must be entered as an array formula.

到目前为止，最简单的解决方案是以下已接受答案的变体:

The simplest solution so far, a variant of the accepted answer below, is the following:

Const pullFormula = "=INDEX(BatchResults,MATCH(TTID&CHAR(1)&ROW()-1,BatchResultsTTIDS&CHAR(1)&BatchResultsLayers,0),MATCH(A$1,BatchTTIDData!$1:$1,0))"
With wrksht
    With .Range(.Cells(2, 1), .Cells(13, .UsedRange.Columns.Count))
        .Formula = pullFormula
        .FormulaArray = .FormulaR1C1
    End With
End With

推荐答案

或者选择数组公式为 R1C1，分配到范围为 FormulaR1C1，然后将 FormulaR1C1 分配为数组公式.这假设数组公式在单元格 A2 中

Or pick up the Array Formula as R1C1, assign to the range as FormulaR1C1, then assign the FormulaR1C1 as Array Formula. This assumes Array Formula is in cell A2

Sub test()

With Sheet1
    pullFormula = .Range("A2").FormulaR1C1
    Set Rng = .Range(.Cells(2, 1), .Cells(13, .UsedRange.Columns.Count))

    Rng.Formula = pullFormula
    Rng.FormulaArray = Rng.FormulaR1C1

End With
End Sub

这篇关于使用 VBA 将 ArrayFormula 设置为多个 Excel 单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！