using vba to enter a unique function formula

Occasional Contributor

i can enter a simple formula in a cell and it works fine



if i use vba to enter the formula, it does not spill and only A1 gets filled

range("A1").Formula = "=unique(items[item])"


it has the @ sign in the formula, which isn't supposed to affect anything.

but, if i edit the cell and remove the @ sign, the results spill correctly.


can vba be used to enter a formula like this?

or, can the results be stored in an array and then written to the spreadsheet?

i can put a single column of unique values into an array, but not into a multi-dimensional array when there are multiple columns.

this works using a table named Items with columns item and description:

Dim arr As Variant

arr = worksheetfunction.unique([Items[item]])

this does not

arr = WorksheetFunction.Unique([Items[item]:[description]])






2 Replies
best response confirmed by g_keramidas (Occasional Contributor)


The Formula property cannot handle dynamic array formulas. Use Formula2 instead:


Range("A1").Formula2 = "=unique(items[item])"

ok, hans. thank you very much