Find next available number in 2 columns on different sheets

Lewiskj

New Member
Joined
Feb 17, 2019
Messages
30
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.
 

Some videos you may like

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
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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
Joined
Feb 17, 2019
Messages
30
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
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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
Joined
Feb 17, 2019
Messages
30

ADVERTISEMENT

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
Joined
Feb 17, 2019
Messages
30
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.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,983
Messages
5,526,056
Members
409,682
Latest member
HisHailo

This Week's Hot Topics

Top