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
Display More