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

New Contributor



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!


1 Reply
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))"


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