How can I Create a range of Alphanumeric serial numbers without Letter O or 0 Zeros

AAA1111,AAA1112,AAA1113...AAB1111,AAB1112,AAB1113

Hi,

This code does the first million or so in that sequence from AAA1111 to AGC9999 with No letter O or zero. Will that be enough?

On my machine it takes ~ 6 seconds to run so be patient, you may have a slower machine.

Sub codes()

'1048576

Application.ScreenUpdating = False

Dim x As Long, y As Long

Dim prefix As String

Dim Code1 As Long, Code2 As Long

Code1 = 65

Code2 = 65

y = 1

Do

prefix = "A" & Chr(Code1) & Chr(Code2)

For x = 1111 To 9999

    If Mid(x, 2, 1) = "0" Then

        x = x + 100

    End If

    If Mid(x, 3, 1) = "0" Then

        x = x + 11

    End If

    If Mid(x, 4, 1) = "0" Then

        x = x + 1

    End If

    Cells(y, 2) = prefix & x

y = y + 1

Next

Code2 = Code2 + 1

If Code2 = 79 Then

    Code2 = Code2 + 1

End If

If Code2 = 91 Then

    Code2 = 65

    Code1 = Code1 + 1

End If

Loop While y < 1000000

Application.ScreenUpdating = True

End Sub

Here are several techniques for creating serial numbers, then you already know how to filter the unwanted ones out

Using sequential numbers – Serial Number

http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

One common question on the newsgroups is "how do I increment a number in my sheet each time I use it", whether it's an invoice, an order form, or some other numbered form. There are a number of factors to take into account . For instance, whether the current number is saved locally or on a server, or whether more than one person will access a number at a time, or only a single user.

Two ways explored here. It is assumed that the sequential numbers should be stored locally, and that only one number at a time is accessed. The first is to use the registry (and yes, Macs use the equivalent of a registry

• the values are stored in file(s) in the Preferences folder). The second uses a text file to store the relevant data. For simplicity I'll assume that an invoice is generated from a template with the following layout:.

Using the registry to hold sequential numbers

An advantage of the registry is that the numbers are unlikely to be inadvertently modified or deleted. A significant disadvantage is that the registry is not designed as a database, which can retain a history, but rather is more suited to storage of a single record. Information is stored in the registry using the SaveSetting method, and retrieved using the GetSetting method. This macro, put in a template's ThisWorkbook code module, will produce an incremented sequential number each time the template is used to generate a document:

Private Sub Workbook_Open()

        Const sAPPLICATION As String = "Excel"

        Const sSECTION As String = "Invoice"

        Const sKEY As String = "Invoice_key"

        Const nDEFAULT As Long = 1&

        Dim nNumber As Long

        With ThisWorkbook.Sheets("Invoice")

            With .Range("B1")

                If IsEmpty(.Value) Then

                    .Value = Date

                    .NumberFormat = "dd mmm yyyy"

                End If

            End With

            With .Range("B2")

                If IsEmpty(.Value) Then

                    nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT)

                    .NumberFormat = "@"

                    .Value = Format(nNumber, "0000")

                    SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&

                End If

            End With

        End With

     End Sub

Using a text file to hold sequential numbers

This method is more useful in some situations. The biggest advantage is that the sequential number is no longer tied to a particular machine - it can be stored on a common server, or even a thumb drive. Disadvantages include difficulty in keeping the file from being modified simultaneously by two users, or of the file being more easily deleted or modified. This function will return the next sequential number:

Public Function NextSeqNumber(Optional sFileName As String, Optional nSeqNumber As Long = -1) As Long

        Const sDEFAULT_PATH As String = "<your path here>"

        Const sDEFAULT_FNAME As String = "defaultseq.txt"

        Dim nFileNumber As Long

        nFileNumber = FreeFile

        If sFileName = "" Then sFileName = sDEFAULT_FNAME

        If InStr(sFileName, Application.PathSeparator) = 0 Then _

            sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName

        If nSeqNumber = -1& Then

            If Dir(sFileName) <> "" Then

                Open sFileName For Input As nFileNumber

                Input #nFileNumber, nSeqNumber

                nSeqNumber = nSeqNumber + 1&

                Close nFileNumber

            Else

                nSeqNumber = 1&

            End If

        End If

        On Error GoTo PathError

        Open sFileName For Output As nFileNumber

        On Error GoTo 0

        Print #nFileNumber, nSeqNumber

        Close nFileNumber

        NextSeqNumber = nSeqNumber

        Exit Function

    PathError:

        NextSeqNumber = -1&

    End Function

If you provide a full path in sFileName, that's where the file will be stored. If not, the file will be stored in whatever default directory you specify. You can set the sequential number by providing a value for nSeqNumber.

Thus, if I'm only using one sequence I can use

Public Sub Workbook_Open()

        ThisWorkbook.Sheets(1).Range("B2").Value = NextSeqNumber

    End Sub

to return the next sequence number. If I'm using multiple sequences, I include the filename (with path, if the text file is not in the default path).

Public Sub NewClientInvoice()

        ThisWorkbook.Sheets(1).Range("B2").Value = NextSeqNumber("Client1.txt")

    End Sub

And if I want to start a new sequence, beginning at, say, 1001, include that number in the function call. If the client name were in cell B4:

