Restarting number sequence based on date

Question

I am hoping someone can help. I am trying to create a referencing system that would create a UID which resets by day. The reference will follow a structure of users initials, todays date and whichever number has been logged that day. So for example

• "MB - 20211015 - 001"
• "MB - 20211015 - 002"
• "AD - 20211015 - 003"

The counter is what I want to reset every day so on the 16th it was reset to 001

• "MB - 20211016 - 001"
• "AD - 20211016 - 002"
• "EH - 20211016 - 003"

I have a code which can get the number but it does not work dynamically in conjunction with the date. Can someone please assist?

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim Nextnum As Long
Dim Ini as String

Ini = Application.WorksheetFunction.Vlookup(Application.Username), ws1.range("IniLookup"), 4, 0)

Set ws1 = Worksheets("Lookups")
Nextnum = ws.Range("AB2").End(xlDown).Value + 1

Set ws2 = Worksheets("SMEXP")

With ws2
    .Range("B15") = Ini & " - " &Format(Now(), "yyyymmdd") & "- " & Nextnum
End With

Answer 1

Please, try the next way:

.Range("B15").value = Ini & " - " & Format(Date, "yyyymmdd") & " - " & Format(NextNum, "000")

Being a Long, I suppose that NextNum is something like 3, 4 and so on...

In order to reinitialize NextNum every day, you should replace

Nextnum = ws.Range("AB2").End(xlDown).Value + 1

with

If ws.Range("AC" & ws.rows.count).End(xlUp).Value = Date Then
        NextNum = ws.Range("AB2").End(xlDown).Value + 1
        ws.Range("AB2").End(xlDown).Offset(1).Value = NextNum
        ws.Range("AC" & ws.rows.count).End(xlUp).Offset(1).Value = Date
  Else
        NextNum = 1
        ws.Range("AB2").End(xlDown).Offset(1).Value = NextNum
        ws.Range("AC" & ws.rows.count).End(xlUp).Offset(1).Value = Date
  End If

Of course, the column AC:AC to be used if not used already. If not available, replace "AC" with any other available column. You should only place once the current date on that column, on its last row.