Formula Changes After a VBA Paste

[OldSwimmer1650]

The change is from $E$2 to $E$3 & $B$2 to $B$3

This formula changes from:
=IFERROR(SUM(FILTER('Garmin Data'!$E$2:$E$5000,ROUNDDOWN('Garmin Data'!$B$2:$B$5000,0)=D324)),"")

to

=IFERROR(SUM(FILTER('Garmin Data'!$E$3:$E$5000,ROUNDDOWN('Garmin Data'!$B$3:$B$5000,0)=D324)),"")
after the below code executes

Windows("Weight_Ex.xlsm").Activate
Sheets("Garmin Data").Select
Range("A2").EntireRow.Select
Selection.Insert Shift:=xlDown
Range("A1").Select
Application.CutCopyMode = False

I know the insert is causing the error, but I don't know how to fix it.

 

[6StringJazzer]

What cell is your formula in to start with, and what cell is it in after the insert?

 

[OldSwimmer1650]

What cell is your formula in to start with, and what cell is it in after the insert?

It's on a different tab, H361 before and still on H361 after the insert. Only the absolute reference is changed as stated above.

 

[OldSwimmer1650]

I guess it's a problem(to me), not an error.

 

[6StringJazzer]

Here is one option.

=IFERROR(SUM(FILTER(INDIRECT("'Garmin Data'!$E$2:$E$5000"),ROUNDDOWN(INDIRECT("'Garmin Data'!$B$2:$B$5000"),0)=D324)),"")

Here is another that should work as long as you are not inserting before row 1.

=IFERROR(SUM(FILTER(OFFSET('Garmin Data'!$E$1,1,0,4999),ROUNDDOWN(OFFSET('Garmin Data'!$B$1,1,0,4999),0)=D324)),"")

INDIRECT and OFFSET are volatile functions, which means Excel cannot tell in advance whether a particular change on the worksheet will affect the results, so it has to recalculate them every time there is any change. If you just have this formula once it's no problem, but if you have it thousands of times it could slow down calculation.

 

[OldSwimmer1650]

Here is one option.

=IFERROR(SUM(FILTER(INDIRECT("'Garmin Data'!$E$2:$E$5000"),ROUNDDOWN(INDIRECT("'Garmin Data'!$B$2:$B$5000"),0)=D324)),"")

Here is another that should work as long as you are not inserting before row 1.

=IFERROR(SUM(FILTER(OFFSET('Garmin Data'!$E$1,1,0,4999),ROUNDDOWN(OFFSET('Garmin Data'!$B$1,1,0,4999),0)=D324)),"")

INDIRECT and OFFSET are volatile functions, which means Excel cannot tell in advance whether a particular change on the worksheet will affect the results, so it has to recalculate them every time there is any change. If you just have this formula once it's no problem, but if you have it thousands of times it could slow down calculation.

Thank you very much. It's contributors like you that make this site work. I've learned something today. Thank you!