Install ArrayFormula in many Excel cells using VBA

Question

I have an array formula that outputs a single value, and I want to give a whole bunch of cells the same array formula. The problem is that when I assign an array formula to a range, it interprets the formula so that they all share the output of one call to the array formula, and not each of them gives a separate value.

To show you what I mean, I use the following code:

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

I want it to look like this:

Here's what it looks like when I enter an array formula separately in each cell of the range.

But what I get :

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

How can I programmatically assign an array formula as if each cell were assigned separately?

---

Formula:

{= INDEX (BatchResults, MATCH (TTiD & CHAR (1) &! LINE () - 1, BatchResultsTTIDS & CHAR (1) & BatchResultsLayers, 0), SEARCH (A $ 1, BatchTTIDData $ 1: $ 1.0))}

It should be placed as an array formula because it does not match in one column, but in two concatenated columns. Colon concatenation must be returned as an array, so the formula must be entered as an array formula.

---

The simplest solution at the moment is the version of the answer below:

 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 

Answer 1

Or take an array formula as R1C1, assign a range as FormulaR1C1, then assign Formula R1C1 as an array formula. This assumes the 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 

Answer 2

Instead of A $ 1, try

 INDIRECT(ADDRESS(1,COLUMN())) 

Answer 3

Try to do this semi-automatically. Set the formula for the first line, then use 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