Get next unused sequence number in a group of numbers

Sahak

Well-known Member
Joined
Nov 10, 2006
Messages
1,012
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Hi All,

I have tried (on line) to find answer to my question but instead totally confused.
I would like to Get next unused sequence number

10223
10224
10229
10304

I need to get 10225

Yhank you in advance.
 
Last edited:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Hi All,

I have tried (on line) to find answer to my question but instead totally confused.
I would like to Get next unused sequence number

10223
10224
10229
10304

I need to get 10225
Assuming your sequence numbers never start with 0, are in sequential order and are in cells (A1:A4 for your posted example), then here is a UDF (user defined function) that will return the number you want...
Code:
Function NextSeqNum(Rng As Range) As Long
  Dim R As Long, Data As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp).Offset(1))
  For R = 2 To UBound(Data)
    If Data(R, 1) <> Data(R - 1, 1) + 1 Then
      NextSeqNum = Data(R - 1, 1) + 1
      Exit Function
    End If
  Next
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NextSeqNum just like it was a built-in Excel function. For example,

=NextSeqNum(A1:A4)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top