Today, a closer look at the macro from Episode 811. While it LOOKED like it worked, the recorded macro leaves a lot to be desired. Your totals will be wrong if you rely on the AutoSum button during macro recording. Episode 812 shows you the workaround.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Well, yesterday, we recorded this macro, and I was pretty happy because I used relative references and I got the totals in the right spot.
However, take a look at these totals, this little green triangle down here in the lower left-hand corner, that green triangle.
Usually, we ignore that, right, but it's telling us there's a huge problem.
This FORMULA OMITS ADJACENT CELLS.
Let me press F2 and you'll see that it is not the total.
Well, it only got the last 13 cells, and why?
Well, yesterday, when I recorded the macro, that particular data set only had 13 rows, and even though I had relative references turned on, the AUTOSUM button does not get recorded correctly by Microsoft.
How frustrating is that?
Well, is there a solution to this?
Is there some way to write a formula that's going to say, look, we always want to go from the row right above us all the way back to row 2 every time?
And we could write a formula that would say =SUM of F$2 to, in this case, F17.
The $ sign is saying, hey, we always want to start at row 2.
Now, we would never actually type this in real life, but that's the key to get the macro recorder to actually record the right code.
[ =SUM(F$2:F17) ] So, what are we going to do?
We're going to close this without saving.
FILE, CLOSE.
Save changes.
NO.
I'll reopen it.
Alright.
We'll record a new macro again.
So, I’ll RECORD MACRO, call it ADDTOTALS, CONTROL+SHIFT+T, store it in THIS WORKBOOK, click OK.
Now, pay attention.
The relative reference button remembers its last setting.
So, it's currently turned off, so I'm going to turn that back on.
Now, the first thing we need to do is make sure we get to cell A1, press CONTROL+G, and do A1.
That way, in case someone happened to click another cell before they run the macro.
Just like yesterday, we can END DOWN or CONTROL+DOWNARROW to get the last row, one more to get to the total, and then head over here to column E.
Now, this is where I used the AUTOSUM button and got into trouble yesterday.
So, the macro recorder doesn't do well with the AUTOSUM button.
We're actually going to type a formula here.
=SUM of E$2 down to E13 today.
I'll press CONTROL+ENTER to enter that formula, and stay in the current cell, and then use the fill handle to copy it to the right.
[ =SUM(E$2:E13) ] Okay, now, at this point, I want to move back to cell A1 -- actually, back to row A1.
I'm going to turn off relative reference to do that and select these cells from A1 to G1, and I can format those, CONROL+B maybe, and then format, column, autofit to make everything fit, and then finally select cell A1.
Okay, well, we're done recording, and we need to press the STOP RECORDING button, and we'll go on and test now on Tuesday.
CONTROL+SHIFT+T. Alright.
The total showed up in the right spot, that's a good sign, and then, when we check, the totals actually worked for all of our data.
On Wednesday, I have even fewer records.
We’ll do CONTROL+SHIFT+T again, and, sure enough, check to make sure that the totals are grabbing just the right rows.
Well, there you have it.
The macro recorder, it can actually work once you master using the relative reference button, and then a few small tricks -- like the AUTOSUM button tends not to work but, if you write your formula carefully, you can work your way around that.
Well, I want to thank you for stopping by and we'll see you next time for another netcast from MrExcel.
Well, yesterday, we recorded this macro, and I was pretty happy because I used relative references and I got the totals in the right spot.
However, take a look at these totals, this little green triangle down here in the lower left-hand corner, that green triangle.
Usually, we ignore that, right, but it's telling us there's a huge problem.
This FORMULA OMITS ADJACENT CELLS.
Let me press F2 and you'll see that it is not the total.
Well, it only got the last 13 cells, and why?
Well, yesterday, when I recorded the macro, that particular data set only had 13 rows, and even though I had relative references turned on, the AUTOSUM button does not get recorded correctly by Microsoft.
How frustrating is that?
Well, is there a solution to this?
Is there some way to write a formula that's going to say, look, we always want to go from the row right above us all the way back to row 2 every time?
And we could write a formula that would say =SUM of F$2 to, in this case, F17.
The $ sign is saying, hey, we always want to start at row 2.
Now, we would never actually type this in real life, but that's the key to get the macro recorder to actually record the right code.
[ =SUM(F$2:F17) ] So, what are we going to do?
We're going to close this without saving.
FILE, CLOSE.
Save changes.
NO.
I'll reopen it.
Alright.
We'll record a new macro again.
So, I’ll RECORD MACRO, call it ADDTOTALS, CONTROL+SHIFT+T, store it in THIS WORKBOOK, click OK.
Now, pay attention.
The relative reference button remembers its last setting.
So, it's currently turned off, so I'm going to turn that back on.
Now, the first thing we need to do is make sure we get to cell A1, press CONTROL+G, and do A1.
That way, in case someone happened to click another cell before they run the macro.
Just like yesterday, we can END DOWN or CONTROL+DOWNARROW to get the last row, one more to get to the total, and then head over here to column E.
Now, this is where I used the AUTOSUM button and got into trouble yesterday.
So, the macro recorder doesn't do well with the AUTOSUM button.
We're actually going to type a formula here.
=SUM of E$2 down to E13 today.
I'll press CONTROL+ENTER to enter that formula, and stay in the current cell, and then use the fill handle to copy it to the right.
[ =SUM(E$2:E13) ] Okay, now, at this point, I want to move back to cell A1 -- actually, back to row A1.
I'm going to turn off relative reference to do that and select these cells from A1 to G1, and I can format those, CONROL+B maybe, and then format, column, autofit to make everything fit, and then finally select cell A1.
Okay, well, we're done recording, and we need to press the STOP RECORDING button, and we'll go on and test now on Tuesday.
CONTROL+SHIFT+T. Alright.
The total showed up in the right spot, that's a good sign, and then, when we check, the totals actually worked for all of our data.
On Wednesday, I have even fewer records.
We’ll do CONTROL+SHIFT+T again, and, sure enough, check to make sure that the totals are grabbing just the right rows.
Well, there you have it.
The macro recorder, it can actually work once you master using the relative reference button, and then a few small tricks -- like the AUTOSUM button tends not to work but, if you write your formula carefully, you can work your way around that.
Well, I want to thank you for stopping by and we'll see you next time for another netcast from MrExcel.
