Forum Discussion

MS85CB's avatar
MS85CB
Copper Contributor
Jul 21, 2022

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

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!

 

excel
Macros and VBA
  • MS85CB's avatar
    MS85CB
    Copper Contributor
    Jul 21, 2022
    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!

Resources