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.

Flip the logic and don't Exit Sub from the loop.If IsNumeric(cell) And cell < 10 Then cell.Value = "0" & cell.Value — Tim Williams
– Tim Williams, Commented Mar 26, 2021 at 4:13

Siddharth Rout · Accepted Answer · 2021-03-26 04:52:59Z

2

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

edited Mar 26, 2021 at 4:52

answered Mar 26, 2021 at 4:47

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Siddharth Rout Over a year ago

Dhanyavaad Tiwariji _/|\_ :)

Collectives™ on Stack Overflow

Excel VBA Adding Zero in Number String

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related