VBA to restart the last three digits in sequential numbers created by code when a change in format

YingFa

Board Regular
Joined
Nov 4, 2019
Messages
63
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?

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.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
How about
VBA Code:
   With Sheets("Data").Range("A6", Sheets("Data").Range("A" & Rows.Count).End(xlUp))
      x = .Worksheet.Evaluate("MAX(IF((ISNUMBER(--RIGHT(" & .Address & ",3)))*(MID(" & .Address & ",4,2)=RIGHT(YEAR(TODAY()),2)),--RIGHT(" & .Address & ",3)))")
   End With
   TextBox1 = "DL-" & Format(Date, "yy") & "-" & Format(x + 1, "000")
 
Upvote 0
It worked well when I created a new entry. It now shows the year 20 and starting as 001.

Thanks a lot for your help on this.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
You're welcome & thanks for the feedback
Hi Sorry for coming back with this bu can I have your help again? When I tried the code it worked well and that is why I commented it in here. But I never tried the code for two entries. So, the first entry worked well creating DL-20-001, but when I created the second entry, it repeated the same entry DL-20-001, when it should be DL-20-002. I have the same code you kindly helped me with.
 
Upvote 0
Are you writing the DL-20-001 value into col A of the Data sheet?
If so then the code should work.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top