[SOLVED] VBA Increment string within For Loop

VBA Increment string within For Loop

I'm trying to break up a textbox string (serial number) and start with the initial textbox value then increment it by 1 within a For Loop. I cannot figure out how to start with the first string in the textbox THEN increment it up by 1 until the loop ends.

For i = j To countForms
        
        If incSerialCheck = True Then 'Checkbox on userform to increment entered serial number
                serialNum = serialNumber.Value
                lastThree = Right(serialNum, 3)
                LastThreeInt = CInt(lastThree)
                LastThreePlusOne = LastThreeInt + 1
                LastThreePlusOneStr = CStr(LastThreePlusOne)
                NewSerialNumStr = Left(serialNum, Len(serialNum) - 3) & LastThreePlusOneStr
        Else
            NewSerialNumStr = serialNumber.Value
        End If
Next i

Solution

Increment Trailing Digits

First run the code as is.
Familiarize yourself with the code by modifying the String serial number (SerialString) and the Boolean flag (IncrementSerial) as you see fit.
If the result is satisfactory, apply to your code. Either way, leave a comment.

Sub IncrementTrailingDigits()

    Const RIGHT_DIGITS As Long = 3
    
    ' Retrieve the values from the user form.
    Dim SerialString As String:
    'SerialString = serialNumber.Value ' more readable is e.g. 'txtSerialNum'
    SerialString = "ABC-123-EFG-001" ' or "123001"
    Dim IncrementSerial As Boolean:
    'IncrementSerial = incSerialCheck.Value ' more readable is e.g. 'chkSerialNum'
    IncrementSerial = True
    
    ' Determine the right digits format.
    Dim RDFormat As String: RDFormat = String(RIGHT_DIGITS, "0")
    
    ' Retrieve the left part of the serial string.
    Dim SerialLeft As String:
    SerialLeft = Left(SerialString, Len(SerialString) - RIGHT_DIGITS)
    
    ' Loop.
    
    Dim i As Long, DigitString As String, IsFirstSerialFound As Boolean
    
    For i = 1 To 5
        If IncrementSerial Then
            If IsFirstSerialFound Then ' it's not the first (the flag is set)
                ' Retrieve right (trailing) digits.
                DigitString = Right(SerialString, RIGHT_DIGITS)
                ' Increment right digits.  
                DigitString = Format(CLng(DigitString) + 1, RDFormat)
                ' Rebuild serial number.
                SerialString = SerialLeft & DigitString
            Else ' it's the first; just set the flag (to 'True')
                IsFirstSerialFound = True
            End If
        'Else ' check box is 'False', do nothing
        End If
    
        MsgBox SerialString
       
    Next i

End Sub