In string search for number to determine next in sequence?

Question

I have a list of build numbers (e.g. R1079-AAA-001, ...-002 etc.) in which the value of "R1079" changes depending on the machine being used. What I want to do is search through the list to determine the last used build number (the last 3 digits) in relation to the specific machine I intend to use. I then need to add one and create a new log for the new build i.e. the last R1079 build was 056, therefore the new one is 057.

Currently the THEORY I have is an in string search for the machine number followed by a number search and store in the string and converted to integer. This is then added into a dynamic array and the maximum found when the loop is complete. One is added to this integer and the new name placed into a cell.

However, the code I have doesn't work so I assume I am missing things/got it all wrong.

Code below:

Sub test()

Dim x As String
Dim n As Integer
Dim i As Integer
Dim Machine_EBM As String
Dim retval As String
Dim retvalint As Integer
Dim LastBuild As Integer
Dim NextBuild As Integer
Dim myarr() As Integer

Machine = "R1079"

x = Cells("A1").Value 'get the first string in the list
    n = 1
    Do Until x = ""
        If InStr(x, Machine) > 0 Then 'search for machine in string
            For i = 6 To Len(Str) 'search for numbers at end of string
                If Mid(x, i, 1) >= "0" And Mid(x, i, 1) <= "9" Then
                    retval = retval + Mid(s, i, 1) 'store numbers
                End If
            Next i
            retvalint = CInt(retval) ' convert to integer
            ReDim Preserve myarr(n)
            myarr(n) = retvalint ' store integer value in array
            n = n + 1
        End If
    Loop

    LastBuild = Worksheet.Function.Max(myarr(n)) ' determine maximum array value
    NewBuild = LastBuild + 1 'add one to the value

    Range("C1").Select
    ActiveCell = Machine = "-AAA-" + NewBuild 'input new build number

End Sub

I am fairly new to VBA and self taught so I realise there may be a lot of errors here that I am missing. Any help is appreciated!

Thanks,

Charlie

Say, how store your build code in your excel sheet. I means, what range or what column. So, I can give more. — R.Katnaan, Aug 05 '15 at 08:33

R.Katnaan · Answer 1 · 2015-08-05T08:30:20.357

Here is a small piece of code for getting new build no for inputted build no.

I already tested the code. It give me right answer. So, you can use this code.

Public Sub getBuildNo()

    Dim machineCode, lastBuildCode, newBuildCode As String
    Dim buildNo As Integer

    'Set machine code
    machineCode = "R1079"

    'Set last build code
    lastBuildCode = Range("A1")

    'Get last build no
    buildNo = Right(lastBuildCode, 3)

    'Increase 1
    buildNo = buildNo + 1

    'Get new build No
    newBuildCode = machineCode & "-AAA-"

    'adding prefix 0s for getting like (001, 002, 025, etc.)
    If buildNo < 10 Then
        newBuildCode = newBuildCode & "00" & buildNo
    ElseIf buildNo < 100 Then
        newBuildCode = newBuildCode & "0" & buildNo
    Else
        newBuildCode = newBuildCode & buildNo
    End If

    'show new code
    Range("C1") = newBuildCode

End Sub

In string search for number to determine next in sequence?

1 Answers1