Excel value is used-CodePudding

I have a little issue, and can't figure it out.

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

Listed like the picture, i need it to figure out witch numbers are used. 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 has different start numbers.

Best regards.

Hope someone can help

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

Have tried this code, but only get it to get the first line. So i guess something is wrong.

CodePudding user response：

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.