Assign "serial number" that NEVER changes again...

[agulesin]

Hello Everyone,

I've been searching for a few days for the solution to my problem, but nothing fits the bill.

Imagine a system for recording Invoices. The invoice number cannot be re-used, and must not change after it has been entered (or rather, looked up from a counter). Is there any way to do this? Preferably without using VBA but I think it's impossible without!

In short, I want to look up a value, insert it in a cell and never allow that cell to change again. More detail: I am recording letters etc. sent to a customer. Entering a code for letter/memo/document increments its own counter and supplies a number for that particular document type. If I delete a previous entry, neither the counter nor previously used entries should change (as this will mess up the records!)

Any suggestions most welcome. Thank you for your assistance!

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

You could just type in the number!

You could create a formula to tell you what the next number should be. For instance:

Excel 2013
	A	B	C
1	Next Number:	3
2		Invoice	Details
3		1	vvv
4		2	iii
Sheet3

Cell Formulas
Range	Formula
B1	=MAX(B3:B1048576)+1

A VBA solution could automate this by detecting additions to column C and then writing the number in for you. Once the number was in place the VBA could be made to never change it.

 

[agulesin]

Hi and welcome to the MrExcel Message Board.

You could just type in the number!

That's what I'm doing at the moment, but it's too easy to miss one out!

A VBA solution could automate this by detecting additions to column C and then writing the number in for you. Once the number was in place the VBA could be made to never change it.

That's what I want. Anyone got the time to throw something together?

I have to select from three different series, let's call them AA, BB and CC. The number format is AA-000nn etc.

Once again thanks for all your help...

 

[RickXL]

I think you will have to help me with some details first:

Are you using a PC or Mac?
How would you like the new numbers to be triggered? That is, what would cause a new number to appear?
Which column is the number to be written in?
Will the numbers always start with two letters and a hyphen?
Will the last five digits vary or just the last two?

 

[agulesin]

I think you will have to help me with some details first:

Are you using a PC or Mac? PC, Excel 2010
How would you like the new numbers to be triggered? That is, what would cause a new number to appear?
In column B I'm entering two letters, which would cause a new number, preceded by those letters, to be generated in column J. Each set of letters has its own independent counter.

Which column is the number to be written in? At the moment it appears in J but there are other things to be added, a standard string preceding the number (the same for all groups). Let's call that "TEXT" for now.
Will the numbers always start with two letters and a hyphen? Yes
Will the last five digits vary or just the last two? first number was 00001, highest 99999 but I hope we won't reach that high! Also I would need to "seed" the generator as I've already used some numbers.

I hope I'm not being too pedantic and wanting too much...

 

[RickXL]

Hi,

Don't worry about being pedantic. The computer is far more pedantic than that.

The following code needs to be pasted in to the code module for the worksheet.

It runs every time you change the worksheet but it only responds if you enter either AA, BB or CC at the end of column B.
When that happens it analyses the contents of column J and looks for all the numbers associated with the entered prefix. It finds the highest one and adds one to it for the next number.
Then it writes the complete ID back into column J in the same row as the newly entered prefix.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim arr     As Variant
    Dim i       As Long
    Dim idMax   As String
    If Target.Count > 1 Then Exit Sub
    If Target = Cells(Rows.Count, "B").End(xlUp) Then
        On Error GoTo Err
        Application.EnableEvents = False
        Target.Value = UCase(Target.Value)
        idMax = "00000"
        Select Case Target.Value
            Case "AA", "BB", "CC"
                arr = Range("J2", Target.Offset(0, 8))
                For i = 1 To UBound(arr)
                    If Left(arr(i, 1), 2) = Target.Value Then
                        If idMax < Right(arr(i, 1), 5) Then idMax = Right(arr(i, 1), 5)
                    End If
                Next
                Target.Offset(0, 8) = Target.Value & Format(idMax + 1, "-00000")
        End Select
    End If
Err:
    Application.EnableEvents = True
End Sub

The code assumes headings in row 1 and will use any existing data if there is any to find the next number.
Note: The code keeps no record of the last used number. It relies on the contents of column J. So if you empty column J it will start back at 00001.

 

[agulesin]

Hi,

The following code needs to be pasted in to the code module for the worksheet.

Thanks very much RickXL, I hope to try it when I get a few minutes spare!

I can see how to customise the AA, BB and CC to suit my codes, but my column labels are in row 9 - will that be a problem? Rows 1-8 contain a "cheat sheet" of codes which I've been using to track up to now!

Also as I'm a relative newbie, I assume that pasting the code into a "macro" (VBA editor window) will be enough to get it going? (I've done that with macros from the 'net before now). Please advise if otherwise.

Once again, many thanks for your help.

 

[RickXL]

I have modified the code so that the first data row can be specified in the Const statement at the start of the code. It is currently set to 10 so anything happening in rows 1 to 9 will be ignored.

I have tidied up the code so that it will start from an empty worksheet without a problem (I hope!).

The code needs to be copied and pasted into a module for that worksheet e.g. Sheet1 or whatever you called it. It won't work in a normal Module because it needs to respond to "events". In this case a WorkSheet Change event. Every time the worksheet changes the code will run.

Private Sub Worksheet_Change(ByVal Target As Range)
    Const rowStart  As Long = 10
    Dim arr         As Variant
    Dim i           As Long
    Dim idMax       As String
    If Target.Count > 1 Or Target.Row < rowStart Then Exit Sub
    If Target = Cells(Rows.Count, "B").End(xlUp) Then
        Select Case UCase(Target.Value)
            Case "AA", "BB", "CC"
                On Error GoTo Err
                Application.EnableEvents = False
                Target.Value = UCase(Target.Value)
                idMax = "00000"
                arr = Range(Cells(rowStart, "J"), Target.Offset(0, 8))
                If Not IsEmpty(arr) And IsArray(arr) Then
                    For i = 1 To UBound(arr)
                        If Left(arr(i, 1), 2) = Target.Value Then
                            If idMax < Right(arr(i, 1), 5) Then idMax = Right(arr(i, 1), 5)
                        End If
                    Next
                End If
                Target.Offset(0, 8) = Target.Value & Format(idMax + 1, "-00000")
        End Select
    End If
Err:
    Application.EnableEvents = True
End Sub