May 05 2021 05:39 PM
I am writing a formula in a cell in vba.
the formula is ws.Range("A1").Formula = "=UNIQUE('Sheet1'!E:E)"
The new "improved" excel is inserting an @ in front of my formula so...
In cell A1 I see =@ UNIQUE('Sheet1'!E:E)
(due to this message boards use of the @ symbol I had to add a space to show you what it looked like. The @ is between the = and the word UNIQUE with no spaces)
This @ forces unique to display only 1 cell (not the list I am expecting)
Does anyone know how to stop Excel from adding this "Implicit intersection operator" into my formula using VBA?
This is what excel is telling me about the @ character it added
"The implicit intersection operator was introduced as part of substantial upgrade to Excel's formula language to support dynamic arrays. Dynamic arrays bring significant new calculation ability and functionality to Excel."
May 05 2021 07:26 PM
SolutionI figured it out. If I use Formula2R1C1 then Excel does not put the @ in.
I am so use to using Formula I didn't want to start using Formula2R1C1, but now I will.
So now the formula is ws.Range("A1").Formula2R1C1 = "=UNIQUE('Sheet1'!C5:C5)"
and it works.
No need to reply. This has been solved. Thanks.
May 05 2021 08:47 PM
May 06 2021 08:02 AM
Feb 25 2024 02:10 PM
@mroegiers Thank You, this solve my problem when I code on VB.NET
May 05 2021 07:26 PM
SolutionI figured it out. If I use Formula2R1C1 then Excel does not put the @ in.
I am so use to using Formula I didn't want to start using Formula2R1C1, but now I will.
So now the formula is ws.Range("A1").Formula2R1C1 = "=UNIQUE('Sheet1'!C5:C5)"
and it works.
No need to reply. This has been solved. Thanks.