# Find next available number in 2 columns on different sheets

#### Lewiskj

##### New Member
I have a workbook for memberships, with 2 sheets "Members Due" and Members Paid".
When a member pays, their row is moved from members due to members paid.

Row A on both sheets containes their membership number (I like to give them their same number every year if the stay)

If someone doesn't renew for some time and a new member joins i would like to find the next available number.

So what I am after is some VBA code that says:

Look in "Members paid" row A and "Members Due" row A and return the next consicutive missing number.

Hope this makes sense.

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### DanteAmor

##### Well-known Member
Try this

Code:
``````Sub New_Number()
x = Sheets("Members Due").Range("A" & Rows.Count).End(xlUp) + 1
y = Sheets("Members Paid").Range("A" & Rows.Count).End(xlUp) + 1
MsgBox "Next number " & WorksheetFunction.Max(x, y)
End Sub``````

#### Lewiskj

##### New Member
Try this

Code:
``````Sub New_Number()
x = Sheets("Members Due").Range("A" & Rows.Count).End(xlUp) + 1
y = Sheets("Members Paid").Range("A" & Rows.Count).End(xlUp) + 1
MsgBox "Next number " & WorksheetFunction.Max(x, y)
End Sub``````

Thanks but, this is returning the largest available number.

What I am looking for is the next missing number.

If in column A on one sheet I have 1,2,3

and column A on the other sheet I have 5,6,7
I'm looking to find 4 which is the lowest available number.

#### DanteAmor

##### Well-known Member
Try this

Code:
``````Sub New_Number()
Dim sh1 As Worksheet, sh2 As Worksheet, b As Range, c As Range

Set sh1 = Sheets("Members Due")
Set sh2 = Sheets("Members Paid")

For i = WorksheetFunction.Min(sh1.Range("A:A"), sh2.Range("A:A")) To WorksheetFunction.Max(sh1.Range("A:A"), sh2.Range("A:A")) + 1
Set b = sh1.Range("A:A").Find(i, LookIn:=xlValues, lookat:=xlWhole)
If b Is Nothing Then
Set c = sh2.Range("A:A").Find(i, LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox "Next number " & i
Exit For
End If
End If
Next
End Sub``````

Last edited:

#### Lewiskj

##### New Member

Try this

Code:
``````Sub New_Number()
Dim sh1 As Worksheet, sh2 As Worksheet, b As Range, c As Range

Set sh1 = Sheets("Members Due")
Set sh2 = Sheets("Members Paid")

For i = WorksheetFunction.Min(sh1.Range("A:A"), sh2.Range("A:A")) To WorksheetFunction.Max(sh1.Range("A:A"), sh2.Range("A:A")) + 1
Set b = sh1.Range("A:A").Find(i, LookIn:=xlValues, lookat:=xlWhole)
If b Is Nothing Then
Set c = sh2.Range("A:A").Find(i, LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox "Next number " & i
Exit For
End If
End If
Next
End Sub``````

It's still ignoring the missing numbers and coming back with the largest number +1

#### Lewiskj

##### New Member
Sorry, My fault. the last code works perfectly I forgot to remove some numbers from the sequence for it to find.

Thanks for taking the time to help I greatly appreciate it and will learn from what you wrote.

Last edited:

Replies
11
Views
108
Replies
5
Views
70
Replies
0
Views
60
Replies
5
Views
113
Replies
9
Views
191