When you are recording a macro, never use the AutoSum button. This video will show you the workaround to summing a column in a macro.
Transcript of the video:
MrExcel podcast is sponsored by “Easy-XL”!
Excel VBA chapter 1 - Adding Totals!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
While we're talking here in chapter 1 about using the macro recorder, already talked about relative references, they need to be turned on in most cases.
Now you can do that back here on the View tab, Macros, Use Relative References, or on the Developer tab, Use Relative References.
So you want to make sure that's turned on, has a nice glow behind it.
We’ll record a macro here that's going to attempt to add totals to the bottom of this report.
We’ll call it AddTotals1, Ctrl+Shift+T, there, click OK.
So the macro recorder is running now.
We're going to use navigation keys, so Ctrl+Down arrow to get down to the last row with data today, down arrow one more time, type the word Total.
And then go over and hit the Autosum button to add those totals in.
Alright, so that macro works fine, we got our answer, 6000.
Tomorrow you get another data set, let's run that macro again.
Do Alt+F8 click “Add Totals”, and because we use relative references, the Total appears in row 10 instead of in row 7, so that worked out great.
Alright, but I mean look at this, we have 8 rows of 1000, that's totally up to 6000, when we go look at that, we see that it gave us the wrong formula.
So here's the problem, apparently, the Excel team, big team, the guy who does the macro recorder, and the guy who does the Autosum.
They must not talk to each other, because the macro recorder can't record the essence of what the Autosum is doing.
So for a long time, at that point, we have to go write some code.
I was in New York, Pennsylvania about 2 or 3 years ago, and someone there, they said, “Wait, we figure out a solution to this”, and this is a great little tip here.
He said “All you have to do is change this formula back here by one character”, we change one character, and the macro recorder will get it right.
The character, it's right here between the B and the 2, put a single $. So that says “Hey, always reach back to row 2, but then go to the row just above where you are.” So, let's record the macro a different way.
So we'll come here, Developer, Record Macro, I'll call it AddTotalsTake2, we’ll go Ctrl+Shift+R, click OK.
Alright, so relative is still turned on because I haven’t closed Excel, Ctrl+Down arrow down here, one more time to type the word Total.
But now, instead of using the Autosum button, I'm going to actually type =SUM(B$2:B4), notice there's no dollar there before 4.
And press Enter, and press Stop.
Now here's the big test, on Thursday we have a different number of records.
We get up here, and we'll run our macro, Alt+F8, Take2, click Run, aright, and the total goes to the right place, and the formula goes back to the beginning, alright.
So a couple of rules as you're using the macro recorder.
First of all, Relative should be turned on all of the time, Microsoft should have turned that on by default, everyone will have a much better experience with the macro recorder.
#2, use the navigation keys to get down to the last row, so Ctrl+Down arrow will reliably get you the last row.
And then 3rd, never, ever, touch the Autosum button while you're recording macro, you're just going to have a disaster.
You should always find some other way to write the formula instead of using the Autosum button.
That’s our example here from Chapter 1.
I want to thank you for buying the book, and thank you for working along.
In many chapters there will be one example that we pull out, and say there’ll be a video there.
So the videos won't be covering the whole book, but just in certain places, adding a little bit of extra content, so you can come out and see this example performed live.
OK hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Excel VBA chapter 1 - Adding Totals!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
While we're talking here in chapter 1 about using the macro recorder, already talked about relative references, they need to be turned on in most cases.
Now you can do that back here on the View tab, Macros, Use Relative References, or on the Developer tab, Use Relative References.
So you want to make sure that's turned on, has a nice glow behind it.
We’ll record a macro here that's going to attempt to add totals to the bottom of this report.
We’ll call it AddTotals1, Ctrl+Shift+T, there, click OK.
So the macro recorder is running now.
We're going to use navigation keys, so Ctrl+Down arrow to get down to the last row with data today, down arrow one more time, type the word Total.
And then go over and hit the Autosum button to add those totals in.
Alright, so that macro works fine, we got our answer, 6000.
Tomorrow you get another data set, let's run that macro again.
Do Alt+F8 click “Add Totals”, and because we use relative references, the Total appears in row 10 instead of in row 7, so that worked out great.
Alright, but I mean look at this, we have 8 rows of 1000, that's totally up to 6000, when we go look at that, we see that it gave us the wrong formula.
So here's the problem, apparently, the Excel team, big team, the guy who does the macro recorder, and the guy who does the Autosum.
They must not talk to each other, because the macro recorder can't record the essence of what the Autosum is doing.
So for a long time, at that point, we have to go write some code.
I was in New York, Pennsylvania about 2 or 3 years ago, and someone there, they said, “Wait, we figure out a solution to this”, and this is a great little tip here.
He said “All you have to do is change this formula back here by one character”, we change one character, and the macro recorder will get it right.
The character, it's right here between the B and the 2, put a single $. So that says “Hey, always reach back to row 2, but then go to the row just above where you are.” So, let's record the macro a different way.
So we'll come here, Developer, Record Macro, I'll call it AddTotalsTake2, we’ll go Ctrl+Shift+R, click OK.
Alright, so relative is still turned on because I haven’t closed Excel, Ctrl+Down arrow down here, one more time to type the word Total.
But now, instead of using the Autosum button, I'm going to actually type =SUM(B$2:B4), notice there's no dollar there before 4.
And press Enter, and press Stop.
Now here's the big test, on Thursday we have a different number of records.
We get up here, and we'll run our macro, Alt+F8, Take2, click Run, aright, and the total goes to the right place, and the formula goes back to the beginning, alright.
So a couple of rules as you're using the macro recorder.
First of all, Relative should be turned on all of the time, Microsoft should have turned that on by default, everyone will have a much better experience with the macro recorder.
#2, use the navigation keys to get down to the last row, so Ctrl+Down arrow will reliably get you the last row.
And then 3rd, never, ever, touch the Autosum button while you're recording macro, you're just going to have a disaster.
You should always find some other way to write the formula instead of using the Autosum button.
That’s our example here from Chapter 1.
I want to thank you for buying the book, and thank you for working along.
In many chapters there will be one example that we pull out, and say there’ll be a video there.
So the videos won't be covering the whole book, but just in certain places, adding a little bit of extra content, so you can come out and see this example performed live.
OK hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
