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!

Good question, and I looked it up...

_{In short:}Use

`=Cells(x,y).Formula2`

instead of`=Cells(x,y).Formula`

_{Explaination:}The

`@`

that shows is called the implicit intersection operator. From MS docs:But why does it appear in your newer Excel O365? Well,

`Range.Formula`

uses IIE (implicit intersection) thus adding the`@`

to basically undo your dynamic array functionality.`UNIQUE`

is a new dynamic array function. So, to write this out in code, you should use the`Range.Formula2`

property (or`Range.Formula2R1C1`

if you use`R1C1`

notation). These properties use AE (array evaluation) and is now the default.Here is an informative doc from MS on the subject which explains the difference between

`Formula`

and`Formula2`

in more detail.If you want to know more about the implicit intersection operator then have a look at this

I answered another question earlier on that involved implicit intersection with an example on how that actually works here if one finds it interesting.