autoFill column equal to adjacent column

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

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

Hi Paul,

In a new sheet assuming you take a list of numbers over and locate them 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

Art678

Appreciate the help