Auto-Generate Number in Sequence

[End_User]

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
<generate next number = 3> 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!

 

[lenze]

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

 

[End_User]

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!

 

[lenze]

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.

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

 

[End_User]

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!

 

[lenze]

Then try this

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

 

[End_User]

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.

 

[lenze]

If you change this line

If Target.Column <> 14 Then Exit Sub

to

If Target.Column <> 18 Then Exit Sub

the code will not fire until an entry is made in Column "R"
lenze

 

[End_User]

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

 

[End_User]

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?