Try the following macro:
Code:
Sub Demo()
Application.ScreenUpdating = False
Dim i As Long
With ActiveSheet.Cells
For i = 1 To 99
.Replace What:="0" & Format(i, "00"), Replacement:=Format(i, "00"), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next
End With
Application.ScreenUpdating = True
End Sub
Note: A significant drawback of what you're trying to do is that Excel will turn many of your strings into dates. If all you're concerned about is their appearance, that can be controlled by applying a suitable date format to the used range. Alternatively, you'll have to turn them all into text strings, a
much slower process (so slow a progress report on the status bar is needed), using code like:
Code:
Sub Demo()
Application.ScreenUpdating = False
Dim lRow As Long, lCol As Long
Dim r As Long, c As Long, i As Long
With ActiveSheet
With .UsedRange.Cells.SpecialCells(xlCellTypeLastCell)
lRow = .Row
lCol = .Column
End With
For r = 1 To lRow
Application.StatusBar = "Processing Row " & r
For c = 1 To lCol
If Left(.Cells(r, c).Text, 1) Like "[0-9]" Then .Cells(r, c).Value = "'" & .Cells(r, c).Text
Next
DoEvents
Next
For i = 1 To 99
.Cells.Replace What:="0" & Format(i, "00"), Replacement:=Format(i, "00"), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next
End With
Application.ScreenUpdating = True
End Sub