Using Excel's find and searching by 2 strings

user_1474921 0
Viewed 48

I need your help,

Is it possible to search an Excel spreadsheet based on 2 given values, as opposed to just 1? similar to that of an "AND" operator?

Here's what I have so far, but I guess if your looking for a needle in a hay stack:

Str = "apples" AND "oranges"

With Sheets(xSheet)

    Set foundCell = .Cells.Find(What:=Str, After:=.Cells(1, 1), _
                    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
End With
vba excel
2 Answers

The code below returns the wanted result:

Sub TestMe()

    Dim str As String: str = "apples*oranges"
    Dim foundCell As Range

    Dim options As Variant
    options = Array(str, Split(str, "*")(1) & "*" & Split(str, "*")(0))
    Dim myVar As Variant

    For Each myVar In options
        With Worksheets(1)
            Set foundCell = .Cells.Find(What:=myVar, After:=.Cells(1, 1), _
                            LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        End With            
        If Not foundCell Is Nothing Then Exit For        
    Next myVar

    If Not foundCell Is Nothing Then
        Debug.Print foundCell.Address
    End If

End Sub

We have two different strings that need to be seaerched for: "apples*oranges" and "oranges*apples". The split and the reverse is done pretty primitively:

options = Array(str, Split(str, "*")(1) & "*" & Split(str, "*")(0))

And then, using a For Each Loop with an early Exit For, the .Find() searches for the two strings.

user_54486260

Here is a simple loop alternative:

Sub ApplesAndOranges()
    Dim r As Range, fruits As Range
    Set fruits = Nothing
    For Each r In ActiveSheet.UsedRange
        v = r.Value
        If v <> "" Then
            If InStr(v, "apples") <> 0 And InStr(v, "oranges") <> 0 Then
                If fruits Is Nothing Then
                    Set fruits = r
                Else
                    Set fruits = Union(fruits, r)
                End If
            End If
        End If
    Next
    MsgBox fruits.Address(0, 0)
End Sub

enter image description here

From the example, you see it returns all cells that contain both sub-strings.

user_24746560
Related