VBA code auto-updated with @ symbol (Spill) and breaking the formula when macro is run.

[MS85CB]

Greetings,

I have a specific formula I want to enter into a cell:

Range("D1").Formula = "=SUM(IF(FREQUENCY(IF(B3:B" & lastrow & "<>"""",MATCH(B3:B" & lastrow & ",B3:B" & lastrow & ",0)),ROW(B3:B" & lastrow & ")-ROW(B3)+1),1))"

The goal of this formula is to count unique Order #s and display in cell D1.

After running the Macro the field comes back as "#Value". When I look at the formula it is updated to:

=SUM(IF(FREQUENCY(IF(@B3:B" & lastrow & "<>"""",MATCH(@B3:B" & lastrow & ",B3:B" & lastrow & ",0)),ROW(B3:B" & lastrow & ")-ROW(B3)+1),1))

If I manually remove the two @ symbols it returns the expected value.

If I can't turn off this new feature to automatically work with spills, how can i edit my VBA code to work correctly with it?

Thank you so much!

[#2]

I have what I think is a workaround for now, using "Formula2" instead of "Formula".

Range("D1").Formula2 = "=SUM(IF(FREQUENCY(IF(B3:B" & lastrow & "<>"""",MATCH(B3:B" & lastrow & ",B3:B" & lastrow & ",0)),ROW(B3:B" & lastrow & ")-ROW(B3)+1),1))"

https://docs.microsoft.com/en-us/office/vba/api/excel.range.formula2

I'd still like to hear from others if they think this is the right solution or if you recommend an alternative way.

Thanks!