Determine next unused value in a list

Question

I have a long list of numbers. I need to figure out which one is used and which is free.

The numbers are placed in 12 other sheets, so I have to search them all.

I need this to generate next free tooling number, the different tools have different start numbers.

Rcount = 0
Row = 2
Set ran = Sheets("Alle").Range("A2:A1600")

For Each cell In ran
    
    FindString = Sheets("All").Cells(Row, 1).Value
    
    'MsgBox ("Test: " & FindString)
    If Trim(FindString) <> "" Then
        For Each sh In ActiveWorkbook.Worksheets
            With sh.Range("A:A")
                Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
                If Not Rng Is Nothing Then
                MsgBox ("Row: " & Row)
                Exit Sub
            End If
        End With
    Next
End If
Row = Row + 1
Next cell

This code only gets the first line.

Answer 1

Welcome to SO.

When you say used numbers I understand you mean those numbers where adjacent column is an X (or other FindString)

You can create an array of all rows numbers where 0 means there is no FindString in that row or the row number if it is.

Just as example, I got 4 worksheets:

My code just returns the row number, in each worksheet, if there is an X in B column:

Sub test()
Dim DataRows As Variant
Dim i As Long
Dim rngSource As Range
Dim FindString As String
Dim wk As Worksheet


For Each wk In Sheets(Array("Sheet1", "Sheet3", "Sheet4")) 'array of 12 sheets
    
    'maybe you need to change parameters in each sheet. You can do it with Select Case
    ' I'm using the same parameters for all worksheets

    FindString = """X""" 'so it saves the quotes too
    Set rngSource = wk.Range("B1:B25")
    
    DataRows = Evaluate("ROW(" & rngSource.Address(, , , True) & ")*(--(" & rngSource.Address(, , , True) & "=" & FindString & "))")
    
    For i = LBound(DataRows) To UBound(DataRows) Step 1
        If DataRows(i, 1) > 0 Then Debug.Print wk.Name, DataRows(i, 1), wk.Range("A" & DataRows(i, 1)).Value 'show only row numbers of used numbers and used numbers
    Next i
    
    Erase DataRows

Next wk

End Sub

Output:

You just need to adapt some parts to get exactly what you want.

The tricky part here is this line of code:

DataRows = Evaluate("ROW(" & rngSource.Address(, , , True) & ")*(--(" & rngSource.Address(, , , True) & "=" & FindString & "))")

Evaluate will evaluate a function, like you type it in Excel, so in the first loop it will be like this:

ROW(B1:B25)*(--(B1:25="X"))

This is a tricky function that returns an array of numbers. (B1:25="X") it's just an array of 1 and 0 if the cell is equal to X or not. Then multiply that array by the row numbers. If the cell is not X then rownumber*0=0 that means it's empty. In other case rownumber*1=rownumber so you got just zeros and row numbers of used numbers :)

Then you save that into an array (arrays work faster) and just get the output if the value in the array is not zero.

DataRows is a bidimensional array by the way, you need an index row i in the code and a column index (always 1 in the code).

Rest of code is easy to understand.