Part 1 of 2: Many times, you have to use a clever mix of relative and absolute recording to get the macro to perform certain tasks. The goal today is to navigate to the bottom of a data set, add totals, and then move back to row 1. Episode 811 will show you how to handle the relative button, but watch out, as episode 812 will reveal yet another 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.
Well, many times when we’re recording macros, sometimes we need to turn on relative reference, turn off relative reference as we're recording, and so I have a situation here today where I get an invoice file from our IT department.
I need to go through and format this, and, basically, I want to add totals.
Think about this.
The total is going to be on a different row every single time.
So, as we're recording this, we want to think about whether we want relative reference on or off, and, now, you notice I'm here in A1.
I'm going to assume that that's not always true.
I might start in some other cell.
So, I click the RECORD MACRO, we’ll call it FORMATINVOICE.
Notice you can't have any spaces in here so I just capitalize the I, and I'll do CONTROL+SHIFT+T for totals, store it in THIS WORKBOOK.
Okay.
Now, first thing we need to do is get to cell A1.
There's a couple of things I could do.
I could turn off relative references and click on A1, or I could use the GO TO button, so CONTROL+G, GO TO, and say I want to go to cell A1.
That gets recorded as absolute because we use the GO TO dialog box whether relative recording is on or off.
Okay.
How do we get down to the bottom of this data set?
Well, there's two ways --CONTROL+DOWNARROW or the END key and then the DOWN ARROW.
So, I'll press END and DOWN ARROW.
That gets me to the last row of data, and then one more DOWN ARROW will get me to the blank row.
Type the word TOTAL and then cruise over to the right here.
I'm going to select all 3 cells for PRODUCT REVENUE, SERVICE REVENUE, and PRODUCT COST, and then go to the formulas tab and hit the AUTOSUM button to add the totals in.
Now, at this point, I want to go back up and format row 1, so I might turn off relative reference.
Well, I click on A1, select all of the cells in A1, and then do format, column, autofit to make everything long enough for those headings.
At this point, I can stop recording.
Okay, now, I can't wait for tomorrow to try this data out.
So, we go onto Tuesday, go onto Tuesday.
Here, you'll notice we have more rows.
This time, when I run this macro, I'm really hoping that the macro recorder goes to row 18 instead of putting the totals in row 14.
So, let's do it.
CONTROL+SHIFT+T, and you'll see that we have our totals down in row 18, and it came back up to row 1 at the end.
Everything looks like it's working just fine.
However, we need to come back and we'll see why the AUTOSUM button does not work in the macro recorder.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, many times when we’re recording macros, sometimes we need to turn on relative reference, turn off relative reference as we're recording, and so I have a situation here today where I get an invoice file from our IT department.
I need to go through and format this, and, basically, I want to add totals.
Think about this.
The total is going to be on a different row every single time.
So, as we're recording this, we want to think about whether we want relative reference on or off, and, now, you notice I'm here in A1.
I'm going to assume that that's not always true.
I might start in some other cell.
So, I click the RECORD MACRO, we’ll call it FORMATINVOICE.
Notice you can't have any spaces in here so I just capitalize the I, and I'll do CONTROL+SHIFT+T for totals, store it in THIS WORKBOOK.
Okay.
Now, first thing we need to do is get to cell A1.
There's a couple of things I could do.
I could turn off relative references and click on A1, or I could use the GO TO button, so CONTROL+G, GO TO, and say I want to go to cell A1.
That gets recorded as absolute because we use the GO TO dialog box whether relative recording is on or off.
Okay.
How do we get down to the bottom of this data set?
Well, there's two ways --CONTROL+DOWNARROW or the END key and then the DOWN ARROW.
So, I'll press END and DOWN ARROW.
That gets me to the last row of data, and then one more DOWN ARROW will get me to the blank row.
Type the word TOTAL and then cruise over to the right here.
I'm going to select all 3 cells for PRODUCT REVENUE, SERVICE REVENUE, and PRODUCT COST, and then go to the formulas tab and hit the AUTOSUM button to add the totals in.
Now, at this point, I want to go back up and format row 1, so I might turn off relative reference.
Well, I click on A1, select all of the cells in A1, and then do format, column, autofit to make everything long enough for those headings.
At this point, I can stop recording.
Okay, now, I can't wait for tomorrow to try this data out.
So, we go onto Tuesday, go onto Tuesday.
Here, you'll notice we have more rows.
This time, when I run this macro, I'm really hoping that the macro recorder goes to row 18 instead of putting the totals in row 14.
So, let's do it.
CONTROL+SHIFT+T, and you'll see that we have our totals down in row 18, and it came back up to row 1 at the end.
Everything looks like it's working just fine.
However, we need to come back and we'll see why the AUTOSUM button does not work in the macro recorder.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
