When recording a macro to enter a SUM formula, you might need to write a formula that freezes one half of the reference for the summed range. Episode 627 shows you why.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Want to talk about recording macros, today.
We have a simple little data set here.
I want to record a macro, that will go down to the end of the data set and add a total formula.
So, I go to tools, macro, record new macro.
Assign to a shortcut key of [ ctrl + T ], click [ ok ].
All right! Now, let's look at the stop recording, tool bar.
There's two buttons there, the first one obviously is stop recording.
The second button is the important one, that's the relative reference.
You want to make sure that, that's turned on.
So, it has the little square around it, that will allow your macros to work more often.
So, I'm gonna hit the [ end ] key and then the [ down arrow ] key to move to the last row in the data set and then one more [ down arrow ], to go down to the total row, will type the word total there.
And I'll come over to where my totals are going to be.
I'm going to select all three cells and hit the [ auto-sum ] button and sure enough, we get some totals.
I'll stop recording.
Now, this worked out great.
Let's say tomorrow, I could have a new data set.
I'll go on to the second sheet here, and I run my macro [ ctrl + T ] and sure enough it puts the totals in but, hey...
There's a huge problem, and if you're not careful you're about to print this report and never notice the problem.
Here's what happened because on the first day.
I happen to have 13 rows, on [ enter.
that [ autos ] button, it only recorded a total for the last 13 rows and today now, I'm missing data.
We have a little green indicator there, usually these green indicators drive me insane but today.
It's telling me that the formula omits adjacent cells.
That's my clue that I have a problem.
Well, to solve this we go back to the original data set.
When we record the macro what we want to do is.
Tools, Macro, Record new Macro.
Hit the [ end ] key, [ down arrow ] key, type the word total and here at this point, where we normally hit the [ auto-sum ], rather than hitting the [ auto-sum ].
I'm actually going type of formula equals SUM and say that I wanted to start from E, dollar sign, 2.
Now, I realize never in the world would you actually type E, dollar sign, 2 in life.
But that's the trick to make it work in the Macro recorder.
Going to E 3 and the E13 is just relative.
When we record the macro that way.
We'll stop recording, you'll see that next day no matter how many rows of data we have, it will work perfectly.
So, there we go [ ctrl + G ], when we look at the formula, now.
It went back all the way up to Row 2 down to the row just above.
So, you when you're recording macros, a couple of things.
First of all make sure to hit the relative reference button.
Second of all, think about the formulas that you'd typed.
But if you always needed to start in row 2.
Make sure to put the dollar sign in.
Hey! Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Want to talk about recording macros, today.
We have a simple little data set here.
I want to record a macro, that will go down to the end of the data set and add a total formula.
So, I go to tools, macro, record new macro.
Assign to a shortcut key of [ ctrl + T ], click [ ok ].
All right! Now, let's look at the stop recording, tool bar.
There's two buttons there, the first one obviously is stop recording.
The second button is the important one, that's the relative reference.
You want to make sure that, that's turned on.
So, it has the little square around it, that will allow your macros to work more often.
So, I'm gonna hit the [ end ] key and then the [ down arrow ] key to move to the last row in the data set and then one more [ down arrow ], to go down to the total row, will type the word total there.
And I'll come over to where my totals are going to be.
I'm going to select all three cells and hit the [ auto-sum ] button and sure enough, we get some totals.
I'll stop recording.
Now, this worked out great.
Let's say tomorrow, I could have a new data set.
I'll go on to the second sheet here, and I run my macro [ ctrl + T ] and sure enough it puts the totals in but, hey...
There's a huge problem, and if you're not careful you're about to print this report and never notice the problem.
Here's what happened because on the first day.
I happen to have 13 rows, on [ enter.
that [ autos ] button, it only recorded a total for the last 13 rows and today now, I'm missing data.
We have a little green indicator there, usually these green indicators drive me insane but today.
It's telling me that the formula omits adjacent cells.
That's my clue that I have a problem.
Well, to solve this we go back to the original data set.
When we record the macro what we want to do is.
Tools, Macro, Record new Macro.
Hit the [ end ] key, [ down arrow ] key, type the word total and here at this point, where we normally hit the [ auto-sum ], rather than hitting the [ auto-sum ].
I'm actually going type of formula equals SUM and say that I wanted to start from E, dollar sign, 2.
Now, I realize never in the world would you actually type E, dollar sign, 2 in life.
But that's the trick to make it work in the Macro recorder.
Going to E 3 and the E13 is just relative.
When we record the macro that way.
We'll stop recording, you'll see that next day no matter how many rows of data we have, it will work perfectly.
So, there we go [ ctrl + G ], when we look at the formula, now.
It went back all the way up to Row 2 down to the row just above.
So, you when you're recording macros, a couple of things.
First of all make sure to hit the relative reference button.
Second of all, think about the formulas that you'd typed.
But if you always needed to start in row 2.
Make sure to put the dollar sign in.
Hey! Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
