DT909
12-08-2017, 02:56 AM
Hi guys,
I'm trying to create a userform where I need a textbox generation new ID number which is based on information coming from 2 different Combobox, each one with 3 letters and numbers + our fiscal year.
In each Combobox, there're 7 to 18 possible choices.
At the end the ID should like : CITP112018001 and the next CITP112018002...
If the first 10 digit changes like UCFP052017 then the 3 last digit start again 001, 002 etc.
My code works well when it comes to add or increase the last 3 digits. But when I have a new prefix (the first 10) it doesn't add the 001.
Here's the code :
Sub findnextnumber()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
Dim lr As Long
Dim x As Long
If Me.PCList = "" Or Me.proFITList = "" Then
Exit Sub
End If
a = Left(Me.PCList, 3)
b = Left(Me.proFITList, 3)
If Date > 9 / 30 / Year(Date) Or Date < 10 / 1 / Year(Date) + 1 Then
C = Year(Date) + 1
Else
C = Year(Date)
End If
myName = UCase(a & b & C)
lr = ws.Cells(Rows.Count, 3).End(xlUp).Row
For x = 1 To lr
If Left(ws.Cells(x, 3), 13) = myName Then
'find last number that applies
lastnum = Right(ws.Cells(x, 3), 3) + 1
End If
lastnum = Format(lastnum, "00#")
Me.EPCNum = myName & lastnum
Next x
End Sub
Any suggestion ?
I'm trying to create a userform where I need a textbox generation new ID number which is based on information coming from 2 different Combobox, each one with 3 letters and numbers + our fiscal year.
In each Combobox, there're 7 to 18 possible choices.
At the end the ID should like : CITP112018001 and the next CITP112018002...
If the first 10 digit changes like UCFP052017 then the 3 last digit start again 001, 002 etc.
My code works well when it comes to add or increase the last 3 digits. But when I have a new prefix (the first 10) it doesn't add the 001.
Here's the code :
Sub findnextnumber()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
Dim lr As Long
Dim x As Long
If Me.PCList = "" Or Me.proFITList = "" Then
Exit Sub
End If
a = Left(Me.PCList, 3)
b = Left(Me.proFITList, 3)
If Date > 9 / 30 / Year(Date) Or Date < 10 / 1 / Year(Date) + 1 Then
C = Year(Date) + 1
Else
C = Year(Date)
End If
myName = UCase(a & b & C)
lr = ws.Cells(Rows.Count, 3).End(xlUp).Row
For x = 1 To lr
If Left(ws.Cells(x, 3), 13) = myName Then
'find last number that applies
lastnum = Right(ws.Cells(x, 3), 3) + 1
End If
lastnum = Format(lastnum, "00#")
Me.EPCNum = myName & lastnum
Next x
End Sub
Any suggestion ?