Range.Find Multiple Criteria

[moonlight22]

Hi,

Is there a way to use the Range.Find to look for multiple criteria instead of just one value, and not withing a consecutive range, but multiple columns across rows?

I'm assuming it can be accomplished by using the "AND" but I don't know where to place it in the function.

In my case I have 4 values in one tab (Controlsheet) in the range I12:I15. These values are found in another tab (Datasheet), withing the following columns: B, F, I & J.

My current formula looks like this....but this was when i was only looking for one value in M:M

    Rng = Sheets("DataSheet").Range("M:M").Find(CriteriaValue, Sheets("DataSheet").Range("M1"), xlValues, xlWhole)

Any help is much much appreciated!

 

[AlphaFrog]

The find method doesn't support multiple criteria in its search. You could .Find one criteria in a loop and test the other three criteria for each match found until all four match.

    Dim Found As Range, Firstfound As String
    Dim rngSearch As Range
    Dim Criteria As Variant
    
    Set rngSearch = Sheets("DataSheet").Range("B:B")
    
    Criteria = Sheets("ControlSheet").Range("I12:I15").Value
        
    Set Found = rngSearch.Find(What:=Criteria(1, 1), _
                               LookIn:=xlValues, _
                               LookAt:=xlWhole, _
                               SearchOrder:=xlByRows, _
                               SearchDirection:=xlNext, _
                               MatchCase:=False)
        
    If Not Found Is Nothing Then
        
        Firstfound = Found.Address
        
        Do
            If Found.EntireRow.Range("F1").Value = Criteria(2, 1) And _
               Found.EntireRow.Range("I1").Value = Criteria(3, 1) And _
               Found.EntireRow.Range("J1").Value = Criteria(4, 1) Then Exit Do 'Match found
            
            Set Found = rngSearch.FindNext(After:=Found)
            If Found.Address = Firstfound Then Set Found = Nothing
            
        Loop Until Found Is Nothing
        
    End If
    
    If Not Found Is Nothing Then
        Application.Goto Found.EntireRow
    Else
        MsgBox "Nothing matched all four criteria. ", , "No Match Found"
    End If

 

[tenriquez39]

The find method doesn't support multiple criteria in its search. You could .Find one criteria in a loop and test the other three criteria for each match found until all four match.

    Dim Found As Range, Firstfound As String
    Dim rngSearch As Range
    Dim Criteria As Variant
   
    Set rngSearch = Sheets("DataSheet").Range("B:B")
   
    Criteria = Sheets("ControlSheet").Range("I12:I15").Value
       
    Set Found = rngSearch.Find(What:=Criteria(1, 1), _
                               LookIn:=xlValues, _
                               LookAt:=xlWhole, _
                               SearchOrder:=xlByRows, _
                               SearchDirection:=xlNext, _
                               MatchCase:=False)
       
    If Not Found Is Nothing Then
       
        Firstfound = Found.Address
       
        Do
            If Found.EntireRow.Range("F1").Value = Criteria(2, 1) And _
               Found.EntireRow.Range("I1").Value = Criteria(3, 1) And _
               Found.EntireRow.Range("J1").Value = Criteria(4, 1) Then Exit Do 'Match found
           
            Set Found = rngSearch.FindNext(After:=Found)
            If Found.Address = Firstfound Then Set Found = Nothing
           
        Loop Until Found Is Nothing
       
    End If
   
    If Not Found Is Nothing Then
        Application.Goto Found.EntireRow
    Else
        MsgBox "Nothing matched all four criteria. ", , "No Match Found"
    End If

WONDERFUL !!! THANKS