Search code examples
vbaexcelexcel-2013countif

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

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

Solution

  • 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