Public Sub SetUpNewClient()

        With ThisWorkbook.Sheets(1)

            .Range("B2").Value = NextSeqNumber(.Range("B4").Value & ".txt", 1001)

        End With

    End Sub

This page last updated Thursday, 28 April 2005

Use Registry for Serial Numbers – take 2

http://answers.microsoft.com/en-us/office/forum/office_2010-excel/file-name-automatic-number-increase/51dee21e-a21f-46d6-ba50-c4da77bd0048

One method...............save a sequential number in the Registry then when you open a new workbook from the Template it is immediately saved as template name + sequential number pulled from the Registry

Example code for Thisworkbook module of an Excel template named MyTemplate.

NOTE: MyTemplate must be saved as macro-enabled template (*.xltm)

Private Sub Workbook_Open()

    Const sAPPLICATION As String = "Excel"

    Const sSECTION As String = "MyTemplate"

    Const sKEY As String = "MyTemplate_key"

    Const nDEFAULT As Long = 1&

    Dim nNumber As Long

    nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT)

    Application.DisplayAlerts = False

    ActiveWorkbook.SaveAs sSECTION & Format(nNumber + 1&, "_0000")

    Application.DisplayAlerts = True

    SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&

End Sub

The first new workbook will be saved as Excel workbook MyTemplate_0001.xlsx then increment from there.........probably no need to have it as macro-enabled.  If you did need the copy saved as macro-enabled post back for revised code to saveas macro-enabled and prevent Workbook_Open from running when copies are opened later for review.

Generating Unique Numbers for Worksheets – Serial Number

http://excel.tips.net/T003336_Generating_Unique_Numbers_for_Worksheets.html

Summary: You may need to automatically generate unique numbers when you create new worksheets in a workbook. Here's a couple of easy ways to do it. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.)

Sometimes you may need Excel to generate a unique number for your worksheets. For instance, you could be using Excel to create forms such as invoices, statements, or tracking sheets, and you need an unique numbers for each form (I'll call this a ticket number). This, of course, implies that Excel needs to remember the number from one session to the next.

There are a couple of ways you can approach this problem. If the numbers don't need to be sequential, you could create a ticket number based on the current time of day, in seconds. The following macro can be added to the ThisWorksheet object:

Private Sub Workbook_NewSheet(ByVal Sh As Object)

    Dim lTicket As Long

    lTicket = CLng(Time * 24 * 60 * 60)

    Sh.Range("A1") = lTicket

End Sub

The macro is triggered every time a new worksheet is added to the workbook. It takes the current time, converts it to an integer number of seconds, and then places that value into cell A1. The likelihood of duplicating ticket numbers within any given day is remote, but it could happen over time. (For instance, if you create a ticket at the exact same time today that you did yesterday or last week.)

To get around this problem, you could create a ticket number in the following manner:

Private Sub Workbook_NewSheet(ByVal Sh As Object)

    Dim sTemp As String

    sTemp = Format(Date, "yymmdd") & Format(Time, "hhmmss")

    Sh.Range("A1") = sTemp

End Sub

This version of the event handler constructs a ticket number based both the date and time. Unless you are creating trouble tickets very quickly, this approach should reduce the possibility of duplicate numbers generated by the macro.

If the numbers must be sequential within the current workbook, then you can define a name that contains the current high value of your ticket number, and then a macro that places that number in a cell on a new worksheet and increments the value of the stored number. Follow these steps to start:

1. Choose Name from the Insert menu, then choose Define. Excel displays the Define Name dialog box. (Click here to see a related figure.) (To display the equivalent dialog box in Excel 2007, display the Formulas tab of the ribbon and then click on Define Name in the Defined Names group. Excel displays the New Name dialog box.)
2. In the Name box, enter a name such as MaxNum.
3. In the Refers To area at the bottom of the dialog box, enter an equal sign followed by the value you want used for the next ticket number.
4. Click on OK. The new name is stored in the workbook.

Now, add the following macro to the ThisWorksheet object in the VBA Editor:

Private Sub Workbook_NewSheet(ByVal Sh As Object)

    Dim iMax As Integer

    iMax = Mid(ThisWorkbook.Names("MaxNum"), 2)

    Sh.Range("A1") = iMax

    iMax = iMax + 1

    ThisWorkbook.Names("MaxNum").RefersTo = "=" & iMax

End Sub

This macro is executed every time you insert a new worksheet in the workbook. It retrieves the value you stored in the MaxNum, places that value into cell A1 of the new worksheet, and then increments what is stored in MaxNum.

AAA1111,AAA1112,AAA1113...AAB1111,AAB1112,AAB1113

Hello - I know I can create a range of alphas and numbers, cocatenate them, then conditional format those that contain the 2 characters that I want to exclude, then select and delete. However, I'm not exactly sure how to avoid doing this manually by creating a macro or function to auto generate a serial number sequencer to use AAA and NNNN (where A = alphas) and (N = Numerics) and AAA can never contain the letter O and the NNNN range can never contain 0.

 

Any help would be greatly appreciated!!

Hi,

What the series AAANNNN comprises of isn't clear; well to me it isn't.

I can follow the bit of no zeroes in the NNNN bit and no letter O in the AAA bit but that about all. Can you manually create and post here the first (say) 20 codes in this series to indicate how it increments?

This looks very informative. I'm not sure about my ability to incorporate, but thank you very much for taking the time to address!! I'm not sure how to mark 'helpful' without closing the request, but I will try to figure out. Thanks.