Hi all. I have the below code to create sequential numbers everytime a new entry is created. For example: DL-19-001, DL-19-002, DL-19-003.... etc. This format will change from time to time so I need the last three digits to reset and start new. The 19 stands for the last two digits of the year, as we are now 2020 the entry will change to DL-20-001. If I manually change the code to modify DL-19 to DL-20 the sequential number does not start from 001 if there is not other entry under DL-20, it just keeps adding based on the next sequential number of the last three digits. Can I have your help on this to apply the reset to the code?
Thank you.
VBA Code:
With Sheets("Data").Range("A6", Sheets("Data").Range("A" & Rows.Count).End(xlUp))
X = .Worksheet.Evaluate("max(if(isnumber(--right(" & .Address & ", 3)),--Right(" & .Address & ",3)))")
End With
TextBox1 = "DL-19-" & Format(X + 1, "000")
Thank you.