# Creating unique numbers sequence for multiple runs based on today's date • ### General discussion

• Hi

I want to write a code where I would have a sequence of 4 numbers like: 0001, 0002, 0003, 0004, 0005, 0006, 0007, 0008, 0009, 0010, 0011, etc...BUT, for each run, I would like to continue from where stopped on last run. The thing is that I will not be using the same file, I will be always receiving a new excel file for creating these numbers, but i need to continue from the number i stopped in the last run. Example: I run the macro and give me a sequence in column "O" (starting in cell "O2") from 0001 to 0018, I would like to run one more time and the code starts the sequence from 0019 on..until the last used row from that worksheet.

Is it possible to create something like this based on today's date? So, today i have a sequence for each one of the runs, tomorrow (from 12:00AM on) it would start from "0001".

Not sure if I was clear on my question. This is what I have so far, the sequence of numbers "0001, 0002...." until the last used row on the active worksheet. If the last used row is 13, I would have the sequence listed until "0013". Next run, on a new file (same template) I would like to start from number "0014" on cell "O2".

I appreciate if anyone can help me with this question, I didn't find anywhere else something similar to this.

Dim lRow As Long
Dim lCount As Long
Dim wse As Excel.Worksheet
Set wse = Application.ActiveSheet
Dim strCount As String

lRow = 2
lCount = 1
Do While lRow <= wse.UsedRange.Rows.Count

strCount = Trim(Str(lCount))
If Len(strCount) = 1 Then
wse.Range("O" & lRow).Value = "'000" & strCount
ElseIf Len(strCount) = 2 Then
wse.Range("O" & lRow).Value = "'00" & strCount
ElseIf Len(strCount) = 3 Then
wse.Range("O" & lRow).Value = "'0" & strCount
Else
wse.Range("O" & lRow).Value = "'" & strCount
End If

lCount = lCount + 1
lRow = lRow + 1
Loop

Monday, August 6, 2018 6:56 PM