Mark asks about how to replicate the AutoSum concept in a macro. The code in Episode 844 gets closer to the AutoSum functionality than the solution that I proposed in Episode 812.
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:
Welcome back to the MrExcel netcast, I'm Bill Jelen.
Let's revisit Episode 812.
Back in that Episode, I talked about how the macro recorder cannot record the AutoSum, so if we go to Tools, Macro, Record New Macro, if I turn on relative reference and press the AutoSum button, basically what it did is, instead of the the concept of AutoSum, it basically just wrote a SUM function.
So now, if I run that macro again here, you see that because the original AutoSum grabbed the last five cells, even though Relative is turned on, the Macro Recorder is always going to record the same number of cells-- last five-- even though here we have seven.
So, Mark sent in a note, he said, "You know, hey, the way the AutoSum is supposed to work is, basically, it starts at the cell right above and it goes up until it encounters a blank." So, for example, if this was a blank cell and we press the AutoSum, it should be smart enough to figure out how many cells are above until a blank is encountered.
And so the solution, unfortunately, can't use the Macro Recorder here, but it is just a single line of code.
Let me break it down so we can actually see it in a couple of lines here that'll fit in the podcast screen.
Basically, you know, we started at the ActiveCell.Offset.
Offset says, "Hey, we're going to go from the active cell number, number of rows up, down, some number of columns, left or right," and so I said, "Go up one cell".
From the active cell, that gets us to that first number right above the active cell.
And then, also, again did Activecell.Offset, but press the End key and the Up Arrow key, and I built a Range from those two cells-- from the cell right above the active cell up until we get to a blank-- and ask for the address here.
So that'll say, you know, maybe E2 to E17 or something like that.
Build a little formula-- =SUM, open parentheses, use the concatenation character to concatenate that address, and then a closing parenthesis (=SUM(" & Range(ActiveCell.Offset(-1, 0))-- and finally, assign that whole big long formula to ActiveCell.formula.
Now, what I did here was, I wrote that Macro and decided to do Ctrl+T. So if I press Ctrl+T here, you'll see that it does successfully figure out how many rows are above us until we get to a space.
We'll try a few more cells here-- go down below, Ctrl+T. And so it is possible, it is possible to write a Macro that will replicate what AutoSum does.
Unfortunately, you're not going to be able to do it with the Macro Recorder-- at least I haven't figured out how to do it with the Macro Recorder.
Now, someone will probably write in and let me know how to do that, but basically, it's one line of code-- one very long line of code-- will do what you needed to do in order to get the AutoSum functionality to appear in a Macro.
Well, there you have it, a great question from Mark.
I want to thank him for sending that in.
Basically whatever I did back in Episode 812 was good for that one specific data set where we always start in a row too, but that's not how the AutoSum works.
The autosum generally goes up until it encounters either a blank cell or a text cell.
Now, this Macro here won't handle the text cells but it will handle the blank cells.
So, if you're trying to replicate that bit of functionality, this will do it for you.
Well, thanks for stopping by, i'll see you next time for another netcast from MrExcel.
[ music ]
Let's revisit Episode 812.
Back in that Episode, I talked about how the macro recorder cannot record the AutoSum, so if we go to Tools, Macro, Record New Macro, if I turn on relative reference and press the AutoSum button, basically what it did is, instead of the the concept of AutoSum, it basically just wrote a SUM function.
So now, if I run that macro again here, you see that because the original AutoSum grabbed the last five cells, even though Relative is turned on, the Macro Recorder is always going to record the same number of cells-- last five-- even though here we have seven.
So, Mark sent in a note, he said, "You know, hey, the way the AutoSum is supposed to work is, basically, it starts at the cell right above and it goes up until it encounters a blank." So, for example, if this was a blank cell and we press the AutoSum, it should be smart enough to figure out how many cells are above until a blank is encountered.
And so the solution, unfortunately, can't use the Macro Recorder here, but it is just a single line of code.
Let me break it down so we can actually see it in a couple of lines here that'll fit in the podcast screen.
Basically, you know, we started at the ActiveCell.Offset.
Offset says, "Hey, we're going to go from the active cell number, number of rows up, down, some number of columns, left or right," and so I said, "Go up one cell".
From the active cell, that gets us to that first number right above the active cell.
And then, also, again did Activecell.Offset, but press the End key and the Up Arrow key, and I built a Range from those two cells-- from the cell right above the active cell up until we get to a blank-- and ask for the address here.
So that'll say, you know, maybe E2 to E17 or something like that.
Build a little formula-- =SUM, open parentheses, use the concatenation character to concatenate that address, and then a closing parenthesis (=SUM(" & Range(ActiveCell.Offset(-1, 0))-- and finally, assign that whole big long formula to ActiveCell.formula.
Now, what I did here was, I wrote that Macro and decided to do Ctrl+T. So if I press Ctrl+T here, you'll see that it does successfully figure out how many rows are above us until we get to a space.
We'll try a few more cells here-- go down below, Ctrl+T. And so it is possible, it is possible to write a Macro that will replicate what AutoSum does.
Unfortunately, you're not going to be able to do it with the Macro Recorder-- at least I haven't figured out how to do it with the Macro Recorder.
Now, someone will probably write in and let me know how to do that, but basically, it's one line of code-- one very long line of code-- will do what you needed to do in order to get the AutoSum functionality to appear in a Macro.
Well, there you have it, a great question from Mark.
I want to thank him for sending that in.
Basically whatever I did back in Episode 812 was good for that one specific data set where we always start in a row too, but that's not how the AutoSum works.
The autosum generally goes up until it encounters either a blank cell or a text cell.
Now, this Macro here won't handle the text cells but it will handle the blank cells.
So, if you're trying to replicate that bit of functionality, this will do it for you.
Well, thanks for stopping by, i'll see you next time for another netcast from MrExcel.
[ music ]
