title: Array Formula via VBA with or without .ArrayFormula For Dynamic Range
body: <p>I am trying to translate a procedure previously done without VBA on an Excel sheet.</p>
  <p>This inserts an array formula:</p>
  <blockquote>
  <p>={MAX(IF(C2:C355=C2,F2:F355))} into cell CE2</p>
  </blockquote>
  <p>and drag it down to the bottom of the data set, which is variable.</p>
  <p>I have attempted different options looping through a variable data set, such as:</p>
  <pre class="lang-vb prettyprint-override"><code>Dim i As Variant
  LastRow = Worksheets(&quot;Sheet1&quot;).Range(&quot;A&quot; &amp; Rows.Count).End(xlUp).Row
  
  For i = 2 To LastRow
  
      Cells(i, 83).FormulaArray = &quot;=MAX(IF(cells(2,LastRow)= cells(5,i),cells(2,LastRow))&quot;
  
  Next i
  </code></pre>
  <p>The following code seems to work, but my attempts to work the code into a dynamic loop causes errors:</p>
  <pre><code>Range(&quot;CE2&quot;).FormulaArray = &quot;=MAX(IF(C:C=C2,F:F))&quot;
  </code></pre>
  <p>Often the error is:</p>
  <blockquote>
  <p>&quot;Unable to set the FormulaArray property of the range class&quot;.</p>
  </blockquote>
  <p>I have noticed that array formulas placed onto the sheet via VBA are slow. I am guessing that there is a way to achieve the same result as my formula via VBA that does NOT use <code>.VarriantArray</code>.</p>
  <p>I have looked into the MAX function.</p>
  <p>How do I</p>
  <ol>
  <li><p>Loop through a dynamic array and place my array formula on the sheet?</p>
  </li>
  <li><p>Achieve the same result as my array formula using VBA functions other than <code>.ArrayFormula</code>?</p>
  </li>
  </ol>