Using the Microsoft Excel Macro Recorder. In Episode #1244, Bill shows you an example of how to record an Excel Macro using Relative References.
...This is the video podcast companion to the book, "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
...This is the video podcast companion to the book, "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Excel in Depth, chapter 27.
Macro Recording.
Hey! Welcome back to the MrExcel netcast.
Chapter 27, is all about VBA Macros and one, no!
Bad decision, the Microsoft made in the Macro recorder.
My classic example here, someone sent me this data set.
This data set was supposed to be used for a mail merge and the person who set it up, didn't understand how it's supposed to be.
Name in Column A, address in Column B.
City, state, zip, in Column C and I get this data.
I'd offer to help with the mail merge and now I realize...
Fix all of these names.
So, that I said simple off, we'll just record a macro.
Macro would be called Fix One.
Shortcut key of [ ctrl + a ], stored in this workbook, great!
Macro's recording now.
So I'm want to go down, cut the first name.
Go up, paste.
Cut this cell and paste in Column C.
Delete those blank rooms and make sure that I'm on the next name.
All right! So, simple it up.
But, here's the problem with the default state of the macro recorder.
The default state of the macro recorder is so, literal.
It does exactly what I do,.
So, when I would press [ ctrl + a ], again.
It would go to cell A2, cut cell A2, watch us paste to B1.
Oh! That's bad.
Go down to A3, cut.
Go up, to see on paste and then delete Rows 2, 3 and 4.
That's what the Macros gonna do So, here let me do it all. I'll press [ ctrl + a ].
Ruined that record.
Ruined that record.
Ruined that record.
I just hold down [ ctrl + a ], I can now destroy data faster, than ever before.
Not necessarily, a good thing.
Alright! Now, here's you know. Here's the thing.
That's the default state of the macro recorder.
The funny thing is there's a setting that lets us fix that.
Why Microsoft doesn't turn this setting on by default, I have no idea.
Use relative reference, turn that on.
Its both there on the view tab and back here on the developer tab, you see it has nice glow, behind it.
Now I'm going to record that macro, again.
So, I'll call it Fix-One-Try-2.
Now, lets do [ ctrl + s ].
Click [ OK ].
Alright! Now, that relative is on.
It doesn't say, go to cell A2.
It says, Oh! From wherever you started, go down one.
So, cut.
Go up, and paste, left down two.
Cut, up two, over to paste.
To leave the next three rows and then go up a cell, down a cell, to make sure that we're on the next name.
Stop recording. [ ctrl + s ] [ ctrl + s ] Works.
I just hold down, [ ctrl + s ] and it's going to fix all my records.
Okay! So.
I think most people have a lot of problem with the excel macro record because they don't understand relative reference.
Microsoft would have been more better served to term relative reference, on by default.
Okay! Now, that I've turned it on.
It will stay on for the rest of the day or until I close excel.
But, tomorrow I open excel again, back to turned off.
They would make that one change, have relative turned on all the time.
Most people have much better experience.
I want to thank you for stopping by.
Will see you next time for another netcast, from MrExcel.
Excel in Depth, chapter 27.
Macro Recording.
Hey! Welcome back to the MrExcel netcast.
Chapter 27, is all about VBA Macros and one, no!
Bad decision, the Microsoft made in the Macro recorder.
My classic example here, someone sent me this data set.
This data set was supposed to be used for a mail merge and the person who set it up, didn't understand how it's supposed to be.
Name in Column A, address in Column B.
City, state, zip, in Column C and I get this data.
I'd offer to help with the mail merge and now I realize...
Fix all of these names.
So, that I said simple off, we'll just record a macro.
Macro would be called Fix One.
Shortcut key of [ ctrl + a ], stored in this workbook, great!
Macro's recording now.
So I'm want to go down, cut the first name.
Go up, paste.
Cut this cell and paste in Column C.
Delete those blank rooms and make sure that I'm on the next name.
All right! So, simple it up.
But, here's the problem with the default state of the macro recorder.
The default state of the macro recorder is so, literal.
It does exactly what I do,.
So, when I would press [ ctrl + a ], again.
It would go to cell A2, cut cell A2, watch us paste to B1.
Oh! That's bad.
Go down to A3, cut.
Go up, to see on paste and then delete Rows 2, 3 and 4.
That's what the Macros gonna do So, here let me do it all. I'll press [ ctrl + a ].
Ruined that record.
Ruined that record.
Ruined that record.
I just hold down [ ctrl + a ], I can now destroy data faster, than ever before.
Not necessarily, a good thing.
Alright! Now, here's you know. Here's the thing.
That's the default state of the macro recorder.
The funny thing is there's a setting that lets us fix that.
Why Microsoft doesn't turn this setting on by default, I have no idea.
Use relative reference, turn that on.
Its both there on the view tab and back here on the developer tab, you see it has nice glow, behind it.
Now I'm going to record that macro, again.
So, I'll call it Fix-One-Try-2.
Now, lets do [ ctrl + s ].
Click [ OK ].
Alright! Now, that relative is on.
It doesn't say, go to cell A2.
It says, Oh! From wherever you started, go down one.
So, cut.
Go up, and paste, left down two.
Cut, up two, over to paste.
To leave the next three rows and then go up a cell, down a cell, to make sure that we're on the next name.
Stop recording. [ ctrl + s ] [ ctrl + s ] Works.
I just hold down, [ ctrl + s ] and it's going to fix all my records.
Okay! So.
I think most people have a lot of problem with the excel macro record because they don't understand relative reference.
Microsoft would have been more better served to term relative reference, on by default.
Okay! Now, that I've turned it on.
It will stay on for the rest of the day or until I close excel.
But, tomorrow I open excel again, back to turned off.
They would make that one change, have relative turned on all the time.
Most people have much better experience.
I want to thank you for stopping by.
Will see you next time for another netcast, from MrExcel.
