title: VBA 1004 error - Unable to set the FormulaArray property of the Range class
body: <p>I am inserting the following INDEX MATCH formula into a set of cells using VBA. </p>
  
  <pre><code>        cell2.FormulaArray = _
  "=INDEX('[08 Debt Comparison &amp; Provision Report.xlsx]Details by Bus Area &amp;  Location'!AK:AK, _ 
      MATCH(1,('[08 Debt Comparison &amp; Provision Report.xlsx]Details by Bus Area &amp;  Location'!$A:$A = A " &amp; value &amp; ")* _
  ('[08 Debt Comparison &amp; Provision Report.xlsx]Details by Bus Area &amp;  Location'!$B:$B=""Total""),0))*1000"
  </code></pre>
  
  <p>When this runs, I encounter an error 1004 - "Unable to set the FormulaArray property of the Range class"</p>
  
  <p>I'm fairly certain it has something to do with referencing a workbook outside of the current as it works fine when I INDEX MATCH inside the same workbook.</p>
  
  <p>The condensed version of the formula (for reading clarity) is the following - </p>
  
  <pre><code>=INDEX(Sheet2!AK:AK, MATCH(1,(Sheet2!A:A = A5)*(Sheet2!B:B="Total"),0))
  </code></pre>
  
  <p>Then in vba -</p>
  
  <pre><code>cell2.FormulaArray = "=INDEX(Sheet2!C:C, MATCH(1,(Sheet2!A:A = A5)*(Sheet2!B:B=""Total""),0))"
  </code></pre>