自动填充整型字符串系列

Sub AutoFillIntegerStringSeries()
    
    ' Reference the worksheet ('ws')
    ' in the workbook containing this code ('ThisWorkbook').
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Reference the range ('rg'). Its first cell holds
    ' the initial integer string.
    Dim rg As Range: Set rg = ws.Range("A1:A10")
    
    ' Store the number of rows in a variable ('rCount').
    Dim rCount As Long: rCount = rg.Rows.Count - 1 ' not counting the first cell
    ' Validate the number of rows.
    If rCount = 0 Then Exit Sub ' there is just one row containing the string

    ' Store the initial string in a variable ('CurrentString').
    Dim CurrentString As String: CurrentString = CStr(rg.Cells(1).Value)
    ' Using the 'IsDigits' function, validate the initial string.
    If Not IsDigits(CurrentString) Then Exit Sub ' not all characters are digits
    
    ' Define an array ('Data') to hold the resulting integer strings.
    Dim Data() As String: ReDim Data(1 To rCount, 1 To 1)
    
    ' Declare variables.
    Dim r As Long ' Current Array Row (Element)
    
    ' Loop through the rows of the array.
    For r = 1 To rCount
        ' Using the 'IncrementIntegerString' function on the current string,
        ' store the incremented integer string in the same variable.
        CurrentString = IncrementIntegerString(CurrentString)
        ' Write the incremented string to the current row of the array.
        Data(r, 1) = CurrentString
    Next r

    ' Write the values from the array to the range excluding the first cell.
    rg.Resize(rCount).Offset(1).Value = Data

End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns a boolean indicating whether all characters of a string
'               are digits.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function IsDigits(ByVal s As String) As Boolean
    If Len(s) > 0 Then IsDigits = s Like String(Len(s), "#")
End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns an integer string which is by one greater than
'               the given integer string ('IntegerString').
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function IncrementIntegerString(ByVal IntegerString As String) As String
    
    Dim iLen As Long: iLen = Len(IntegerString)
    
    Dim Digit As Long
    Dim n As Long
    
    For n = iLen To 1 Step -1
        Digit = CLng(Mid(IntegerString, n, 1))
        If Digit <> 9 Then Exit For
    Next n
    
    If n = 0 Then
        IncrementIntegerString = "1" & String(iLen, "0")
    Else
        IncrementIntegerString = Left(IntegerString, n - 1) _
            & CStr(Digit + 1) & String(iLen - n, "0")
    End If
    
End Function