Code appears to work in immediate window, but not as a whole

[suunto]

Hi all -

I've leveraged a code that applies a serial number to a list (unique number starting at 1 to whatever). The code as written didn't have any formatting, which I've attempted to add.

The code originally wrote serial numbers as such (1,2,3......9,10).

I want the serial numbers to be formatted like this (01,02,03....09,10), where the number of leading zeroes are dependent on the larges serial number inputted. That is, if 1000 is the largest serial number, the first item in the list would display as 0001.

My logic for achieving this was to use:

 ActiveCell.NumberFormat = WorksheetFunction.Rept(Arg1:="0", Arg2:=(t))

where t is based on the length of the original input, i. When I put each line of the code in the immediate window, it generally works. For example, if I put '100'' in the input box, the Len function returns 3. And if I ActiveCell.NumberFormat = WorksheetFunction.Rept(Arg1:="0", Arg2:=(3)), it will format the cell correctly. However, for some reason when I run the whole code, it sets the default format to "00" regardless of the input box value.


I am very beginner at this stuff, so if anyone could explain where I'm going wrong, I would really appreciate it. Code below.

Thanks,

suunto

Sub AddSerialNumbers()

Dim i As Integer
Dim t As Variant


On Error GoTo Last


i = InputBox("Enter Value", "Enter Serial Numbers")
t = Len(i)


For i = 1 To i


ActiveCell.Value = i


ActiveCell.NumberFormat = WorksheetFunction.Rept(Arg1:="0", Arg2:=(t))


ActiveCell.Offset(1, 0).Activate


Next i


Last: Exit Sub


End Sub

 

[rlv01]

It's a bit hard to understand where you want to write the values, but this will clean things up a bit.

Sub AddSerialNumbers2()

    Dim s As String
    Dim i As Integer
    Dim t As Variant

    s = Trim(InputBox("Enter Value", "Enter Serial Numbers"))

    If s = "" Then
        MsgBox "No data entered", vbInformation, "Warning"
        Exit Sub
    End If
    
    If Not IsNumeric(s) Then
        MsgBox "Must enter an integer number", vbExclamation, "Warning"
        Exit Sub
    End If
        
        i = CInt(s)
        t = Len(s)

        Application.ScreenUpdating = False
        For i = 1 To i
            ActiveCell.Value = i
            ActiveCell.NumberFormat = WorksheetFunction.Rept(Arg1:="0", Arg2:=(t))
            ActiveCell.Offset(1, 0).Activate
        Next i
        Application.ScreenUpdating = True
End Sub