(Solved) – Excel VBA adding @ symbol to formula and table column reference

(solved)-–-excel-vba-adding-@-symbol-to-formula-and-table-column-reference

I am adding a formula to a worksheet via VBA which should be:

=UNIQUE(IF(TableA[ColumnA]=A1,TableA[ColumnB],""))

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:

=@UNIQUE(IF(TableA[@[ColumnA]]=A1,TableA[ColumnB],"")

What is going on, and what have I missed?

Thanks in advance!

Leave a Reply

Your email address will not be published. Required fields are marked *