I am adding a formula to a worksheet via VBA which should be:
This utilises the new SPILL feature in Excel to give me a list of column B values where the related value in column A matches what is in cell A. I’m also applying the UNIQUE function to remove any multiple blank (“”) results.
This works perfectly if I manually type the formula into Excel, however in using VBA to add the formula, Excel is adding @ symbols within the formula, and causing it to show #VALUE!.
The VBA line being used to add the formula is:
=Cells(x,y).Formula = "=UNIQUE(IF(TableA[ColumnA]=A1,TableA[ColumnB],""""))"
The resulting output in Excel is:
What is going on, and what have I missed?
Thanks in advance!