autoFill column equal to adjacent column

[Paulg]

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

 

[Guest]

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]

Thanks much

 

[art678]

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

 

[Paulg]

Appreciate the help