Mark from New Hampshire notes that the macro recorder can not record the simple act of pressing the AutoSum button. In Episode 823, I show you the arcane workaround to solve the problem.
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.
I want to send a thanks to Scott Fox of scottfoxradio.com.
Last week, I spent about an hour with Scott talking about MrExcel and how we got started and why I give everything away for free.
Great little story about the early days of MrExcel.
So, just go to scottfoxradio.com if you're interested in listening to that.
Today, we have a question sent in by Mark from New Hampshire, and I love this question because it's one that's been driving me crazy forever.
Mark tries to record a macro using the AUTOSUM button.
So, let’s do TOOLS, MACRO, RECORD NEW MACRO, and we'll just SHORTCUT KEY of CONTROL+A, store it in THIS WORKBOOK.
Now, we're smart enough to turn on RELATIVE REFERENCE, and then we simply press the AUTOSUM button, and it comes up with a formula of adding up the 11 cells immediately above.
So, we'll press STOP, and now we try this macro again.
You would think that the macro recorded the action of pressing the AUTOSUM.
So, I run the macro, CONTROL+A, and I get the exact same answer.
When we look, the formula only went up 11 cells, just like it did in the original macro, and this is so frustrating that Microsoft cannot record the act of pressing the AUTOSUM button.
So, Mark said, is there any way to get around this, and it's completely unintuitive and something that we would never do in real life, but there is a way.
Think about the formula.
If we would put in a formula here, how would you tell the macro recorder, look, we want to go from the cell right above us but we always want to go back to cell, in this case, G1, always row 1.
No matter what, we want to go back to row G1.
Well, we would type G $ sign 1 to G11 -- no $ signs in that last piece, so there's only one $ sign there -- and when we use that with the macro recorder, it will get the right answer.
[ =SUM(G$1:G11) ] So, let's try it again.
TOOLS, MACRO, RECORD NEW MACRO, SHORTCUT KEY, I'll go CONTROL+S this time, and I'll type a formula.
=SUM G$1 to G11 -- only one $ sign -- CONTROL+ENTER, and now I'll stop recording.
Now, it’s a hassle to do that during the macro recording process instead of just hitting the AUTOSUM.
However, that basically teaches Excel to do what you would have thought the AUTOSUM would have been recorded as.
So, here's a little test.
We’ll do CONTROL+S. Sure enough, it got the whole situation, even if it had less numbers.
So, I'll put in a few less numbers and go here.
Now, if I would have run CONTROL+A here, I would have gotten an error because they would try and go up 11 rows, but I'll do CONTROL+S, the second macro, and it works just fine.
So, a huge, huge “got you” with the macro recorder.
It cannot do the AUTOSUM correctly.
Basically, you have to trick Excel into writing the correct code by typing the formula yourself and locking in that top row with a $ sign.
Well, I want to thank Mark for sending that question and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
I want to send a thanks to Scott Fox of scottfoxradio.com.
Last week, I spent about an hour with Scott talking about MrExcel and how we got started and why I give everything away for free.
Great little story about the early days of MrExcel.
So, just go to scottfoxradio.com if you're interested in listening to that.
Today, we have a question sent in by Mark from New Hampshire, and I love this question because it's one that's been driving me crazy forever.
Mark tries to record a macro using the AUTOSUM button.
So, let’s do TOOLS, MACRO, RECORD NEW MACRO, and we'll just SHORTCUT KEY of CONTROL+A, store it in THIS WORKBOOK.
Now, we're smart enough to turn on RELATIVE REFERENCE, and then we simply press the AUTOSUM button, and it comes up with a formula of adding up the 11 cells immediately above.
So, we'll press STOP, and now we try this macro again.
You would think that the macro recorded the action of pressing the AUTOSUM.
So, I run the macro, CONTROL+A, and I get the exact same answer.
When we look, the formula only went up 11 cells, just like it did in the original macro, and this is so frustrating that Microsoft cannot record the act of pressing the AUTOSUM button.
So, Mark said, is there any way to get around this, and it's completely unintuitive and something that we would never do in real life, but there is a way.
Think about the formula.
If we would put in a formula here, how would you tell the macro recorder, look, we want to go from the cell right above us but we always want to go back to cell, in this case, G1, always row 1.
No matter what, we want to go back to row G1.
Well, we would type G $ sign 1 to G11 -- no $ signs in that last piece, so there's only one $ sign there -- and when we use that with the macro recorder, it will get the right answer.
[ =SUM(G$1:G11) ] So, let's try it again.
TOOLS, MACRO, RECORD NEW MACRO, SHORTCUT KEY, I'll go CONTROL+S this time, and I'll type a formula.
=SUM G$1 to G11 -- only one $ sign -- CONTROL+ENTER, and now I'll stop recording.
Now, it’s a hassle to do that during the macro recording process instead of just hitting the AUTOSUM.
However, that basically teaches Excel to do what you would have thought the AUTOSUM would have been recorded as.
So, here's a little test.
We’ll do CONTROL+S. Sure enough, it got the whole situation, even if it had less numbers.
So, I'll put in a few less numbers and go here.
Now, if I would have run CONTROL+A here, I would have gotten an error because they would try and go up 11 rows, but I'll do CONTROL+S, the second macro, and it works just fine.
So, a huge, huge “got you” with the macro recorder.
It cannot do the AUTOSUM correctly.
Basically, you have to trick Excel into writing the correct code by typing the formula yourself and locking in that top row with a $ sign.
Well, I want to thank Mark for sending that question and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
