Find all values in a range that fit a certain criteria and return each row VBA

user_10451617 0
Viewed 1489

I'm looking for some code to search through a range and return the row number of each cell in that range that fits a certain criteria and list those rows.

Previously I have only been requiring the first value so have been using the code:

Dim Criteria1 As Single
Dim Criteria2 As Single
Dim Required As Integer
Dim Range1 As Range
Dim GearNeg1 As Integer

SetColumn = 24
Set Range1 = Sheets("X").Range("A2:BT72").Columns(SetColumn).Cells
Criteria1 = Sheets("X").Range("P111").Value
Criteria2 = Sheets("X").Range("Q111").Value

For Each Cell In Range1

    If Cell.Value < Criteria1 And Cell.Value > Criteria2 Then

        Required = Cell.row

        Exit For

    End If
Next

I've been playing around with adding a for loop to return all the row values for the values that meet the criteria to a list. However am struggling and can only seem to hit the first value found every time.

for-loop vba excel search
2 Answers

This is a minimal example, returning the rows of the Range A1:A30, which are with X in them:

Public Sub TestMe()

    Dim rowValues As String
    Dim myCell As Range

    For Each myCell In Worksheets(1).Range("A1:A30")
        If myCell = "X" Then
            rowValues = Trim(rowValues & " " & myCell.Row)
        End If
    Next myCell

    Debug.Print rowValues

End Sub

The return is dones through concatenation here: rowValues = Trim(rowValues & " " & myCell.Row), the Trim() is needed to reduce the first " " on the first concatenation.

user_54486260

You could read the range into an array, loop the array and concatenate qualifying rows into a string. I use the fact you start from row 2, and that I am using a 1 based array to determine the row i.e. I add 1 to the value of i which is the index in the array where the qualifying value is.

You could also use

required = required & "," & i + ws.Range("A2:BT72").Row - LBound(arr)

VBA:

Option Explicit
Public Sub test()
    Dim criteria1 As Single, criteria2 As Single, required As String
    Dim arr(), ws As Worksheet, setColumn As Long, i As Long

    Set ws = ThisWorkbook.Worksheets("X")
    setColumn = 24
    arr = Application.Transpose(ws.Range("A2:BT72").Columns(setColumn).Value)

    criteria1 = ws.Range("P111").Value
    criteria2 = ws.Range("Q111").Value

    For i = LBound(arr) To UBound(arr)
        If arr(i) < criteria1 And arr(i) > criteria2 Then
            required = required & "," & i + 1
        End If
    Next

    required = Replace$(required, ",", vbNullString, 1, 1)

    Debug.Print required
End Sub
user_62412350
Related