SOLVED

using vba to enter a unique function formula

Occasional Contributor

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

=unique(items[item])

 

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

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

=@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]])

 

thanks

Gary

 

 

2 Replies
best response confirmed by g_keramidas (Occasional Contributor)
Solution

@g_keramidas 

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

 

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

ok, hans. thank you very much