Changing VBA macro code to change number

Question

What I'm trying to do is increment number and then loop subtract the first 2 digits by 1 and then increment the rest until the first 2 digits = 0

So I have M201001 would become m191002 and next would be m181003 until m011020

Answer 1

What you'll need to do is break up your value into 3 using left, mid and right functions.

Then do the looping as you require it. I'm trying it out myself so I'll update my answer once I've done so.

Added Code:

Sub Testing()

    Dim myIn As String
    myIn = "M201001"

    Dim myLeft As String
    Dim myMid As Integer, myRight As Integer, i As Integer
    Dim myOut As String
    myLeft = Left(myIn, 1)
    myMid = CInt(Mid(myIn, 2, 2))
    myRight = CInt(Right(myIn, 4))
    myOut = myLeft & Format(myMid, "00") & Format(myRight, "0000")
    i = 0

    Debug.Print "IN:        " & myIn
    Debug.Print "BROKEN UP: " & myOut

    Do Until myMid = -1
        Debug.Print "ITERATION " & Format(i, "00") & ": " & myLeft & Format(myMid, "00") & Format(myRight, "0000")

        myMid = myMid - 1
        myRight = myRight + 1
        myOut = myLeft & Format(myMid, "00") & Format(myRight, "0000")
        i = i + 1
    Loop

End Sub

If you need any explanation please ask. I will be happy to explain.

Answer 2

Try this

Function GetNextNumber(n As String) As Variant
    ' Validate input
    If Len(n) <> 7 Then
        GetNextNumber = xlErrNum
        Exit Function
    ElseIf Left$(n, 1) <> "M" Then
        GetNextNumber = CVErr(xlErrNum)
        Exit Function
    End If

    GetNextNumber = Left$(n, 1) & Format(val(Mid$(n, 2)) - 9999, "000000")

End Function

Test using this

Sub demo()
    Dim n As Variant

    n = "M201001"
    Debug.Print n
    Do
        n = GetNextNumber(CStr(n))
        If IsError(n) Then Exit Sub
        Debug.Print n
    Loop Until val(Mid$(n, 2)) <= 19999
End Sub