title: Set ArrayFormula to many Excel cells using VBA
body: <p>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.</p>
  
  <p>To show you what I mean, I'm using the following code:</p>
  
  <pre><code>With MarginalData
      .Range(.Cells(2, 1), .Cells(13, .UsedRange.Columns.Count)).FormulaArray = pullFormula
  End With
  </code></pre>
  
  <p><strong>What I want</strong>, is a result that looks like this:
  <img src="https://i.stack.imgur.com/Rs3rq.png" alt="desired result"></p>
  
  <p>That is what it looks like when I enter the array formula separately in every cell in the range.</p>
  
  <p>But <strong>what I get</strong> is this:
  <img src="https://i.stack.imgur.com/Auiru.png" alt="given result"></p>
  
  <p>The output of the array formula in the first cell is repeated in all the columns - they all share the same output.</p>
  
  <p>How can I programatically assign the array formula as though each cell had it assigned separately?</p>
  
  <hr>
  
  <p>The formula is:</p>
  
  <blockquote>
    <p>{=INDEX(BatchResults,MATCH(TTID&amp;CHAR(1)&amp;ROW()-1,BatchResultsTTIDS&amp;CHAR(1)&amp;BatchResultsLayers,0),MATCH(A$1,BatchTTIDData!$1:$1,0))}</p>
  </blockquote>
  
  <p>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.</p>
  
  <hr>
  
  <p>The simplest solution so far, a variant of the accepted answer below, is the following:</p>
  
  <pre><code>Const pullFormula = "=INDEX(BatchResults,MATCH(TTID&amp;CHAR(1)&amp;ROW()-1,BatchResultsTTIDS&amp;CHAR(1)&amp;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
  </code></pre>