Forum Discussion
Solved
Implicit intersection operator: @
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 ...
I 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.
I 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.
- One last thing. Formula2R1C1 ends up making "=Unique('Sheet1'!$E:$E)" which was okay for my current issue. However, as I wanted it to work exactly like Formula used to I have discovered I can use Formula2 and it gives me exactly what formula used to give me. "=Unique('Sheet1'!E:E)" without the @. So, for me, it is Formula2 from now on.
- I think the key sentence from the VBA object model help is "In Dynamic Arrays enabled Excel, Range.Formula2 supersedes Range.Formula". Personally, I would get rid of the practice of direct cell referencing from VBA at the same time, but that is just my approach to increasing robustness.
