VBA Auto generate numbering

[Kaniguan1969]

Hi ,

I have a userform with button to generate automatic ticket number.
Ticket number contains "L1"+yy+mm-999. I have already the codes but
my problem with my codes it did not add a number. thank you in advance.

my worksheet name as reference is "TransactionDB"
Column "A"

Here is my code:

Public Sub cmdnew_Click()
    Dim iRow As Long
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim yfmt As String, fmt As String, datefmt As String, TicketNum As String, sample As String
       
    yfmt = Format(Date, "yy")
    mfmt = Format(Date, "mm")
    datefmt = yfmt & mfmt
    
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("TransactionDB")
    Me.TextBox1.Enabled = True
    With ws
        my_sheet = ActiveSheet.Name
        If Trim(Me.TextBox1.Value) = "" Then
            'Generate Ticket Number
            Me.TextBox1.SetFocus
            MsgBox "Please enter Ticket Number; one will be suggested…", vbExclamation, "Ticket Number"
            
            TicketNum = Format(Application.Max(ws.Range("A:A")) + 1, "000")
            Me.TextBox1.Value = "L1" & datefmt & "-" & Format(TicketNum + 1, "000")
            Exit Sub
        End If
    End With
    Me.CmdNew.Enabled = True
    
End Sub

[Kaniguan1969]

I think this will not work because the max cater only the numbers but in my ticket number i have a char string. another option come into my mind is to
get the last row then i add plus 1? May i know on how to do this. thanks.

[Kaniguan1969]

Hi Expert,

May I ask your help on how to get the last row with data and get the cell value of column A. This is my other option to automate generating ticket number.
Please take a look also my existing codes if this is correct. thanks.

Here is my initial code.

Dim wb As Workbook      Dim ws As Worksheet
      Dim lRow As Long, lCol As Long
      Dim rng As Range
      Dim TextNum As String
      Dim yfmt As String, fmt As String, datefmt As String, TicketNum As Variant, sample As String
         
      yfmt = Format(Date, "yy")
      mfmt = Format(Date, "mm")
      datefmt = yfmt & mfmt
    
      '~~> Set this to the relevant worksheet
      Set wb = ThisWorkbook
      Set ws = wb.Sheets("TransactionDB")
    
      With ws
          '~~> Get the last row
          lRow = .Range("A" & .Rows.Count).End(xlUp).row
          '~~> get the first column
          lCol = .Cells(1, 1).End(xlToLeft).Column1

        '~~> Set the range
          Set rng = .Range(.Cells(lRow, 1), .Cells(lRow, lCol))
          
          If Not rng Is Nothing Then
             With rng
                Debug.Print .Address
                '?? how to get the cell value of last row 1st column
             End With
          Else
              Me.TextBox1.Value = "L1" & datefmt & "-"
           End If
      End With

[snb]

Sub M_snb()
    [A1:A2000] = [index("L1"&text(today(),"yymm")&text(row(A1:A2000),"000"),)]
End Sub

[SamT]

Public Sub cmdnew_Click()
    Dim iRow As Long
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim yfmt As String, fmt As String, datefmt As String, TicketNum As String, sample As String
    
    Dim LastNum As Long
    
    yfmt = Format(Date, "yy")
    mfmt = Format(Date, "mm")
    datefmt = yfmt & mfmt
     
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("TransactionDB")
    Me.TextBox1.Enabled = True
    With ws
        my_sheet = ActiveSheet.Name
        If Trim(Me.TextBox1.Value) = "" Then
             'Generate Ticket Number
            Me.TextBox1.SetFocus
            MsgBox "Please enter Ticket Number; one will be suggested…", vbExclamation, "Ticket Number"
             
            LastNum = CLng(Right(Range("A1").End(xlDown).Value, 3))
            Me.TextBox1.Value = "L1" & datefmt & "-" & CStr(LastNum + 1)
            Exit Sub
        End If
    End With
    Me.CmdNew.Enabled = True
     
End Sub