title: How to automatically input an array formula as string with more than 255 characters in length into an excel cell using VBA?
body: <p>My codes work for non-array formulas, but when I try it as an array formula, it ends up pasting the whole line of code instead of the result it should give out. This is how it looks like:</p>
  
  <p><a href="https://i.stack.imgur.com/ofw0o.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ofw0o.png" alt="enter image description here"></a>         </p>
  
  <p>My VBA code is as shown below (no need to read whole code):</p>
  
  <pre><code>Worksheets("DTR").Cells(Total_Rows_Formulas + 1, 20) = "{=IF(INDEX('Payroll Tables and Settings'!D$2:D$1048576,MATCH(DTR!B" &amp; Total_Rows_Formulas + 1 &amp; ",'Payroll Tables and Settings'!A$2:A$1048576,0))=""Extra"",P" &amp; Total_Rows_Formulas + 1 &amp; "*INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" &amp; Total_Rows_Formulas + 1 &amp; ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8,IF(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B" &amp; Total_Rows_Formulas + 1 &amp; ",'Payroll Tables and Settings'!A$2:A$1048576,0))&gt;0,IF(AI" &amp; Total_Rows_Formulas + 1 &amp; "=""Sunday"",0,IF(OR(AF2&gt;=24,AF2&lt;=8)=TRUE," &amp; _
          "(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B" &amp; Total_Rows_Formulas + 1 &amp; ",'Payroll Tables and Settings'!A$2:A$1048576,0))/2)/(INDEX('Payroll Tables and Settings'!AC$2:AC$538,MATCH(1,IF(DTR!C" &amp; Total_Rows_Formulas + 1 &amp; "&gt;='Payroll Tables and Settings'!Z$2:Z$538,IF(DTR!C" &amp; Total_Rows_Formulas + 1 &amp; "&lt;='Payroll Tables and Settings'!AA$2:AA$538,1)),0))-INDEX('Payroll Tables and Settings'!AB$2:AB$538,MATCH(1,IF(DTR!C" &amp; Total_Rows_Formulas + 1 &amp; "&gt;='Payroll Tables and Settings'!Z$2:Z$538,IF(DTR!C" &amp; Total_Rows_Formulas + 1 &amp; "&lt;='Payroll Tables and Settings'!AA$2:AA$538,1)),0)))" &amp; _
          "-IF(DTR!AI" &amp; Total_Rows_Formulas + 1 &amp; "=""Sunday"",0,IF(SUM(DTR!P" &amp; Total_Rows_Formulas + 1 &amp; ":S" &amp; Total_Rows_Formulas + 1 &amp; ")&lt;8,IF(IFERROR(INDEX('Holidays Table'!B$2:B$1048576,MATCH(C" &amp; Total_Rows_Formulas + 1 &amp; ",'Holidays Table'!A$2:A$1048576,0)),0)=0,(8-SUM(DTR!P" &amp; Total_Rows_Formulas + 1 &amp; ":S" &amp; Total_Rows_Formulas + 1 &amp; "))*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" &amp; Total_Rows_Formulas + 1 &amp; ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8),0),0))," &amp; _
          "(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B" &amp; Total_Rows_Formulas + 1 &amp; ",'Payroll Tables and Settings'!A$2:A$1048576,0))/2)/(INDEX('Payroll Tables and Settings'!AG$2:AG$538,MATCH(1,IF(DTR!C" &amp; Total_Rows_Formulas + 1 &amp; "&gt;='Payroll Tables and Settings'!AD$2:AD$538,IF(DTR!C" &amp; Total_Rows_Formulas + 1 &amp; "&lt;='Payroll Tables and Settings'!AE$2:AE$538,1)),0))-INDEX('Payroll Tables and Settings'!AF$2:AF$538,MATCH(1,IF(DTR!C" &amp; Total_Rows_Formulas + 1 &amp; "&gt;='Payroll Tables and Settings'!AD$2:AD$538,IF(DTR!C" &amp; Total_Rows_Formulas + 1 &amp; "&lt;='Payroll Tables and Settings'!AE$2:AE$538,1)),0)))" &amp; _
          "-IF(DTR!AI" &amp; Total_Rows_Formulas + 1 &amp; "=""Sunday"",0,IF(SUM(DTR!P" &amp; Total_Rows_Formulas + 1 &amp; ":S" &amp; Total_Rows_Formulas + 1 &amp; ")&lt;8,IF(IFERROR(INDEX('Holidays Table'!B$2:B$1048576,MATCH(C" &amp; Total_Rows_Formulas + 1 &amp; ",'Holidays Table'!A$2:A$1048576,0)),0)=0,(8-SUM(DTR!P" &amp; Total_Rows_Formulas + 1 &amp; ":S" &amp; Total_Rows_Formulas + 1 &amp; "))*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" &amp; Total_Rows_Formulas + 1 &amp; ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8),0),0)))),IF(Z" &amp; Total_Rows_Formulas + 1 &amp; "&gt;0,0,IF(AI" &amp; Total_Rows_Formulas + 1 &amp; "=""Sunday"",0,P" &amp; Total_Rows_Formulas + 1 &amp; "*INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" &amp; Total_Rows_Formulas + 1 &amp; ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8))))}"
  </code></pre>
  
  <p>After trying <code>Worksheets("DTR").Cells(Total_Rows_Formulas + 1, 20).FormulaArray = ...</code>:</p>
  
  <p><a href="https://i.stack.imgur.com/rBka2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/rBka2.png" alt="enter image description here"></a></p>
  
  <p>Latest Attempt:</p>
  
  <pre><code>With Worksheets("DTR").Cells(Total_Rows_Formulas + 1, 20)
          .FormulaArray = "=IF(INDEX('Payroll Tables and Settings'!D$2:D$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))=""Extra"",P2*INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))/8,2424)"
          .Replace "2424", "IF(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))&gt;0, IF(AI2=""Sunday"",0, IF(OR(AF2&gt;=24,AF2&lt;=8)=TRUE,1111+2222+3333,b.)),4444+5555+6666)"
          .Replace "1111", "(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))/2)"
          .Replace "+2222", "/(INDEX('Payroll Tables and Settings'!AC$2:AC$538,MATCH(1,IF(DTR!C2&gt;='Payroll Tables and Settings'!Z$2:Z$538,IF(DTR!C2&lt;='Payroll Tables and Settings'!AA$2:AA$538,1)),0))+8888)"
          .Replace "+8888)", "-INDEX('Payroll Tables and Settings'!AB$2:AB$538,MATCH(1,IF(DTR!C2&gt;='Payroll Tables and Settings'!Z$2:Z$538,IF(DTR!C2&lt;='Payroll Tables and Settings'!AA$2:AA$538,1)),0)))"
          .Replace "+3333", "IF(SUM(DTR!P2:S2)&lt;8,IF(IFERROR(INDEX('Holidays Table'!B$2:B$104857,MATCH(C2,'Holidays Table'!A$2:A$104857,0)),0)=0,(8-SUM(DTR!P2:S2))*(INDEX('Payroll Tables and Settings'!B$2:B$104857,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$104857,0))/8),0),0)"
          .Replace "4444", "(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))/2)"
          .Replace "+5555", "/(INDEX('Payroll Tables and Settings'!AG$2:AG$538,MATCH(1,IF(DTR!C2&gt;='Payroll Tables and Settings'!AD$2:AD$538,IF(DTR!C2&lt;='Payroll Tables and Settings'!AE$2:AE$538,1)),0))+1212)"
          .Replace "+1212)", "-INDEX('Payroll Tables and Settings'!AF$2:AF$538,MATCH(1,IF(DTR!C2&gt;='Payroll Tables and Settings'!AD$2:AD$538,IF(DTR!C2&lt;='Payroll Tables and Settings'!AE$2:AE$538,1)),0)))"
          .Replace "+6666", "IF(SUM(DTR!P2:S2)&lt;8,IF(IFERROR(INDEX('Holidays Table'!B$2:B$104857,MATCH(C2,'Holidays Table'!A$2:A$104857,0)),0)=0,(8-SUM(DTR!P2:S2))*(INDEX('Payroll Tables and Settings'!B$2:B$104857,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$104857,0))/8),0),0)"
  End With
  </code></pre>
  
  <p>Still does not work with the same run-time error as shown above</p>
  
  <p>Working Code (thanks to Chris Mack):</p>
  
  <pre><code>With Worksheets("DTR").Cells(Total_Rows_Formulas + 1, 20)
          .FormulaArray = "=IF(INDEX('Payroll Tables and Settings'!D$2:D$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))=""Extra"",P2*7777/8,2424)"
          .Replace "7777", "INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))"
          .Replace "2424", "IF(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))&gt;0, IF(AI2=""Sunday"",0, IF(OR(AF2&gt;=24,AF2&lt;=8)=TRUE,1111+2222+3333,4444+5555+6666)),3434)"
          .Replace "1111", "(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))/2)"
          .Replace "+2222", "/(INDEX('Payroll Tables and Settings'!AC$2:AC$538,MATCH(1,IF(DTR!C2&gt;='Payroll Tables and Settings'!Z$2:Z$538,IF(DTR!C2&lt;='Payroll Tables and Settings'!AA$2:AA$538,1)),0))+8888)"
          .Replace "+8888)", "-INDEX('Payroll Tables and Settings'!AB$2:AB$538,MATCH(1,IF(DTR!C2&gt;='Payroll Tables and Settings'!Z$2:Z$538,IF(DTR!C2&lt;='Payroll Tables and Settings'!AA$2:AA$538,1)),0)))"
          .Replace "+3333", "-IF(SUM(DTR!P2:S2)&lt;8,IF(IFERROR(INDEX('Holidays Table'!B$2:B$104857,MATCH(C2,'Holidays Table'!A$2:A$104857,0)),0)=0,(8-SUM(DTR!P2:S2))*(INDEX('Payroll Tables and Settings'!B$2:B$104857,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$104857,0))/8),0),0)"
          .Replace "4444", "(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))/2)"
          .Replace "+5555", "/(INDEX('Payroll Tables and Settings'!AG$2:AG$538,MATCH(1,IF(DTR!C2&gt;='Payroll Tables and Settings'!AD$2:AD$538,IF(DTR!C2&lt;='Payroll Tables and Settings'!AE$2:AE$538,1)),0))+1212)"
          .Replace "+1212)", "-INDEX('Payroll Tables and Settings'!AF$2:AF$538,MATCH(1,IF(DTR!C2&gt;='Payroll Tables and Settings'!AD$2:AD$538,IF(DTR!C2&lt;='Payroll Tables and Settings'!AE$2:AE$538,1)),0)))"
          .Replace "+6666", "-IF(SUM(DTR!P2:S2)&lt;8,IF(IFERROR(INDEX('Holidays Table'!B$2:B$104857,MATCH(C2,'Holidays Table'!A$2:A$104857,0)),0)=0,(8-SUM(DTR!P2:S2))*(INDEX('Payroll Tables and Settings'!B$2:B$104857,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$104857,0))/8),0),0)"
          .Replace "3434", "IF(Z2&gt;0,0,IF(AI2=""Sunday"",0,P2*INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))/8))"
  End With
  </code></pre>