Auto Serial number generator that resets to 01 every day

[Dave8899]

Hi all

Hope you can help i need to crate a serial number generator that creates a new serial number for every entry , and resets to 01 every day.

So far i have, the sheet auto populating The User ID, the date and it crates a Serial number, but i cant think/find a way to get the Serial number to reset to 01 each day.

Top code Add User ID and the Serial number bottom code adds the date.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim i As Long

i = Application.WorksheetFunction.CountA(Sheet1.Range("d:d"))

If i > 1 Then

Sheet1.Range("b" & i).Value = i - 1

i = Application.WorksheetFunction.CountA(Sheet1.Range("d:d"))

If i > 1 Then

Sheet1.Range("c" & i).Value = Application.UserName



End If

End If

End Sub



Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140722
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("c:c"), Target)
xOffsetColumn = -2
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, xOffsetColumn).Value = Now
            Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

sorry cant upload a mini sheet it a works PC and it very restricted

 

[mart37]

If i > 1 Then
If Sheet1.Range("A"&i-1)<>Now then i=2
Sheet1.Range("b" & i).Value = i - 1

 

[steve the fish]

Its confusing. Are you changing column D?? Maybe this to replace both? Test it out first.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim datelastran As Date, lr As Long

datelastran = Application.Max(Columns(1))

If Target.Cells.Count = 1 Then
    If Target.Column = 4 Then
        Application.EnableEvents = False
        lr = Range("B" & Rows.Count).End(xlUp).Row
        If datelastran <> Date Or Not IsNumeric(Range("B" & lr).Value) Then
            Target.Offset(0, -2) = 1
        Else
            Target.Offset(0, -2).Value = Range("B" & lr).Value + 1
        End If
        Target.Offset(0, -1) = Application.UserName
        Target.Offset(0, -3) = Date
        Application.EnableEvents = True
    End If
End If

End Sub

 

[Dave8899]

Hi Both, thank for the help, what I'm trying to do is, When the data in column A changes to the next day get the serial number in column B to reset start form 0 again and then continue for the rest of that day and then the next day etc. So may team can have a ref of Date_ serial number _who did it.

Hope this makes sense

 

[steve the fish]

But what is the trigger? Changing a cell in column D? If so try what i gave. If not then what is the trigger for the code to fire?

 

[Dave8899]

Hi Steve the Fish yes as soon as we put as something column D? I have tried what but sadly no luck

 

[steve the fish]

Need a little bit more than sadly no luck. What does that mean? What does it do? On my machine if you type a single value into column D of the sheet where the code is housed then it puts the date into A, a number into B, and the user into C. Yours will do the same under the same conditions.

 

[Dave8899]

so sorry, yes I should have explained more, the date appears, the user appears, but the the number column B stays at 1, regardless of the date, when I tested it I had 3 eateries for today, all showing 1 and when I chaged the computer date till tomorrow the same, the date etc update but still just 1 Hope helps a little

 

[Dave8899]

Hi Steve, just to let you know it’s working perfectly now there was nothing wrong with your code at all, there must have been with the sheet, no idea what it was possibly something from the others codes stuck or hidden I don’t know, but I opened a new sheet dropped you code in and it worked first time.

thank you so much for your help and apologies for making you doubt yourself.

Dave