Configure job number to incorporate prefix, year, month and next sequential number

Question

I am using Excel 2013 (64 bit).

I'm currently working on a Userform which is used to log incidents. However I have been unable to work out in VBA how to come up with the code that will result in the next available number from the previous row populating the INC number textbox (ADD_txtSEC_INC_No) in the userform (and then update the spreadsheet accordingly).

The end format should display as IncYYYYMM-00000 - example: Inc201603-00456 (which is exactly what I need)

I currently have a code (below) which brings up the next number but it's not in the format required. Sample of New form in development

Private Sub UserForm_Initialize()
'** SECURITY INCIDENT NUMBER IN TEXTBOX
'**WORKS

    Me.ADD_txtSEC_INC_No.Enabled = True
    Dim irow As Long
    Dim ws As sw_SecIncidentDetails
    Set ws = sw_SecIncidentDetails

'find last data row from database'
   irow = ws.Cells(Rows.Count, 1).End(xlUp).Row

   Me.ADD_txtSEC_INC_No.Text = ws.Cells(irow, 1).value + 1

End Sub

Additional Information When this form goes LIVE then I will need to have it continue on from the previous number, example, if the Inc number on our current sheet, in Row A is Inc201603-00456 then I'd need your code to go to the next Inc number when the form is initialised, eg Inc201603-00457 We can't start the numbering from 0000 because that would throw the sequence out from previous entries. Sorry if this sounds confusing.

In column B I have the cell populated by a textbox called ADD_Date_Recorded_TXT which is coded :

       Me.ADD_Date_Recorded_TXT.value = Format(Now, "dd/mm/yyyy")

I mention this because David's solution below seems to change the format of the date to mm/dd/yyyy (don't understand why it did that) - I need all my dates to remain in the dd/mm/yyyy format.

I just thought I'd address these issues above in the "Additional Information" rather than responding to each individually

score 1 · Answer 1 · answered Mar 26 '16 at 11:35

As I understand it, your previous row has an incident number like Inc201603-00455, and you need to generate Inc201603-00456 for your new one? If so, try replacing your last line with this:

Me.ADD_txtSEC_INC_No.Text = "Inc" & Year(Date) & Format(Month(Date), "00") & "-" & Format(Split(ws.Cells(irow, 1).value, "-")(1) + 1, "00000")

This will assemble all the segments you need, in your required format. But this assumes that you want today's date, rather than lifting the date from your previous row. If you actually want to copy the date from the previous row then you'd use this instead:

Me.ADD_txtSEC_INC_No.Text = Split(ws.Cells(irow, 1).value, "-")(0) & "-" & Format(Split(ws.Cells(irow, 1).value, "-")(1) + 1, "00000")

We are using the Split function to divide the old incident number using the hyphen as a delimiter. So part (0) is 'Inc201603' and part (1) is the number as a text string, '00456'. But as soon as you do maths with it (+1) the value becomes a pure number, '456', so that's why we use the Format function to give it a five digit mask again.

Hi David, ty for your time and effort You are correct, date based on today, however, the 1st code you gave gives me 2 issues 1. In column B in sheet cells are filled with Me.ADD_Date_Recorded_TXT.value = Format(Now, "dd/mm/yyyy") but now has mm/dd/yyyy format Must be UK date format dd/mm/yyyy in Column B 2. The actual date that is coming out from your code is Inc201604-201605 and when I create another entry, it doesn't increment to the next sequential number, comes up as the same number Inc201604-201605 I appreciate the effort you've put in though Cheers, ShyButterfly — TheShyButterfly, Apr 05 '16 at 04:21

score 1 · Answer 2 · answered Mar 26 '16 at 13:01

To guarantee the next available number, you should COUNTIF the previous IncYYYYMM-nnnnn numbers in column A.

Private Sub UserForm_Initialize()
'** SECURITY INCIDENT NUMBER IN TEXTBOX
'**WORKS

    Me.ADD_txtSEC_INC_No.Enabled = True
    Dim i As Long, str As String
    Dim ws As sw_SecIncidentDetails
    Set ws = sw_SecIncidentDetails

    'find last data row from database'
    str = Format(Date, "\I\n\cyyyymm")
    i = Application.CountIf(ws.Columns(1), str & Chr(42))

    Me.ADD_txtSEC_INC_No.Text = str & Format(i, "-00000")

End Sub

This will work dynamically. Next month the count will restart at Inc201604-00000.

Hi Jeeped, Thank you for your time & effort. I don't want the number to restart the numbering each month. The number sequence must continue regardless of month and year. The only things that will change will be the year and month, based on the date that the incident/job is logged. — TheShyButterfly, Apr 05 '16 at 13:12

score 1 · Accepted Answer · answered Mar 26 '16 at 15:05

1

I find it quite dangerous to use the last row to build the new id. If the first colum were to be unsorted you'd probably end up with duplicated identifiers. So I would first search for the maximum number in the first column, then increment that number and finally format a new identifier:

Private Sub UserForm_Initialize()
'** SECURITY INCIDENT NUMBER IN TEXTBOX
'**WORKS

    Me.ADD_txtSEC_INC_No.Enabled = True
    Dim nextNumber As Long
    Dim nextId As String
    Dim ws As sw_SecIncidentDetails
    Set ws = sw_SecIncidentDetails

   ' get the next record number from the first column
   nextNumber = GetNextRecordNumber(ws.UsedRange.Columns(1))

   ' build the new record id
   nextId = "Inc" & Format(Now, "yyyymm") & Format(nextNumber, "-00000")

   Me.ADD_txtSEC_INC_No.text = nextId

End Sub

Private Function GetNextRecordNumber(source As Range) As Long
  Dim max$, v
  For Each v In source.value
    If InStr(1, v, "Inc") = 1 And v > max Then max = v
  Next
  If max <> Empty Then GetNextRecordNumber = Split(max, "-")(1) + 1
End Function

answered Mar 26 '16 at 15:05

Florent B.

41,537
7
86
101

Hi Florent B, Thank you for your time and effort Unfortunately there is only one problem, I can't seem to get it to work, it generates a Run Time Error '9' - "Subscript out of Range. It does not show a debug to indicate to me where the problem lies - I had commented out all my code relating to this function, I haven't had this error code before. All I have done is copy the routines and pasted them under appropriate sub-routines. I have also added some 'Additional Information' in my orig post that may be of additional help. With much gratitude, ShyButterfly – TheShyButterfly Apr 05 '16 at 03:06
There's probably a record id starting with "Inc" but without a "-" in it. Can you check it? – Florent B. Apr 05 '16 at 05:22
I'll check it out :) thank you very much for your time :) – TheShyButterfly Apr 06 '16 at 13:55
My apologies for not getting back to you sooner. I have taken your advice, and sure enough you were correct. your code works 100% as far as I can see ... the test will be when the month ticks over to a new one ... but I'll worry about that if/when that happens. Thank so very much for all your help. – TheShyButterfly May 09 '16 at 12:33

Configure job number to incorporate prefix, year, month and next sequential number

3 Answers3