auto generate sequential numbers

[MCE2014]

Im trying to create a job number generater.

I have the following code:

Dim NextNum As Long

NextNum = Application.WorksheetFunction.Max(Sheet1.UsedRange.Columns(1))

Me.txtjobno.Value = NextNum
Me.txtjobno.Enabled = False

Anybody know how I can get the program to remember the last number issued and continue on from the next sequential number even after being closed and reopened???

Also I want it to start at 1 and not at 0

 

[RickXL]

Hi, presumably you will be writing the numbers to column 1?

If so, won't the previous highest number be saved there? You just need to find it using the MAX statement again.

As for starting at 1 there are several choices:
You could enter the first one manually or you could add an IF statement to your logic which says if the number is 0 (or blank) then add 1 to it.

 

[Teeroy]

NextNum should be incremented by 1 over the maximum found so this would even work if there was no data. Change to:

NextNum = Application.WorksheetFunction.Max(Sheet1.UsedRange.Columns(1)) + 1

A note to remember is that Columns(1) is referenced to the UsedRange, not the sheet. If you happened to have Column A empty with data starting in Column B then that would return the Column B maximum.