title: Array formula to VBA
body: <p>I have an Excel spreadsheet with two worksheets titled “Cities” and “Data”.
  The "Data" page contains 108264 rows of data, and columns progress all the way up to column AT. </p>
  
  <p>Under the Cities worksheet, I have a list of 210 cities from rows B4 to B214. Next to it (Column C) is a list of the counts of codes used for each city (i.e. how many codes did that city use). The next 20 columns (Columns D to W) should show a sequence of the most commonly used codes for each city (i.e. most common to least common). I’ve enclosed images with sample pseudo dataset to provide a graphical representation of what I’m referring to. </p>
  
  <p><img src="https://i.stack.imgur.com/hs4jw.jpg" alt="Cities Worksheet"></p>
  
  <p><img src="https://i.stack.imgur.com/mMB1n.jpg" alt="Data Worksheet"></p>
  
  <p>If you'll look at City "1" for example (row 4 "Cities") you will notice it has a Count of 5, and the most frequently used code is 5, then 4, then 3, then 2 and finally 1. If you refer to the "Data" image, you can see the correlation.</p>
  
  <p>The array formulas I've used for this sample set are as follows:</p>
  
  <p>In D4 of "Cities"</p>
  
  <pre><code>      {=IFERROR((MODE(IF(ISNUMBER(SEARCH(B4,Data!$B2:$B6)),IF(ISNUMBER(Data!$K2:$AT6),Data!$K2:$AT6)))),"")}
  </code></pre>
  
  <p>In E4 of "Cities"</p>
  
  <pre><code>{=IFERROR(MODE(IFERROR(SMALL(IF(ISNUMBER(SEARCH($B$4, Data!$B2:$B6))*ISNUMBER(1/Data!$K2:$AT6)*ISNA(MATCH(Data!$K2:$AT6,$D4:D4,0)),Data!$K2:$AT6,""),ROW(INDEX($A:$A,1):INDEX($A:$A,COUNT(Data!$K2:$AT6)))),"")),"")}
  </code></pre>
  
  <p>Then I drag the formula from E4 onwards, and it automatically counts the frequency of the commonly used codes based on the data in the previous column. </p>
  
  <p><strong>The objective is this:</strong> for each city noted in “Cities” worksheet, I’d like to return those 20 most commonly used codes by searching Columns B and Columns K to AT from the “Data” Worksheet. So it would look up the city in Column B, then look across to which codes were commonly used in Columns K to AT.</p>
  
  <p>I do have two array formulas I use for this (i.e. that counts the most used code, than depending on the value in the previous column, returns the next most commonly used code). The problem is, due to such a large dataset, creating an array formula for each and every cell becomes time consuming, and slows down the Excel spread sheet considerably.</p>
  
  <p>So, this is what I’ve tried so far:</p>
  
  <ol>
  <li>Array formulas (please also refer to the enclosed sheet)</li>
  <li>Below VBAs. First one returns a <em>Run-time error ‘1004’ Unable to set the FormulaArray property of the Range Class</em>, while the second does nothing. </li>
  </ol>
  
  <p>Any suggestions or help on either speeding up the array formulas, or modifying the VBA accordingly would be greatly appreciated. If you have an alternate VBA as well, that'd be appreciated too.</p>
  
  <p>Thanks. </p>
  
  <pre><code>Sub Option1()
       Dim r As Long
       For r = 4 To 214
          Sheet2.Cells(r, 210).FormulaArray = _
          "=IFERROR((MODE(IF(ISNUMBER(SEARCH(C" &amp; CStr(r) &amp; ", Data!$B$2:$B$108264)),IF(ISNUMBER(Data!$K2:$AT108264),Data!$K2:$AT108264)))),"")"
      Next r
   End Sub
  
  
  
  Sub Option2()
  
      Sheet1.Range("C4").FormulaArray = _
          "=IFERROR((MODE(IF(ISNUMBER(SEARCH(C4, Data!$B$2:$B$108264)),IF(ISNUMBER(Data!$K2:$AT108264),Data!$K2:$AT108264)))),"")"
  
      Sheet1.Range("D4:D214").FillDown
  
  End Sub
  </code></pre>