Working with two monitors is realy great. There are keyboard shortcuts in Windows 7 that make the work with them much easier and faster ...
If you are working with a Table (ListObject) manually, you can use an array formula. Simply past your formula with CTRL+SHIFT+ENTER. There is no problem and it works perfectly. But if you try paste the same array formula via VBA, you'll get this error: Run-time error '1004': Unable to set the FormulaArray property of the range class.
So, you can't insert an array formula by standard way, but you can use an other possibility - insert your formula into a defined name. Why? Because it is always evaluated as array formula. Then just paste reference to your defined name. Something like that:
Select all
ActiveWorkbook.Names.Add Name:="ArrayFormula", RefersTo:="=YourArrayFormula"
With ActiveSheet.ListObjects("TableName").ListColumns("ColumnName").DataBodyRange
.Formula = "=ArrayFormula"
End With 'ActiveSheet.ListObjects("TableName").ListColumns("ColumnName").DataBodyRange
Update
The error is caused when I try insert an array formula into an entire column at once. But if insert it into a single cell of the column, it works (thanks to Daniel McCracken). Here is a way with no named formula:
Select all
With ActiveSheet.ListObjects("TableName").ListColumns("ColumnName").DataBodyRange
.ClearContents
.Cells(1).FormulaArray = "=YourArrayFormula"
End With 'ActiveSheet.ListObjects("TableName").ListColumns("ColumnName").DataBodyRange
|
|
Tweet |
There actually is a way to add an array formula to a table column without using a defined name. It's just kind of weird.
ReplyDeleteSub addArrayFormulaToTblCol()
Dim colNum As Long
Dim colName As String
Dim formulaText As String
Dim tbl As ListObject
Set tbl = Sheet1.ListObjects("YourTable")
formulaText = "=YourArrayFormula"
colName = "Column1"
colNum = Application.WorksheetFunction.Match(colName, tbl.HeaderRowRange, 0)
With tbl.Range.Columns(colNum).Cells(2)
.FormulaArray = formulaText
.AutoFill Destination:=tbl.ListColumns(colInt).DataBodyRange
End With
End Sub
Oops, switch out "colInt" for "colNum" in the AutoFill line.
DeleteHi Daniel, thanks for your reaction.
DeleteYes, you are right. I always tried to insert an array formula into entire column at once and it caused an error. But if I insert it into a single cell only (as you proposed), it works. For sure I cleared the column before pasting. Then I don't have to use autofill.
Hi Karel,
DeleteYour new version (using Cells(1) of the DataBodyRange instead of Cells(2) of the column range) is much cleaner, nice work! I still might prefer using AutoFill instead of ClearContents, though. There's a chance the user might have disabled the "automatically autofill tables" feature.
Hi Daniel
DeleteYes, it is possible. In this case, it's better to test this setting before running autofill. Something like:
If Not Application.AutoCorrect.AutoFillFormulasInLists Then
.Cells(1).AutoFill Destination:=.Cells
End If