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

问题描述

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

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

 使用MarginalData 
 .Range（.Cells（2，1），.Cells（13，.UsedRange.Columns.Count））。FormulaArray = pullFormula 
结束
  

我想要的结果如下：

这是我在每个单独输入数组公式时的样子

但是我得到的是：

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

如何编程分配数组公式，就像每个单元格分别分配一样？

---

公式是：

{= INDEX（BatchResults，MATCH（TTID& CHAR（1） & ROW（） - 1，BatchResultsTTIDS& CHAR（1）& BatchResultsLayers，0），MATCH（A $ 1，BatchTTIDData！$ 1：$ 1,0））}

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

---

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

  Const pullFormula == INDEX（BatchResults， MATCH（TTID& CHAR（1）& ROW（）-1，BatchResultsTTIDS& CHAR（1）& BatchResultsLayers，0），MATCH（A $ 1，BatchTTIDData！$ 1：$ 1,0））
 With wrksht 
带.Range（.Cells（2，1），.Cells（13，.UsedRange.Columns.Count））
 .Formula = pullFormula 
 .FormulaArray = .FormulaR1C1 
结束
结束
  

解决方案

或选择阵列公式为R1C1，将其分配给公式R1C1，然后将公式R1C1分配为数组公式。这假设数组公式在单元格A2中

 子测试（）
 
 With Sheet1 
 pullFormula = .Range（A2）。FormulaR1C1 
设置Rng = .Range（.Cells（2，1），.Cells（13，.UsedRange.Columns.Count））
 
 Rng.Formula = pullFormula 
 Rng.FormulaArray = Rng.FormulaR1C1 
 
 End with 
 End Sub 
  

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 get is 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))}

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

解决方案

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屋！