Macro with relative address problem XSL 2000

[Dave McAuliffe]

Basically I want to (and did at one time) create a macro that edits a
number (zip code), inserts a leading zero then moves down one cell.

Cell Contents
A1 1234
A2 2345
A3 3456

With the cursor on A1 my steps are:

Tools\Macro\Record a New Macro\z
On Stop Recording Toolbar depress Relative Address, then
Edit
Home
'0
enter
Down
Click Stop recoding


Since the cursor is on a A2, Pressing CTRL+z will make the contents
01234, not the desired 02345.

I've tried different combinations of when to depress the relative
address button with no success. And double clicking the cell instead
of F2.

Any help giving me a solution or pointing to one appreciated.

--
Dave
Central Mass. USA

To email: Replace
mailinator.com with email.com

 

[Earl Kiosterud]

Dave,

You should look at what the macro recorder creates. Alt-F11 takes you to
the VBE. Double-click the module to open the code window and look at the
code. You'll see that the macro recorder doesn't record your commands -- it
records the results. It recorded the 01234, not the fact that you did
certain edit steps. It isn't a keystroke or command macro language.

Apparently you have numbers, not text, in the cells. They can be
permanently converted with a temporarily installed formula in a helper
column, if this is a one-time thing. Post back.

Or we can give you the code to do what you want if you still want to use the
macro solution, and have gotten into the VBE.

You can format the zip codes (if you're leaving them as numbers) for the
zeroes. Format - Cells - Number tab - Custom: 00000. This won't work if
you'll have 9-digit zip codes in the list -- You have to make them all text
in that case for a proper sort.

 

[Dave McAuliffe]

Dave,

You should look at what the macro recorder creates. Alt-F11 takes you to
the VBE. Double-click the module to open the code window and look at the
code. You'll see that the macro recorder doesn't record your commands -- it
records the results. It recorded the 01234, not the fact that you did
certain edit steps. It isn't a keystroke or command macro language.

I had seen the 01234 hard coded in and that is what I was trying to
get around. What gets me is, I thought I had done this in the past
simply through the record process as I don't write program code.

Apparently you have numbers, not text, in the cells. They can be
permanently converted with a temporarily installed formula in a helper
column, if this is a one-time thing. Post back.

Thanks for the heads up. The helper cell will work in the template
and be usable after a paste values step.

Or we can give you the code to do what you want if you still want to use the
macro solution, and have gotten into the VBE.

Thanks, but no. I haven't programmed (COBOL) for decades and that was
only an aside.

You can format the zip codes (if you're leaving them as numbers) for the
zeroes. Format - Cells - Number tab - Custom: 00000. This won't work if
you'll have 9-digit zip codes in the list -- You have to make them all text
in that case for a proper sort.

This is the solution I'll try and hang the sort outcome. It will be
interesting to see if the leading zero gets recognized by Word during
the mail merge process.


Thanks very much for your time and help, Earl.

--
Dave
Central Mass. USA

To email: Replace
mailinator.com with email.com

 

[Earl Kiosterud]

Dave,

I'll save you the step. Using cells formatted for number (00000) won't work
in a Word mail merge -- you won't get the leading zeroes. Using cells
pre-formatted (or typed with leading apostrophe) as text works, and is also
the only way to ensure that 9-digit zip codes sort correctly with 5-digit
ones.

 

[KL]

Hi guys,

There is a work-around for Word's MailMerge, which allows you to leave your
Excel values as numeric values since you can give custom format to a field
directly in Word:

1) Right-clic on your "Zip-Code" field on the Word document
2) Choose 'Toggle Field Code'
3) you will see something like this in your field:

{ MERGEFIELD ZIP-CODE }

edit the above for example as follows:

{ MERGEFIELD ZIP-CODE \# "00000" }

5) Right-clic on the field and choose 'Update Fields'


Regards,
KL

 

[Dave McAuliffe]

Much obliged to both of you for your help. I'll pass on the mail
merge work-around to the secretary (administrative assistant).


--
Dave
Central Mass. USA

To email: Replace
mailinator.com with email.com