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