Spreadsheet to Generate and Track ISRC Numbers With a Single One Click Button To Generate Next Number In The Sequence

[deciongroup]

Hello,

I am [very much] a novice with macros and buttons so I am reaching out for [any] help for this effort. I started a small sound design business and need to track International Sound Recording Codes [ISRC] which are assigned to all MP3 and other formats of files delivered to my clients.

What I want to do is to simply have a spreadsheet that I will enter project titles and other information into in a given row. I then want a single button [labeled "MAKE NEXT ISRC"] in the center of the top row that when clicked will generate the next number and place that number in a given cell.

For example, [viewing the attached screenshot], I enter the date, the name of who assigned the code, the project producer, and the project title. Then I want to click the "MAKE NEXT ISRC" button and the next number colored blue [starting with QZTDG2000001] is generated in cell C3. The next entry and done the same way, click the "MAKE NEXT ISRC" button and the next number in the sequence fills cell C4 and so on.

Can anyone tell me how this can be done in whole or in part]? I would be grateful.

 

[CSmith]

Are you wanting to find the next open line? will there be blank lines?

 

[ajetrumpet]

you need to give more info to get an answer on the issue of "how to generate the next ISRC". what you're missing is what kind of "criteria" determines how that alphanumeric string is generated. you didn't say anything about that in your post. just saying that the first one will be "QZTDG20000001" means nothing in terms of determining where it came from. as far as putting the value, once you generate it, into a cell, all you have to do in that regard is select a cell in a given range, use the spec called ".end(XLDOWN)" to move to the last row of populated data, then get the row number by getting property ".ROW" property of the range and put the value in the column. it's as simple as that.

 

[Leith Ross]

Hello Decion,

Add a new VBA Module to your workbook. Copy the code below and paste the macro code into the new module. Assign the macro to your Button.

Sub NewISRC()

    Dim Cell    As Range
    Dim dc      As Long
    Dim Rng     As Range
    Dim Wks     As Worksheet

        Set Wks = ActiveSheet
        
        Set Rng = Wks.Range("A2").CurrentRegion
        
        If Rng.Row = 1 Then Exit Sub
        
        Set Cell = Rng.Cells(Rng.Rows.Count, "C")
        
        On Error Resume Next
            dc = CLng(Right(Cell.Offset(-1, 0), 5))
            If Err <> 0 Then dc = 1 Else dc = dc + 1
        On Error GoTo 0
        
        Cell.Value = "QZTDG" & Format(Cell.Offset(0, -2), "yy") & Format(dc, "00000")
            
End Sub

 

[deciongroup]

Are you wanting to find the next open line? will there be blank lines?

Thanks for your response. There idea is to just have the next number in the sequence fill the next blank space in the "C" column.

you need to give more info to get an answer on the issue of "how to generate the next ISRC". what you're missing is what kind of "criteria" determines how that alphanumeric string is generated. you didn't say anything about that in your post. just saying that the first one will be "QZTDG20000001" means nothing in terms of determining where it came from. as far as putting the value, once you generate it, into a cell, all you have to do in that regard is select a cell in a given range, use the spec called ".end(XLDOWN)" to move to the last row of populated data, then get the row number by getting property ".ROW" property of the range and put the value in the column. it's as simple as that.

I can appreciate your questions and what is simple for one person might not be as simple for another.

The alphanumeric string as I had indicated is the string built from information assigned to music creators/publishers to add a digital signature to music for streaming, downloads, and for all tracking for sales and usage around the world. For example, these codes are assigned to each track on a CD and another for the entire CD, so a 10-track CD will have 11 codes of these type. It is what allows artists and record label information to appear on your car radio or Sirrus radio or on a streaming service. It is how publishers, songwriters, and other music [and video] creators get paid when music or sound effects are used with or without being properly licensed when that usage is not captured by your PRO or agent like Harry Fox in a commercial, video games or similar. These codes are required to have complete catalogs for publishing.

The "QZ" indicates that the publishers/owner of the sample, song, or clip is in the United States. The next three letters are uniquely assigned to a music publisher, record label, or in our case, sound designer for registering all of your projects or songs. The following two [2] numbers indicate the year that the work is registered. The final five [5] numbers are assigned by our engineer to uniquely number every project that we produce. Since [in a single session/day] we can produce as many as 25 new sound effects in a three [3] minute video clip, along with background music and scoring, and each must have a separate and unique ISRC number to track the number of downloads, or plays via iTunes, Accuradio, Pandora, Facebook, YouTube, radio, etc., this project/task is to better assign and track numbers assigned and which we must submit to RIAA.

The number assigned to our new effort starts at QZTDG2000001. I am looking to just add "1" to the that number and have it appear in the next cell in column "C". It could be done manually but I would like it made simple for the team and have the button do that work.

 

[CSmith]

How about this?

Book1
	A	B	C	D	E
1	PIC
2	Date	Assigned By	ISRC	Producer	Title
3	1/19/2020 6:46:57 PM	D.Hines	QZ-TDG-20-00001	D. Hines	Example Composition
MrExcel Help- deciongroup

Cell Formulas
Range	Formula
A3	A3	=NOW()

Sub btnMakeNewISRC()
  Const CountryCode = "QZ"
  Const RegistrantCode = "TDG"
  Const SheetName = "MrExcel Help- deciongroup"
  Dim rw As Range, r As Long
  r = Worksheets(SheetName).Cells(Rows.Count, 3).End(xlUp).Row
  Do
    DoEvents
    If Cells(r, 3) = "" Or IsEmpty(Cells(r, 3)) Then
      If r < 4 Then
        Cells(r, 3).Value = CountryCode & RegistrantCode &  Right(DatePart("yyyy", Now()), 2) & "00001"
        Exit Sub
      Else
        Cells(r, 3).Value = CountryCode & RegistrantCode & Right(DatePart("yyyy", Now()), 2)  & Right("0000" & CInt(Right(Cells(r - 1, 3).Value, 5)) + 1, 5)
        Exit Sub
      End If
    Else
      r = r + 1
    End If
  Loop
End Sub

 

[deciongroup]

Yes. This is what I am trying to do.

 

[CSmith]

? What is?

 

[deciongroup]

Sorry. Your example....al2bb.xlam. That is what I am trying to do except for the dashes that break up the code.

 

[CSmith]

I took those out

I built from data here: ISRC