VBA code to generate serial numbers in a specific format

Hi, I'm new to this forum and also quite new in VBA coding.

I'm trying to write some VBA code to generate a serial number that uses this format: YYMM-000, where YYMM is extracted from the current date (1708, for example), and 000 is the running number.
In my code I extract the maximum value from the column that contains the serial numbers, increment it and put that new formatted number into the next new row of that column. The reason I used the MaxValue approach is so that I don't have to mess with the user's existing data, just find the newest number and increment it, and for some reason the user's existing data may not be in chronological order.
It's working pretty well except for a catch: when the number reaches 999, it should reset to 001. Since I look for the MaxValue, this is where I hit the bottleneck.
Is there a way to fix this? Here's my code. Thanks.

Sub NewOrderNo()
Dim i As Double, MaxVal As Double
Dim Serials() As Double, OrderNo As String, yymm As String, Num As String
Call GetRealLastCell
ReDim Serials(RealLastRow - 2)
'Data starts from Row 3 of Column 3
For i = 3 To RealLastRow
    Serials(i - 2) = Val(Left(Cells(i, 3), 4) & Right(Cells(i, 3), 3))
    'Serials(i - 2) = Val(Right(Cells(i, 3), 3))
    If Serials(i - 2) > MaxVal Then MaxVal = Serials(i - 2)
Next i
yymm = Format(Date, "yymm")
Num = Format(Val(Right(CStr(MaxVal), 3)) + 1, "000")
'If Val(Num) > 999 Then Num = "001"
OrderNo = yymm & "-" & Num
'OrderNo = Format(Date, "yymm") & "-" & Format(MaxVal + 1, "000")
Cells(RealLastRow + 1, 3).Select
Selection.Value = OrderNo
End Sub

Re: VBA code to generate serial numbers in a specific format

...
My manager came to me and said: don't change the data structure, don't make my staff rearrange data, just come up with a code that can autogenerate the next number based on the existing data...
That was why I used the MaxValue approach and hit this 999-reset wall...
I have refined my code a bit but still can't find a way to get past this 999 and resume normal operation...
Any other approach other than MaxValue? I know I could also set up some kind of counter somewhere in the code, but everything will reset when I restart Excel. I could also hide the counter somewhere in the worksheet, but I was hoping I can achieve this just through VBA code.

Re: VBA code to generate serial numbers in a specific format

Quote from sktneer;797298

The latest and max possible order Num will always be in the last row in column C OR it may exist anywhere in column C?
Don't you have a sequence no. field in your data set? Is there any Date field?

Can you share a dummy sample workbook with the layout as same as that of your actual workbook to work with?

Here, I have attached a dummy file that I use to test my code. There is a date field but that's kind of irrelevant because the newest entry may not contain the newest OrderID, just the most recent activity, like delivery of a previous order (they also use this sheet to track deliveries). That was why I had to look for the max serial value and got stuck at the millennium crossover (the millennium bug?).

Now this is what it looks like at the crossover at the moment:
1708-998
1708-999
1708-001
1708-001
1708-001
Since the max is always 999, the subsequent ones are always 001

This file is the stripped down version of the actual one they use, which is a lot messier. The serial should reset back to 001 at the cross of the year or 999.

Re: VBA code to generate serial numbers in a specific format

Quote from sktneer;797299

You may write the Num variable value in the document property itself and recall it during the code execution.

Hey that just might work. Thanks, I'll give that a shot.