Excel VBA Adding Zero in Number String

Question

im only new to vba so i dont really know much about programming, anyway, i created a macro that adds a zero before a single digit string number in my selection, it only works if my selections are single digits only but doesn't work when theres a double digits or more in my selection, this is my code:

Sub AddZero()
 Dim cell As Range
 
 For Each cell In Selection
  If Not IsNumeric(cell) = False And cell > 10 Then
   Exit Sub
    Else:
   cell = "0" & cell
  End If
 Next
End Sub

How can i make my macro skip all cells with double digits and only target single digits, pls help and thanks very much in advance.

user_1140579 · Answer

How can i make my macro skip all cells with double digits and only target single digits

No need for a Loop. You can achieve what you are doing in 1 line

Selection.NumberFormat = "00"

Also avoid the use of Selection. Try something like this

Sub AddZero()
    Dim rng As Range
    
    '~~> Change this to the relevant sheet/range
    Set rng = ThisWorkbook.Sheets("Sheet1").Range("A1:A10")
    
    rng.NumberFormat = "00"
End Sub

BEFORE

AFTER

Excel VBA Adding Zero in Number String

1 Answers