Advancing an Irregular Number

[mharper90]

I'm trying to get TextBox1 in UserForm1 to auto populate with the next number in a sequence. The difficulty is that the number system is irregular. It follows this format: XXX-YYYY-ZZ , where XXX is a constant identifier of our organization, ZZ is the last two digits of the year, and YYYY is the sequential numbering from 0001 to 9999 for each item generated in that year (the zeros are important, as YYYY must be 4 digits).

I want to add to the UserForm macro, a way for it to find the largest YYYY for the current ZZ (year) in column A of ws1. Then add +1 to the YYYY portion, and suggest that as the next number to use in TextBox1 when UserForm1 is initiated.

For example, if column A has the following numbers:

555-0001-18
555-0002-18
555-0003-18
555-0001-19
555-0002-19

Then when UserForm1 is initialized, TextBox1 would already have the suggested value of 555-0003-19 ready.

Most likely column A will already be in numerical order, but ideally I'd like this macro to find the largest YYYY value for the current ZZ (year) without requiring the list to be in order. Not a dealbreaker if this is significantly more difficult. Thanks for the help!

 

[Akuini]

Try this:
I assumed the "XXX" part is always "555"

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] UserForm_Initialize()
[COLOR=Royalblue]Dim[/COLOR] va
[COLOR=Royalblue]Dim[/COLOR] yr [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR], y [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], x [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
va = Range([COLOR=brown]"A1"[/COLOR], Cells(Rows.Count, [COLOR=brown]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp))
yr = Format([COLOR=Royalblue]Date[/COLOR], [COLOR=brown]"YY"[/COLOR])
Debug.Print yr
[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
    [COLOR=Royalblue]If[/COLOR] Right(va(i, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]2[/COLOR]) = yr [COLOR=Royalblue]Then[/COLOR]
        y = [COLOR=Royalblue]Mid[/COLOR](va(i, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]5[/COLOR], [COLOR=crimson]4[/COLOR])
        [COLOR=Royalblue]If[/COLOR] y > x [COLOR=Royalblue]Then[/COLOR] x = y
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]Next[/COLOR]

TextBox1.Text = [COLOR=brown]"555-"[/COLOR] & Format(x + [COLOR=crimson]1[/COLOR], [COLOR=brown]"0000"[/COLOR]) & [COLOR=brown]"-"[/COLOR] & yr

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
[/FONT]

 

[Rick Rothstein]

Here is a function that you can use to get the next sequence number... simply assign its output to the TextBox inside of the UserForm's Initialize event.

[table="width: 500"]
[tr]
	[td]Function NextSeqNum()
  Dim LastNum As String, Yr As String
  LastNum = Cells(Rows.Count, "A").End(xlUp).Value
  Yr = Year(Now) Mod 100
  If Val(Right(LastNum, 2)) < Yr Then
    NextSeqNum = Left(LastNum, 3) & "-0001-" & Yr
  Else
    NextSeqNum = Left(LastNum, 3) & Format(1 + Mid(LastNum, 5, 4), "-0000\-") & Yr
  End If
End Function[/td]
[/tr]
[/table]