数组公式通过VBA [英] Array Formula through VBA
问题描述
工作表(sheet1)。范围(Table2 [[& Me.cboSec公式数组=公式
这个最小的代码段适用于我:
工作表(sheet1)。范围(A1:B4 ).FormulaArray == AVERAGE(C1:C5)
这将使单元格从A1到B4显示单元格C1至C5中的数字的平均值,并且绑定到公式数组。
但是这个问题提到了对表的结构化引用。正如Rory所说,在表中没有公式数组可能。如果您尝试通过< CTRL>< SHIFT>< ENTER>
手动将数组插入表中,那么您可以挖掘地面,这是通常不是程序化办法。 Excel答案:表格中不允许使用多单元格数组公式。
但为了至少用一组相等的公式填充表格的单元格,这个最小的代码段适用于我:
工作表(sheet1)。ListObjects(Table2)。DataBodyRange.Formula = = SUM($ F $ 1:$ G $ 1)
或
工作表(sheet1)。ListObjects(Table2)。DataBodyRange.Cells.Formula == SUM($ F $ 1:$ G $ 1)
这是一个更鼓舞人心的提示: http://www.myonlinetraininghub.com/excel-2007-tables
对于表记录宏不会帮助记录粘贴到必要的最低复杂描述。
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
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.
这篇关于数组公式通过VBA的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!