Generate next requisition number in sequence

[tahjb]

[COLOR=rgba(0, 0, 0, 0.9)]Hey Excel Guru's -Looking for a little help with VBA code. I'm generating a requisition number on a form and each time I save the file I need excel to generate the next number in line. The below code partially works, but when I get to a certain number it keeps increasing the number of digits...Range("H4").Value = Left(Range("H4").Value, 4) & Mid(Range("H4").Value, 4, 4) + 1For example, I would want the requisition number to go in sequence: 05-1000, 05-1001, 05-1002 and so on. But, issue occurs and it jumps from 05-1000 to 05-11001 and adds a digitAny tips or pointers.Thank You[/COLOR]

 

[lrobbo314]

Welcome to the forum. This code will increment the requisition number in range H4 each time the workbook is saved.

Make sure to paste the code below in the workbook after save event.

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Dim SP() As String: SP = Split(Range("H4").Value, "-")
If SP(1) = 9999 Then
    SP(0) = SP(0) + 1
    SP(1) = 1000
Else
    SP(1) = SP(1) + 1
End If


Range("H4").Value = Join(SP, "-")
End Sub

 

[tahjb]

Welcome to the forum. This code will increment the requisition number in range H4 each time the workbook is saved.

Make sure to paste the code below in the workbook after save event.

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Dim SP() As String: SP = Split(Range("H4").Value, "-")
If SP(1) = 9999 Then
    SP(0) = SP(0) + 1
    SP(1) = 1000
Else
    SP(1) = SP(1) + 1
End If


Range("H4").Value = Join(SP, "-")
End Sub

Thanks will give a try!