Record an Excel macro using relative references
Transcript of the video:
MrExcel podcast is sponsored by Excel-XL.
Excel in-depth Chapter 27; Macro Recording.
Hey welcome back to the MrExcel netcast.
Chapter 27 is all about VBA, Macros and one you know bad decision, the Microsoft made in the Macro Recorder.
My classic example here is 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 A Column, 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 realized I'll fix all of these Names, so that I said simple up, we'll just Record a Macro.
Macro be called Fix One and a shortcut key of Ctrl+A stored in this workbook.
Great, Macros is recording now, so I'm gonna go down, cut the first Name, go up paste.
Cut this C's team it, paste in column C.
Delete those blank rows 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 literally, does exactly what I do.
So when I would press Ctrl+A again, it would go to cell A2, cuts cell A2 that paste to be 1.
Oh that's bad! go down to A3, cut crop to C1, paste and then delete rows 2 3 & 4.
That's where the Macros gonna do. So here let me do it.
I'll press Ctrl+A, ruin that record...
If I just hold down Ctrl+A, I can now destroy data faster than ever before.
not necessarily a good thing.
All right, now here's the thing, that 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, both there on the View tab and back here on the Developer tab.
It has a nice globe behind it.
Now I'm gonna record that Macro again, so I'll call it Fix One Try2.
Let's do Ctrl+S, okay, all right 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 over and paste, left down to cut up 2 over 2, paste, delete the next three rows and then go up and settle down, so to make sure that we're on the next Name.
Stop recording, Ctrl+S.
I... Ctrl+S works, works, works. I just hope that Ctrl+S gonna fix all my records.
Okay so I think most people have a lot of problem with the Excel Macro Recorder because they don't understand Relative Reference.
Microsoft would have been far better served to turn Relative Reference on by default.
Okay now, that I've turned 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 that relative turned on all the time.
Most people have much better experience...
Hey, I wanna thank you for stopping by.
We'll 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 you know bad decision, the Microsoft made in the Macro Recorder.
My classic example here is 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 A Column, 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 realized I'll fix all of these Names, so that I said simple up, we'll just Record a Macro.
Macro be called Fix One and a shortcut key of Ctrl+A stored in this workbook.
Great, Macros is recording now, so I'm gonna go down, cut the first Name, go up paste.
Cut this C's team it, paste in column C.
Delete those blank rows 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 literally, does exactly what I do.
So when I would press Ctrl+A again, it would go to cell A2, cuts cell A2 that paste to be 1.
Oh that's bad! go down to A3, cut crop to C1, paste and then delete rows 2 3 & 4.
That's where the Macros gonna do. So here let me do it.
I'll press Ctrl+A, ruin that record...
If I just hold down Ctrl+A, I can now destroy data faster than ever before.
not necessarily a good thing.
All right, now here's the thing, that 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, both there on the View tab and back here on the Developer tab.
It has a nice globe behind it.
Now I'm gonna record that Macro again, so I'll call it Fix One Try2.
Let's do Ctrl+S, okay, all right 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 over and paste, left down to cut up 2 over 2, paste, delete the next three rows and then go up and settle down, so to make sure that we're on the next Name.
Stop recording, Ctrl+S.
I... Ctrl+S works, works, works. I just hope that Ctrl+S gonna fix all my records.
Okay so I think most people have a lot of problem with the Excel Macro Recorder because they don't understand Relative Reference.
Microsoft would have been far better served to turn Relative Reference on by default.
Okay now, that I've turned 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 that relative turned on all the time.
Most people have much better experience...
Hey, I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
