Implicit Intersection Operator: @

[dhancy]

Hello,

Our agency recently upgraded to Excel for Microsoft 365 MSO (version 2302).

In one of my VBA scripts, I have this formula:

Cells(s, 6) = "=sum(1/countif(F8:F" & s - 1 & ", F8:F" & s - 1 & "))"

When I run my script, I see this in Cell(s,6)

=SUM(1/COUNTIF(F8:F26,@ F8:F26))

But this gives me a result of #DIV/0

When I remove the @ symbol, I get the correct answer.

How can I prevent @ from showing up?


Thank you!


Dennis

 

[Fluff]

Sounds like you need to use Formula2 rather than Formula

 

[dhancy]

Thanks, but in my code, I do not use @.

I am not sure how it got into my spreadsheet. It seems to show up on my spreadsheet automatically.

 

[RoryA]

What Fluff is saying is to use Cells(s, 6).Formula2 = not just Cells(s, 6) =

 

[dhancy]

Oh! For as long as I've been writing VBA scripts, I did not know there was a "Formula2" option. This works!!

Thank you Fluff and RoryA!!!


Dennis

 

[Fluff]

For as long as I've been writing VBA scripts, I did not know there was a "Formula2" option.

There hasn't, it only came out with 365 & 2021 to cater for spill ranges.