none
Creating unique numbers sequence for multiple runs based on today's date RRS feed

  • 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