Array Formula through VBA

0
votes

I am trying to write an array formula via VBA but getting an error " unable to set the FormulaArray property of the range class". I have tried both ' Range.FormulaArray' and '.Range.selection.FormulaArray' but nothing works. Thanks for helping me with this.

Worksheets("sheet1").Range("Table2[[" & Me.cboSec.value & " Row Score Max]]").FormulaArray = formulae
1
vbaexcel
How long is the formula? If it's over 255 characters you will get that error. - Rory
Is it really sheet1 or is it Sheet1? - Tanner
@Rory, the formula is less than 255 characters. - Malick
@Tannman357, The worksheet name is in small letters. - Malick
Okay just wanted to check - I've made many mistakes like that before. - Tanner

1 Answers

1
votes

This minimal snippet works for me:

Worksheets("sheet1").Range("A1:B4").FormulaArray = "=AVERAGE(C1:C5)"

This will have the cells from A1 to B4 showing the average of the numbers in cells C1 to C5, and being bound to a formula array.

But the question mentions structured references to a Table. As Rory mentioned, in a Table are no formula arrays possible. You can dig to the ground if you try to insert an array into the Table manually by <CTRL><SHIFT><ENTER>, which is the usual not programmatic way. Excel answers with: "Multi-cell array formulas are not allowed in tables."

But in order to at least fill the cells of a Table with a set of equal formulas, this minimal snippet works for me:

Worksheets("sheet1").ListObjects("Table2").DataBodyRange.Formula = "=SUM($F$1:$G$1)"

or 

Worksheets("sheet1").ListObjects("Table2").DataBodyRange.Cells.Formula = "=SUM($F$1:$G$1)"

Here is one more inspiring hint: http://www.myonlinetraininghub.com/excel-2007-tables

For the Table, recording macros won't help as recording sticks to the lowest complex description necessary.