VBA find row number by multiple criteria

[mrMadCat]

Hello,

I need a macro to look by rows for the row that suits 3 criteria (3 exact whole values in cells in 3 exact columns). The first row's number that suits all 3 criteria should be put into a new variable row_number.
It is also needed that this macro work fast, so looks by rows, but not first one criteria, then another in the first criteria's range and then another.
Please help.

 

[wesborland1234]

I didn't test this, but I think it should work.

Option Explicit

Sub Button1_Click()
    'Declarations
    Dim lastRow As Long
    
    Dim row_number As Long
    Dim currRow As Long
    
    Dim col1 As Integer 'The 3 column numbers where the criteria are
    Dim col2 As Integer
    Dim col3 As Integer
    
    Dim val1 As String 'the 3 criteria to match (Change to double, int or long, if these are all numeric values)
    Dim val2 As String
    Dim val3 As String
    
    'Initialize everything
    currRow = 1 'Or 2 if you have column headers
    lastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    col1 = 1
    col2 = 2
    col3 = 3
    
    val1 = "Hello" 'Change to values to match
    val2 = "World"
    val3 = "FizzBuzz"
    
    For currRow = 1 To lastRow
        If Cells(currRow, col1).Value = val1 Then
            If Cells(currRow, col2).Value = val2 Then
                If Cells(currRow, col3).Value = val3 Then
                    row_number = currRow
                    Exit For 'This ensures that only the first matching row will be assigned to row_number
                End If
            End If
        End If
    Next
    
End Sub

 

[mrMadCat]

Thank you for your reply. I finished with this:

Function FindMatch(x, y, z)
    Const FirstRow = 2
    Dim LastRow As Long
    Dim CurRow As Long
    With Worksheets("Sheet1")
        LastRow = .Range("B:C").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For CurRow = FirstRow To LastRow
            If .Range("B" & CurRow).Value = x And .Range("C" & CurRow).Value = y And .Range("D" & CurRow).Value = z Then
                FindMatch = CurRow
                Exit Function
            End If
        Next CurRow
    End With
    FindMatch = "NOT_FOUND"
End Function

Sub Write_Close()
    Row_Number = FindMatch(x, y, z)
    MsgBox Row_Number 
End Sub

 

[Rick Rothstein]

If I am not mistaken, the FindMatch function you posted can be replaced by this one (which returns 0 as the row number if there is no match)...

[table="width: 500"]
[tr]
	[td]Function FindMatch(X, Y, Z)
  Dim LastRow As Long
  LastRow = Columns("B:D").Find("*", , xlValues, , xlRows, xlPrevious).Row
  FindMatch = Application.Min(Evaluate(Replace("IF(B1:B#&""/""&C1:C#&""/""&D1:D#=""" & X & "/" & Y & "/" & Z & """,ROW(B1:B#),"""")", "#", LastRow)))
End Function[/td]
[/tr]
[/table]

 

[Jimmy1772]

@Rick Rothstein it looks like you are using a small function to return a row? I'm not entirely certain on how this works though.

    Function findrow(ProcessNo, Equipment)
    
    Dim Range1 As Range
    Dim Range2 As Range
    Set Range1 = DestTable.ListColumns("No.").DataBodyRange.Cells
    Set Range2 = DestTable.ListColumns("Equipment").DataBodyRange.Cells
    
    Dim lastrow As Long
    lastrow = Range1.Find("*", , xlValues, , xlRows, xlPrevious).row
    findrow = Application.Min(Evaluate(Replace("IF(Range1 & ""/"" & Range2=""" & ProcessNo & " / " & Equipment & """,row(range1),"""")", "#", lastrow)))
    
  
 
    End Function

The findrow line is throwing error 2029, and I'm not sure if I have the wrong number of quotations or if this is only compatible with worksheets.