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!
A dynamic array formula is entered in one cell and completed with a regular Enter keystroke. To complete an old-fashioned array formula, you need to press Ctrl + Shift + Enter. New array formulas spill to many cells automatically. CSE formulas must be copied to a range of cells to return multiple results.
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:
Implicit intersection logic reduces many values to a single value. Excel did this to force a formula to return a single value, since a cell could only contain a single value. If your formula was returning a single value, then implicit intersection did nothing (even though it was technically being done in the background).
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With