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.

 

[vw412]

why not find the largest GigInfo.GigDate and add .1 to it? Is there any reason you need to have consecutive values? I don't have any formula or code at this time.

 

[quackdad]

GigInfo.GigDate is a value from a UserForm; datecheck is looking for something on the worksheet. I'm not sure how to code it to look for the highest value of the integer.

 

[vw412]

Trying to understand your design. Could you post an image of your sheet with some representative data? After that we can talk with a little more detail.

 

[kvsrinivasamurthy]

dates are in A2:A10.
In B2 then copy down

=1*(INT(A2)&"."&COUNTIF($A$2:$A2,$A2)-1)

If row is inserted copy the formula to inserted row. For deletion no problem.

 

[quackdad]

I hope this helps:

When I enter a new gig, new columns are inserted (D:I) and are populated with data (including the date from GigInfo.GigDate in G10. I search row 10 for dates and if there are no duplicates, then the serial number is created by inserting the unformatted version of the date in D5 (formatted to show a 1 place decimal). If there are duplicate dates, then the decimal value is increased correspondingly. I then sort the gigs in a hierarchy by Row 4, Row 5, then row 6. The serial numbers are important because it allows me to attach the data in rows 15 and below to the appropriate gig.

This shows two gigs with the same performance date of 7/18/21 (CSPS and Big Top). If CSPS were to be rescheduled to a new date, and new gig was created for 7/18/21, the search of row 10 would show only one existing gig on that date (Big Top, SN 44395.1). It would assign the new gig a SN of 44391 + 0.1, giving me two gigs with the same serial number.

For what it's worth, the uncolored rows and columns will be hidden. The columns beginning with V and to the right are for past (Inactive, row 4) gigs in order to preserve a history. This is a project I've been working at for a number of years and with the complex (for me!) code I've created, I'm not interested in adding additional rows or columns if it can be avoided.

As I look at this and am trying to explain it, maybe the answer is to, before inserting a value into D5, search row 5 for a matching integer and find the highest existing value for cells with that integer. But my aging and feeble brain is struggling with how to do that. [That and the fact that the VBA gremlins (or whoever) are active, and the code I had above stopped working and IsNumeric would no longer recognize the date as a number. But I did figure out to change it to IsDate. Can't wait to see what tomorrow brings.]

 

[vw412]

I have some thoughts but need to understand how you do your data entry on this sheet. Do you use a userform to do data entry and then populate cells from that userform entry? Basically you are looking for a way to validate a gig date/serial number with already existing data. Not that hard but need some idea of the flow of data entry to find where to do the validation.

 

[quackdad]

Nothing is entered by the user directly on the worksheet. There is a userform that gathers the data to populate E10, G10, H10, G11, H11, and G13. D5 is populated at the same time by the macro above as part of a click event on the userform. E5:I5, H7:H9, H12, and H13 are all generated by formulas in the cells. The data in rows 15 and below are added later via a macro that links the cells to a database on another sheet.

 

[quackdad]

I've done some refining and I think what I need at this point is VBA code to search a given range (in this case Row 5) to find the highest value of cells whose integer matches GigInfo.GigDate. Then I can add to that value to input in D5. It seems to me this should be a simple thing to do, but I am totally self-taught in VBA and this is not an area I have done much work with.

 

[vw412]

Here is a function you can use:

Option Explicit

Public Function NextSerialNumber(inDate As Date, InRange As Range) As Single
  Dim DateLong As Long, c As Range, TestSN As Long, MaxSN As Single
  DateLong = CLng(inDate) ' make inDate into a long integer
  MaxSN = 0
  For Each c In InRange
    TestSN = CLng(c.Value) ' make value of current cell into a long integer for comparison
    If DateLong = TestSN Then
      MaxSN = IIf(MaxSN > c.Value, MaxSN, c.Value)  ' update MaxSN if inDate = c.Value date
    End If
  Next c
  If MaxSN = 0 Then
    NextSerialNumber = CSng(inDate)  ' inDate not found so it is returned as NextSerialNumber
  Else
    NextSerialNumber = MaxSN + 0.1   ' inDate is found so it is return MaxSN plus 0.1 as NextSerialNumber
  End If
End Function

You can use this as a user-defined function in a cell or call it from some VBA.