title: ArrayFormula in VBA using Excel 2007
body: <p>This array formula works just fine if entered using ctrl sht enter:</p>
  
  <pre><code>={IF(ROWS(A$7:A7)&gt;A$4,"",IF(SUMPRODUCT((consumers=$A$6)*(data=A$5)*(data=A$5&lt;&gt;""))&gt;0,INDEX 
  (employees,SMALL(IF(((consumers=$A$6)*(data=A$5)*(data=A$5&lt;&gt;"")),COLUMN(Data!$B$2:$AC$2)-COLUMN 
  (Data!$B$2)+1),ROWS(A$7:A7)))))}
  </code></pre>
  
  <p>I did some research and read that I would have to use the R1C1 notatation if I wanted to use this formula in VBA ... so this is what I have thus far:</p>
  
  <pre><code>Sub frmarry()
     Range("y28").Select
     Selection.FormulaArray = "=IF(ROWS(R28C25:R28C25)&gt;R26C25,"",IF(SUMPRODUCT((consumers=R27C25)* 
     (data=R24C7)*(data=R24C7&lt;&gt;""))&gt;0,INDEX(staff,SMALL(IF(((consumers=R27C25)*(data=R24C7)* 
     (data=R24C7&lt;&gt;"")),COLUMN(Data!$R2C2:$R2C29)-COLUMN(Data!R2C2)+1),ROWS(R28C25:R28C25)))))"
  End Sub
  </code></pre>
  
  <p>Now, when I run the routine I get a flag:</p>
  
  <pre><code>  'unable to set the FormaulaArray property of the Range class'
  </code></pre>
  
  <p>and if I select debug, it highlights the above arrayformula.</p>
  
  <p>I just used the excel setting to set my formula to R1C1 notation so maybe something went astray in the conversion. Since I am not to conversant with R1C1 notation I was hoping someone more conversant with R1C1 would help me resolve this issue or point me in a direction so I can troubleshoot this VBA issue.</p>
  
  <p>Thanks.</p>