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

Not sure this is on topic here, but if you want help with a macro you're working on, it might be useful to include it in your question ;-) — halfer
– halfer, Commented Feb 20, 2013 at 23:47
Are you sure the VB.NET tag is correct? VB.NET != VB (or VBA). — Tim
– Tim, Commented Feb 20, 2013 at 23:53

glh · Accepted Answer · 2013-02-21 02:35:21Z

0

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.

edited Feb 21, 2013 at 2:35

answered Feb 21, 2013 at 2:20

glh

4,9824 gold badges26 silver badges42 bronze badges

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

2 Comments

Tariq Khalaf Over a year ago

thats awesome so beyond my capability! it even calculates it within the macro which i guess is what debug print is and using ITERATION well now got that i need to place it to where it puts those in a sheet which im gona play arround with it and post it up here

Tariq Khalaf Over a year ago

I had to post a new question since the rules of the site state to do so which is on this page stackoverflow.com/questions/14996655/… was trying to get it to work almost there

chris neilsen · Accepted Answer · 2013-02-21 05:51:01Z

0

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

edited Feb 21, 2013 at 5:51

answered Feb 21, 2013 at 4:54

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

2 Comments

Tariq Khalaf Over a year ago

thanks for your help with that but i need it to separate the number like the macro bellow

Tariq Khalaf Over a year ago

I had to post a new question since the rules of the site state to do so which is on this page stackoverflow.com/questions/14996655/…

Collectives™ on Stack Overflow

Changing VBA macro code to change number

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related