Assigning unique values to dates

[quackdad]

I am using dates as a sort of serial number for identification. There can be multiple items (up to 3) using the same date, so I have created a way to refine the serial numbers. I also allow for the occasional string to be entered instead of a date. :

    Dim datecheck As Integer
    If IsNumeric(GigInfo.GigDate) Then
        datecheck = Application.WorksheetFunction.CountIf(Rows(10), GigInfo.GigDate)
        Range("D5") = Range("G10") + (0.1 * (datecheck - 1))
    Else
        datecheck = Application.WorksheetFunction.CountIf(Rows(10), 99999)
        Range("D5") = 99999 + (0.1 * datecheck)
    End If

So a first use of 6/30/2021 would generate a serial number of 44377.0, the next use of that date would be 44377.1, etc. Here's my potential problem: Say I have 3 items with the same date - 44377.0, 44377.1, and 44377.2. I delete 44377.1 (leaving only two items with the same date). The problem is that, if I want to add another item with that date, the code will see there are only 2 identical integers and assign the new item the serial number 44377.2, which already exists. I need a method to generate a unique serial number for this new item that would still leave the integer intact (as I use it for sorting).

For the record, I am using Excel 2010 on Windows 10.

 

[quackdad]

I appreciate very much the effort you put into creating the function. However, it's not clear to me how it works and I have made it a rule not to use code that I don't fully understand (or at least think I understand). Your questions did force me to look more closely at what I am doing. I was able to finally solve my problem with a Loop using row 5 (instead of 10 as previously); I had to rearrange some of my dedicated cells in column B. When entering a new gig 6 new columns are inserted at D:I, and since the Loop exits when a blank cell is found, the search begins in column J. I also decided to change scheme for increasing the serial number (SN) from adding a decimal to working with integers only; the date value is first multiplied by 1000. GigInfo.GigDate is data from a userform. What follows is a simplified version of what I'm actually using:

        If IsDate(GigInfo.GigDate) Then
            Range("B7") = GigInfo.GigDate * 1000
        Else
            Range("B7") = 99999000
        End If
        Range("J5").Activate
        Do Until ActiveCell = ""
            If ActiveCell = Range("B7") Then
                Range("J5").Activate   'Always going back to the beginning once the SN has changed
                Range("B7") = Range("B7") + 1
            End If
        ActiveCell.Offset(0, 1).Activate
        Loop
        Range("D5") = Range("B7")

Let me know if you see potential issues with this approach. Thanks again for your work.

 

[vw412]

If you want me to I can instruct you on how the function I supplied works. Otherwise I will consider this solved.