I have a macro that takes a 9 digit number from one cell and converts i
into a zipcode + 4 number.

If the number is in A1 I set focus on B1 and =left(a1,5), then in C1
put the dash, (-) in d1 =right(A1,4) then I concatentate the 3 cells i
E1, and then autofill the columns down to the last row with the 9 digi
number.

My problem is that the incoming sheets can have rows fro 100 to 10,00
so in the Macro I have to set the autofill command to 10,005 to mak
sure I fill the sheet far enough. Of course, when I have a sheet o
100, I have to go back and delete all the dashes that filled to ro
10,005.

Is there a way to autofill the inserted columns to the same row as th
original column with the 9 digits in it???

Thanks

Pau

--
Paul
-----------------------------------------------------------------------
Paulg's Profile: http://www.excelforum.com/member.php...fo&userid=3607
View this thread: http://www.excelforum.com/showthread.php?threadid=56162

Paul,

Try this in your module...

Option Explicit
Dim cell As Range
Sub stripzip()
For Each cell In Range("A:A")
If Len(cell) = 9 And IsNumeric(cell) Then
Cells(cell.Row, 2) = Left(cell.Value, 5) 'Could Delete
Cells(cell.Row, 3) = "-" 'Could Delete
Cells(cell.Row, 4) = Right(cell.Value, 4) 'Could Delete
Cells(cell.Row, 5) = Left(cell.Value, 5) & "-" & Right(cell.Value, 4)
End If
Next cell
End Sub

This will do exactly as you specified. IMHO you can cut out the
intermediate steps (Marked with 'Could Delete) and save some run time.

Mike

"Paulg" wrote:


I have a macro that takes a 9 digit number from one cell and converts it
into a zipcode + 4 number.

If the number is in A1 I set focus on B1 and =left(a1,5), then in C1 I
put the dash, (-) in d1 =right(A1,4) then I concatentate the 3 cells in
E1, and then autofill the columns down to the last row with the 9 digit
number.

My problem is that the incoming sheets can have rows fro 100 to 10,000
so in the Macro I have to set the autofill command to 10,005 to make
sure I fill the sheet far enough. Of course, when I have a sheet of
100, I have to go back and delete all the dashes that filled to row
10,005.

Is there a way to autofill the inserted columns to the same row as the
original column with the 9 digits in it???

Thanks

Paul


--
Paulg
------------------------------------------------------------------------
Paulg's Profile: http://www.excelforum.com/member.php...o&userid=36077
View this thread: http://www.excelforum.com/showthread...hreadid=561623

Thanks much


--
Paulg
------------------------------------------------------------------------
Paulg's Profile: http://www.excelforum.com/member.php...o&userid=36077
View this thread: http://www.excelforum.com/showthread...hreadid=561623

Hi Paul,

In a new sheet assuming you take a list of numbers over and locate the
in Column A:

Sub zipa()
Range("A1", Range("A1").End(xlDown)).Copy Range("C1")
Range("C1", Range("C1").End(xlDown)).NumberFormat = "00000-0000"
End Sub

Art67

--
art67
-----------------------------------------------------------------------
art678's Profile: http://www.excelforum.com/member.php...fo&userid=3639
View this thread: http://www.excelforum.com/showthread.php?threadid=56162

Appreciate the help


--
Paulg
------------------------------------------------------------------------
Paulg's Profile: http://www.excelforum.com/member.php...o&userid=36077
View this thread: http://www.excelforum.com/showthread...hreadid=561623