MrExcel's Learn Excel #810 - Recording Moving

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Jan 13, 2009.
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!
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top