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
```

### Answer：

Instead of A$1, try

```
INDIRECT(ADDRESS(1,COLUMN()))
```

### Answer：

Try to do it semi-automatically. Set formula for the first row, 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
```

Tags: excelexcel, vba