Sep 27 2022 01:42 PM
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
Sep 27 2022 02:02 PM
SolutionThe Formula property cannot handle dynamic array formulas. Use Formula2 instead:
Range("A1").Formula2 = "=unique(items[item])"
Sep 30 2022 06:16 AM
Sep 27 2022 02:02 PM
SolutionThe Formula property cannot handle dynamic array formulas. Use Formula2 instead:
Range("A1").Formula2 = "=unique(items[item])"