Non Regular Sequential Numbering

[Jab331]

Hi All,

I have information submitted using a user form which when submitted populated certain columns within a worksheet. Each time a new batch of information is submitted I need to assign it a unique number, "ME0001", "ME0002", "ME0003" and so forth. Is there some code in VBA that would allow me to achieve this?

The numbers would need to start in C7 at "ME0001" and then increase by 1 for each new row added.

Thanks,

Jack

[paulked]

Private Sub CommandButton2_Click()
     'Copy inut values to sheet.
    Dim lRow As Long, BdyTxt As String
    Dim ws As Worksheet
    Set ws = Worksheets("ME Jobs")
    BdyTxt = " - New job added to the register."
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
        .Cells(lRow, 1).Value = Me.TextBox1.Value
        .Cells(lRow, 3).Value = "ME" & Format(lRow - 6, "0000") '<<<<<<<<
        .Cells(lRow, 4).Value = Me.ComboBox1.Value
        .Cells(lRow, 5).Value = Me.TextBox3.Value
        .Cells(lRow, 6).Value = Me.TextBox4.Value
        .Cells(lRow, 7).Value = Me.ComboBox2.Value
    End With
     'Clear input controls.
    Me.TextBox1.Value = ""
    Me.TextBox2.Value = ""
    Me.ComboBox1.Value = ""
    Me.TextBox3.Value = ""
    Me.TextBox4.Value = ""
     'Add your variable strings here-------------------------
    If ComboBox2 = "BK117" Then
        BdyTxt = "Programme: BK117" & BdyTxt
    ElseIf ComboBox2 = "EC135" Then
        BdyTxt = "Programme: EC135" & BdyTxt
    End If
     
    Me.ComboBox2.Value = ""
    Call Email1("Jack.Burge@GKNAerospace.com", "New Job Added to ME Job Register", BdyTxt & _
    "  Please assign a suitable ME and priority to the new job.")
End Sub

Should do the trick!!

[Paul_Hossler]

Assuming that the ME's start in C7 and there are no blank cell until the last one

Capture.JPG


If the ME column is sorted, or a ME***x is deleted, this still returns the next higher ME.

It can be simplified if the ME's are always in order, ME001 to MExyx

Option Explicit

Function NextME()
    Dim v As Variant
    Dim i As Long
    
    v = Application.WorksheetFunction.Transpose(Range(Range("C7"), Range("C7").End(xlDown)).Value)
    For i = LBound(v) To UBound(v)
        v(i) = CLng(Right(v(i), Len(v(i)) - 2))
    Next I
    
    NextME = Format(Application.WorksheetFunction.Max(v) + 1, "\ME000")
End Function

[paulked]

    .Cells(lRow, 3).Value = "ME" & Format(lRow - 6, "0000") '<<<<<<<<

This will always give you the next sequential number after a sort too, as any new jobs added to the table will be added to the bottom.

[SamT]

A generic algorithm for incrementing any Alpha+Numerical string

La = Length of Alpha part on Alphanumeric value
Iv = Increment Value = 1
str = Value of Alpha part of number = "ME"
OldNumber = value of previous number Cell
NewNumber = The String Value to place in the next cell.

NewNumber = str & CStr(Clng(Mid(OldNumber, La + 1))+Iv)

Customized for you

NewNumber= "ME" & CStr(Clng(Mid(OldNumber, 3))+1)

Mid(OldNumber, La + 1) Returns a String of all to the right of "ME"
Clng(Mid(OldNumber, 3)) Converts string to a number
CStr(Clng(Mid(OldNumber, 3))+1 Adds one to the number and converts the result to a String
NewNumber= "ME" & CStr(etc... Concatenates "ME" and the numerical value String

[Jab331]

This has worked perfectly, thank you. I've removed the Job Number Text Box from the form and they are generated automatically now.