Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Auto-Generate Number in Sequence

This is a discussion on Auto-Generate Number in Sequence within the Excel Questions forums, part of the Question Forums category; Hello all, I need your help because I am a bit of a newbie! I have a spreadsheet that has ...

  1. #1
    New Member
    Join Date
    Jun 2010
    Posts
    7

    Default Auto-Generate Number in Sequence

    Hello all,

    I need your help because I am a bit of a newbie! I have a spreadsheet that has a unique number for each row that gets sorted by due date. When a new record is added, the number for the row has to be the next in sequence. Is there a way to automatically generate the next number that should be used for a new row that is not a duplicate of any of the numbers above it.

    Here's an example of what I mean:

    1 John Doe email@email.com 1/1/2010
    2 Jane Smith email@email.com 5/1/2010
    etc...

    If it can't be automatically generated, then even if I can pick it from a list would be okay.

    The reason I can't just number the rows beneath it and wait for them to be filled in is that this list gets exported based on the number of records in it. If I fill in numbers it exports blank records.

    I'm hoping someone here could help me!

  2. #2
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690

    Default Re: Auto-Generate Number in Sequence

    Hi and welcome to the Board!!
    You can use a VBA solution for this!!
    Basicly when a new date is entered, the sheet resorts and renumbers!! Would that be an Option?? It's not diffecult to do and would run in the background!!
    What column holds your Date? What Column has the numbers??
    lenze
    If you have to tell your boss you're good with Excel, you're NOT!!
    All I know about Excel I owe to my ignorance!
    Scotch: Because you don't solve great Excel problems over white wine

  3. #3
    New Member
    Join Date
    Jun 2010
    Posts
    7

    Default Re: Auto-Generate Number in Sequence

    Hi Lenze,

    The sheet needs to sort by the due date before anything else. Right now, there is a macro (called Sort) that runs at workbook open. First is sorts column N (due date), then column B (number), both ascending.

    Also, I have another question I forgot in my first post. Someone before me has built a macro that refers to specific rows. Is there a way to change the 2 to 50 part of the text below in blue to equal however many rows there are filled in starting at row 2 and goes to the last entry?

    With ActiveSheet
    For row = 2 To 100

    I hope that made sense!

  4. #4
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690

    Default Re: Auto-Generate Number in Sequence

    This code in the WorkSheet module will resort Column "N" when a Date is entered and renumber Column "B". Note the use of LR to determine the Last Row.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 14 Then Exit Sub
    Dim LR As Long
    LR = Cells(Rows.Count, "N").End(xlUp).Row
    Range("$N$2").CurrentRegion.Sort Key1:=Range("$N$2"), Header:=xlYes
    Range("$B$2") = 1
    Range("$B$3:$B" & LR).Formula = "=$B2+1"
    End Sub
    HTH
    lenze
    Last edited by lenze; Jun 4th, 2010 at 02:30 PM. Reason: Changed Column.Count
    If you have to tell your boss you're good with Excel, you're NOT!!
    All I know about Excel I owe to my ignorance!
    Scotch: Because you don't solve great Excel problems over white wine

  5. #5
    New Member
    Join Date
    Jun 2010
    Posts
    7

    Default Re: Auto-Generate Number in Sequence

    That helps, but it still produces a problem. The number that is assigned to the row has to remain with the row as we use it as an internal reference number to identify the record. This code sorts by due date and the number changes because it references the cell above it and adds one.

    For example, if number 6 has been assigned a due date of 7/1/2010 and then the due date changes to 8/1/2010, it still has to be number 6.

    If the list looked like this:

    2 John Doe 1/1/2010
    3 Jane Smith 2/1/2010
    1 End User 3/1/2010

    the next number would have to be 4.

    Maybe this isn't doable. But thanks for your efforts! I appreciate it!

  6. #6
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690

    Default Re: Auto-Generate Number in Sequence

    Then try this
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 14 Then Exit Sub
    Dim LR As Long
    LR = Cells(Rows.Count, "N").End(xlUp).Row
    Cells(Target.Row, "B") = Application.WorksheetFunction.Max(Range("$B$2:$B" & LR)) + 1
    Range("$N$2").CurrentRegion.Sort Key1:=Range("$N$2"), Header:=xlYes
    End Sub
    If you have to tell your boss you're good with Excel, you're NOT!!
    All I know about Excel I owe to my ignorance!
    Scotch: Because you don't solve great Excel problems over white wine

  7. #7
    New Member
    Join Date
    Jun 2010
    Posts
    7

    Default Re: Auto-Generate Number in Sequence

    That's wonderful! Works like a charm.

    I only have one last little thing to ask. Is it possible for it not to sort until all of the data for the row is filled out? We have to enter data past column N - we end in column R, but sort by column N.

  8. #8
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690

    Default Re: Auto-Generate Number in Sequence

    If you change this line
    Code:
    If Target.Column <> 14 Then Exit Sub
    to
    Code:
    If Target.Column <> 18 Then Exit Sub
    the code will not fire until an entry is made in Column "R"
    lenze
    If you have to tell your boss you're good with Excel, you're NOT!!
    All I know about Excel I owe to my ignorance!
    Scotch: Because you don't solve great Excel problems over white wine

  9. #9
    New Member
    Join Date
    Jun 2010
    Posts
    7

    Default Re: Auto-Generate Number in Sequence

    Thank you, thank you, thank you! That's perfect!

  10. #10
    New Member
    Join Date
    Jun 2010
    Posts
    7

    Default Re: Auto-Generate Number in Sequence

    In using this code, I've noticed a little problem. If we move one of the rows off of our spreadsheet and say it's number 100, when we add in a new line, it repeats number 100 because it's now the highest number in the list.

    My question is, can it be changed so that once a number is used, it's never used again?

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com