PDA

View Full Version : VBA Auto generate numbering



Kaniguan1969
03-13-2014, 09:53 PM
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
03-13-2014, 10:19 PM
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
03-15-2014, 06:06 AM
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
03-15-2014, 08:04 AM
Sub M_snb()
[A1:A2000] = [index("L1"&text(today(),"yymm")&text(row(A1:A2000),"000"),)]
End Sub

SamT
03-15-2014, 09:20 AM
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