Store account numbers as text I wrote this code to make sure account numbers (accounting), which are numbers, are stored as text in the range. Replacing r.value by r.text does not seem to work on whole ranges, so I need to loop. Any better idea?

Sub num2text(r As Range, Optional keepFormat As Boolean)
'change numbers to text, optionally leaving the numberFormat unchanged
    Dim oldFormat
    Dim c As Range
    For Each c In r
        If IsNumeric(c) Then
            If keepFormat Then oldFormat = c.NumberFormat   'save num fmt
            c.NumberFormat = "@"
            c.Value = c.Text
            If keepFormat Then c.NumberFormat = oldFormat   'restore num fmt
        End If
    Next c
End Sub

Sub num2text_test()
    num2text Range("b10:b100")
End Sub
I updated the code to include @TinMan's remarks.
I also added a safety net by intersecting the range in parameter with the UsedRange to avoid scanning 1 million empty cells if a full column is provided as a parameter.  

The most effective change is probably using .SpecialCells(xlCellTypeConstants, xlNumbers): I tried on about 100.000 rows in various configurations:
- all numbers: makes little difference
- just a few numbers: goes from 18 sec to 0.02 sec!  

Finally I turned of recalc during the macro.

Sub num2text(rng As Range, Optional keepFormat As Boolean)
'change numbers to text, optionally leaving the numberFormat unchanged
    Dim oldFormat As String, t
    Dim c As Range, i As Long, r As Range
    On Error GoTo finito    'in case selection contains no numeric constants
    Set r = Intersect(rng, rng.Parent.UsedRange).SpecialCells(xlCellTypeConstants, xlNumbers)
    On Error GoTo 0
    Application.Calculation = xlCalculationManual
    For Each c In r
        If keepFormat Then oldFormat = c.NumberFormat   'save num fmt
        t = c.Text
        c.NumberFormat = "@"
        c.Value = t
        If keepFormat Then c.NumberFormat = oldFormat   'restore num fmt
    Next c
    Application.Calculation = xlCalculationAutomatic
finito:
End Sub

Sub selection2text()
    num2text Selection
End Sub