While yesterday's macro showed a simple formatting macro, it had a limitation that you can not move to a new cell. That is a fairly severe limitation. In Episode 810, we take a look at why moving the cell pointer causes a macro to fail and the simple setting to allow the macros to work.
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. Well, we've been talking about the macro recorder.
Yesterday, I showed how the macro recorder worked perfectly.
Now, today though, we have a slightly more complicated situation and really most of the macros are you record fit this pattern in that we have to move the cell pointer in order to record the macro.
I have data here someone sent me.
They said, hey, we want to do a mail merge.
We have Excel data.
We want to do mail merge in Word, and the data came in completely the wrong format.
I need name in column A, address in column B, city, state, zip in column C, and when this data showed up, I’m like, you know, you said you had data in Excel but I assumed it was formatted correctly.
Well, I figure I could write a quick macro to fix one record, and just run the macro over and over and over.
So, I say that I want to record a macro, and I’m just going to call it FIXONE.
SHORTCUT KEY of CONTROL+A. Now, I know CONTROL+A is already used but it's just easy to hit, and I'm only going to do this for today.
Where am I going to store it?
I'm going to store it in THIS WORKBOOK because, you know, it's not a common problem.
It's not something I have to do on every workbook.
This is specialized to this workbook.
So, I go through it.
I fix one record.
I go down to A2, I cut, go up, and paste to B1.
I go down to A3, I cut, and I paste to C1.
I delete those next 3 rows and make sure that I'm on the next name so that way I can run the macro again, and then, of course, we press the STOP RECORDING button to stop recording.
Well, here's the problem.
The macro recorder is so literal that it literally recorded, hey, every time we run this macro, we want to go to cell A2, and we're going to take that data, we're going to cut it and paste it to B1, and then we're going to go to cell A3 and cut that data and paste it to C1, and then we're going to delete rows 2, 3, and 4.
Well, that's horrible.
That doesn't work at all.
In fact, that macro basically only works for the first record, and, by the time we've recorded the macro, the first record’s already fixed.
So, this is the least useful macro in the history of the world.
In fact, I can destroy this whole data set.
So, right now, you know, we basically have used the macro recorder, you know, to solve a problem that is not very impressive at all.
I'll close this.
Save changes.
No.
Okay.
So, we reopen the data set.
Here's the trick to make the macro recorder work.
We're going to do all the steps before, RECORD MACRO, FIXONE, SHORTCUT KEY of CONTROL+A, store it in THIS WORKBOOK, click OKAY.
Here's the trick -- this button called USE RELATIVE REFERENCE.
USE RELATIVE REFERENCE.
By turning that on, we get a completely different macro recorder that will actually record the relative action.
So, what the macro recorder is recording now is, hey, go down one cell from where you started, cut, go up one cell and over one cell, and paste.
Go down 2 cells, cut, up and over 2, and paste.
Delete the next 3 rows and then select that cell.
So, now, we've recorded a macro by pressing CONTROL+A. CONTROL+A. CONTROL+A. CONTROL+A.
It's working perfectly, and I can just hold down CONTROL+A and fix all of these records in an instant.
That works most of the time.
In fact, if I were running Microsoft, I would change the tool tip to say, press this button to make your macros work 99% of the time instead of 1% of the time.
I would also add the editorial comment, wouldn't it have been nice if we would have turned this on by default, but that's just me.
For right now, they're calling it USE RELATIVE REFERENCE.
Well, hey, next week, we're going to take a look at a few other macros that we record, and just a few tweaks that you can pay attention to while you're recording macros to make them work more often than not.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Yesterday, I showed how the macro recorder worked perfectly.
Now, today though, we have a slightly more complicated situation and really most of the macros are you record fit this pattern in that we have to move the cell pointer in order to record the macro.
I have data here someone sent me.
They said, hey, we want to do a mail merge.
We have Excel data.
We want to do mail merge in Word, and the data came in completely the wrong format.
I need name in column A, address in column B, city, state, zip in column C, and when this data showed up, I’m like, you know, you said you had data in Excel but I assumed it was formatted correctly.
Well, I figure I could write a quick macro to fix one record, and just run the macro over and over and over.
So, I say that I want to record a macro, and I’m just going to call it FIXONE.
SHORTCUT KEY of CONTROL+A. Now, I know CONTROL+A is already used but it's just easy to hit, and I'm only going to do this for today.
Where am I going to store it?
I'm going to store it in THIS WORKBOOK because, you know, it's not a common problem.
It's not something I have to do on every workbook.
This is specialized to this workbook.
So, I go through it.
I fix one record.
I go down to A2, I cut, go up, and paste to B1.
I go down to A3, I cut, and I paste to C1.
I delete those next 3 rows and make sure that I'm on the next name so that way I can run the macro again, and then, of course, we press the STOP RECORDING button to stop recording.
Well, here's the problem.
The macro recorder is so literal that it literally recorded, hey, every time we run this macro, we want to go to cell A2, and we're going to take that data, we're going to cut it and paste it to B1, and then we're going to go to cell A3 and cut that data and paste it to C1, and then we're going to delete rows 2, 3, and 4.
Well, that's horrible.
That doesn't work at all.
In fact, that macro basically only works for the first record, and, by the time we've recorded the macro, the first record’s already fixed.
So, this is the least useful macro in the history of the world.
In fact, I can destroy this whole data set.
So, right now, you know, we basically have used the macro recorder, you know, to solve a problem that is not very impressive at all.
I'll close this.
Save changes.
No.
Okay.
So, we reopen the data set.
Here's the trick to make the macro recorder work.
We're going to do all the steps before, RECORD MACRO, FIXONE, SHORTCUT KEY of CONTROL+A, store it in THIS WORKBOOK, click OKAY.
Here's the trick -- this button called USE RELATIVE REFERENCE.
USE RELATIVE REFERENCE.
By turning that on, we get a completely different macro recorder that will actually record the relative action.
So, what the macro recorder is recording now is, hey, go down one cell from where you started, cut, go up one cell and over one cell, and paste.
Go down 2 cells, cut, up and over 2, and paste.
Delete the next 3 rows and then select that cell.
So, now, we've recorded a macro by pressing CONTROL+A. CONTROL+A. CONTROL+A. CONTROL+A.
It's working perfectly, and I can just hold down CONTROL+A and fix all of these records in an instant.
That works most of the time.
In fact, if I were running Microsoft, I would change the tool tip to say, press this button to make your macros work 99% of the time instead of 1% of the time.
I would also add the editorial comment, wouldn't it have been nice if we would have turned this on by default, but that's just me.
For right now, they're calling it USE RELATIVE REFERENCE.
Well, hey, next week, we're going to take a look at a few other macros that we record, and just a few tweaks that you can pay attention to while you're recording macros to make them work more often than not.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
