Find next available number in 2 columns on different sheets

[Lewiskj]

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.

 

[DanteAmor]

Try this

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]

Try this

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]

Try this

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

 

[Lewiskj]

Try this

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]

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]

I'm glad to help you. Thanks for the feedback.