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
>
>
>