The Relative Macro

[DanP]

I know how to create a relative macro...to a point. I know that when I start
recording the macro, I have to pick the RELATIVE button in the little toolbar.
I will tell you what I want to accomplish and then what happens.

1. I have a cell (A1) that has a city, state, & zip code (Pittsburgh, PA
15212).
2. Cell B1 is empty and already formatted for text.
3. I place the cursor in cell A1 and start to record the macro.
4. I press the relative button.
5. I double click in A1, hold the shift key down and use the arrow keys to
highlight the zip code and the space in front of it. (6 arrow keystrokes to
the left)
6. When highlighted, I press Ctrl-X to cut the space and zip code and copy
it to the clipboard.
7. I hit <Enter> and the cursor moves down to the next cell. (A2)
8. I use the arrow keys to move the cursor back up and to the right cell (B1).
9. I press Ctrl-V to paste the zip code into B1 and hit <Enter> which moves
the cursor down to B2.
10. I use the arrow keys to move the cursor over to cell A2.
11. I then stop recording the macro.

You can see that I want to strip the zip codes out of one cell and put it
into another.
Here's is what happens. The macro runs great as far as the movement and the
cutting of the zip codes. But, when it moves to the next cell, it always
pastes the same number...the one that was originally cut and copied in the
original cell.

What am I doing wrong? Does it have something to do with the relative vs
actual?

Please help,

 

[Sheeloo]

If you just want the ZIP code then the formula in B1 (and copied down)
=RIGHT(A1,6)
will give you that...

If you really want a macro solution then pl. paste what you are getting so
that we can see why it is doing what it is doing...

 

[Shane Devenshire]

Hi,

You are using a macro when you don't need one. Take a look at the Data,
Text to Columns command. This will allow you to split the data in A1 into as
many columns as you want and keep or not keep those columns.

Why don't you post a sample of you data and explain what you want as a
result. Disregard the macro discussion.

 

[Shane Devenshire]

Sorry, I forgot to mention that you can record the Data, Text to Columns
after you learn how to use it. One command will work against one cell or an
entire column of data.

 

[Gord Dibben]

You cannot record what you do while in Edit Mode so the editing in steps 5
and 6 is not being recorded.


Gord Dibben MS Excel MVP

 

[DanP]

It didn't help. It left the zip code still in A1. I need to delete it from
A1 and put it in B1. But, I can tell you this, it solved something else for
me down the road.

I believe, the response that Shane Devenshire sent me about using the Text
to Column will work fine for what I need to do.

Thank you much. I really apreciate it.

 

[DanP]

Thank you much. That's exacly what I needed. Took a little bit of time to
fully understand the Text to Column but I got it. Thanks again.