I build a string using the filter function to assign to cell.formulaR1C1 but excel inserts an @ sign after the = and before FILTER, (=@Filter) This prevents to spill.
I can leave VBA code and manually edit out the @sign but that defeates the purpose of the VBA code.
Under certain conditions I achieve the spill, under others I do not.
Objective: Recategorize groups based on new group assignments
Overview: The filter function allows me to redistribute items under new groups
Issue: Excel inserts an @ sign before FILTER in certain conditions, preventing desired array
Step 1: Write the group names into an array using filter
While the WorksheetFunction.Filter does not work, I can write a formula to a cell and read the resulting array into an array
Issue: The cell formula to produce this array is =FILTER(A2:A121,A2:A121<>"","")
Assigning that string cell.formula results in =@FILTER(A2:A121,A2:A121<>"","") and no array
Assigning the equivalent string in R1C1 reference to cell.formulaR1C1 work
Step 2: Loop through the category array filtering the items based on the group assignments
Issue: Excel inserts and @ sign before FILTER in both cell.formula and cell.formulaR1C1
Stepping though the code in the attached workbook will demonstrate how the first use of the filter function is successful but how the next attempt results in Excel placing the @ sign into the formulaR1C1 string.