使用 VBA 将 ArrayFormula 设置为多个 Excel 单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6048076/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Set ArrayFormula to many Excel cells using VBA
提问by Alain
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.
这就是我在范围内的每个单元格中分别输入数组公式时的样子。
But what I getis this:
但我得到的是：
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?
如何以编程方式分配数组公式，就像每个单元格都单独分配一样？
The formula is:
公式为：
{=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,BatchTTIDData!:,0))"
With wrksht
With .Range(.Cells(2, 1), .Cells(13, .UsedRange.Columns.Count))
.Formula = pullFormula
.FormulaArray = .FormulaR1C1
End With
End With
采纳答案by osknows
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
或选取数组公式为 R1C1，将范围指定为 FormulaR1C1，然后将 FormulaR1C1 指定为数组公式。这假设数组公式在单元格 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
回答by Lance Roberts
Instead of A$1, try
而不是 1 澳元，试试
INDIRECT(ADDRESS(1,COLUMN()))
回答by Steve Mallory
Try to do it semi-automatically. Set formula for the first row, then use FillDown.
尝试半自动完成。为第一行设置公式，然后使用 FillDown。
Private Sub soCopyFormula()
Dim MarginalData As Worksheet
Set MarginalData = ActiveWorkbook.Worksheets("Sheet2")
Dim oRange As Range
Dim i As Integer
With MarginalData
Set oRange = .Range(.Cells(2, 1), .Cells(13, .UsedRange.Columns.Count))
' for each column
For i = 0 To oRange.Columns.Count - 1
' set first row
oRange(1, i).FormulaArray = pullFormula
' copy down
oRange.Columns(i).FillDown
Next
End With
End Sub
