Fixing SSN's with a macro

I'm trying to automate the fixing of SSN's from numbers to text. I have a
maco which will do it, but I'm trying automate it so it will do evrry cell
in a range. I calculate the len() of the ssn cell and then sort by length to
get all the len(7) and len(8) rows to the top. The macro below works if I
take all the "FOR EACH and NEXT" looping stuff out and run it on a cell,
then move to the next cell and execute it again. Problem is sometimes our
data files have 100+ rows with dorked ssn's. Gotta be an easy way to do this
right? I'm an old mainframe programmer trying to handle on this new stuff.

Sub Add2()
'
' Add2 Macro
' Adds 2 zeros to ssn
'
' Keyboard Shortcut: Ctrl+t
'
Dim junk As String

For Each Cell In Selection
If Len(ActiveCell.Value) = 7 Then
junk = "'00" & ActiveCell.Value
Else
junk = "'0" & ActiveCell.Value

End If
ActiveCell.Value = junk
Next Cell

End Sub



Thanks,

Bruce

This is one of those many instances where one is better off leveraging
the power of XL (and using VBA to automate that).

To convert a 7-9 character ssn in say cell A1 into a 9 character ssn use
the XL formula =REPT("0",9-LEN(A1))&A1. Copy this formula as far down
as there are data in col. A. Next, copy the contents of this column and
edit | paste special... | values into the original column. Finally,
delete the new column.

You could actually use XL's macro recorder to record the above as you go
through the steps using the GUI. To clean up the XL generated code, see
Beyond Excel's recorder
http://www.tushar-
mehta.com/excel/vba/beyond_the_macro_recorder/index.htm

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

In article <eG7sb70cFHA.2288@TK2MSFTNGP14.phx.gbl>, bmartin@asmr.com
says...
> I'm trying to automate the fixing of SSN's from numbers to text. I have a
> maco which will do it, but I'm trying automate it so it will do evrry cell
> in a range. I calculate the len() of the ssn cell and then sort by length to
> get all the len(7) and len(8) rows to the top. The macro below works if I
> take all the "FOR EACH and NEXT" looping stuff out and run it on a cell,
> then move to the next cell and execute it again. Problem is sometimes our
> data files have 100+ rows with dorked ssn's. Gotta be an easy way to do this
> right? I'm an old mainframe programmer trying to handle on this new stuff.
>
> Sub Add2()
> '
> ' Add2 Macro
> ' Adds 2 zeros to ssn
> '
> ' Keyboard Shortcut: Ctrl+t
> '
> Dim junk As String
>
> For Each Cell In Selection
> If Len(ActiveCell.Value) = 7 Then
> junk = "'00" & ActiveCell.Value
> Else
> junk = "'0" & ActiveCell.Value
>
> End If
> ActiveCell.Value = junk
> Next Cell
>
> End Sub
>
>
>
> Thanks,
>
> Bruce
>
>
>

>Gotta be an easy way to do this right?

maybe just use a formula:

=TEXT(A1,"000-00-0000")

or

=TEXT(A1,"000000000")


You can use them in VBA code if you want

Application.Worksheetfunctions.Text ....

You don't need to sort it first, with that, either.

Bruce,

I'm not an expert at VBA, but I beleive if you substitute Cell.Value for
every Activecell.Value your code will work. "Cell" in your code is a variable
(is could have been called almost anything) that is used to select each item
in the collection "Selection". The For Each Loop processes each item in the
collection on each repetition to the loop using the varaible Cell (or
whatever name you might want to call it). I beleive that the Activecell
remains the first cell of your selection and does not change during the For
Each Loop.

Bob
"Bruce Martin" wrote:

> I'm trying to automate the fixing of SSN's from numbers to text. I have a
> maco which will do it, but I'm trying automate it so it will do evrry cell
> in a range. I calculate the len() of the ssn cell and then sort by length to
> get all the len(7) and len(8) rows to the top. The macro below works if I
> take all the "FOR EACH and NEXT" looping stuff out and run it on a cell,
> then move to the next cell and execute it again. Problem is sometimes our
> data files have 100+ rows with dorked ssn's. Gotta be an easy way to do this
> right? I'm an old mainframe programmer trying to handle on this new stuff.
>
> Sub Add2()
> '
> ' Add2 Macro
> ' Adds 2 zeros to ssn
> '
> ' Keyboard Shortcut: Ctrl+t
> '
> Dim junk As String
>
> For Each Cell In Selection
> If Len(ActiveCell.Value) = 7 Then
> junk = "'00" & ActiveCell.Value
> Else
> junk = "'0" & ActiveCell.Value
>
> End If
> ActiveCell.Value = junk
> Next Cell
>
> End Sub
>
>
>
> Thanks,
>
> Bruce
>
>
>

Does it have to be formatted as text? If you just want it displayed
w/leading zeros, you could click Format/Cells/Special/SS#
or Format/Cells/Custom and input "000000000"

I had some trouble w/your code if my cells were not already formatted as
text so I put an extra line in the macro to change the numberformat. This
macro also does not require the list to be sorted prior to running.

Dim Cell As Range

For Each Cell In Intersect(Selection, Selection.Parent.UsedRange)
If Not IsEmpty(Cell) Then
Cell.NumberFormat = "@"
Cell.Value = Format(Cell.Value, "000000000")
End If
Next Cell

End Sub

"Bruce Martin" wrote:

> I'm trying to automate the fixing of SSN's from numbers to text. I have a
> maco which will do it, but I'm trying automate it so it will do evrry cell
> in a range. I calculate the len() of the ssn cell and then sort by length to
> get all the len(7) and len(8) rows to the top. The macro below works if I
> take all the "FOR EACH and NEXT" looping stuff out and run it on a cell,
> then move to the next cell and execute it again. Problem is sometimes our
> data files have 100+ rows with dorked ssn's. Gotta be an easy way to do this
> right? I'm an old mainframe programmer trying to handle on this new stuff.
>
> Sub Add2()
> '
> ' Add2 Macro
> ' Adds 2 zeros to ssn
> '
> ' Keyboard Shortcut: Ctrl+t
> '
> Dim junk As String
>
> For Each Cell In Selection
> If Len(ActiveCell.Value) = 7 Then
> junk = "'00" & ActiveCell.Value
> Else
> junk = "'0" & ActiveCell.Value
>
> End If
> ActiveCell.Value = junk
> Next Cell
>
> End Sub
>
>
>
> Thanks,
>
> Bruce
>
>
>