Excel VBA 2007, use "Find" function to look in 2 cells

You can use Find to look in 2 cells but you can't look for 2 values with a single Find.

If you want to check D26 and E26 contain 'Apple' and 'Pear' respectively you could concatenate them.

If Range("D26").Value & Range("E26").Value = "ApplePear" Then

Select allOpen in new window

newparadigmz,

Do you mean you want to look for two different values anywhere in a worksheet, or do you want to look for certain values in two certain cells?

You cannot use a find statement which has a "What" split into two cells. The "What" has to be in one cell either wholly or partially.

@imnorie

That's backwards to what I need.
I need to find the position of two cells where one is Apple and the other is Pear and they are next to each other.

@aebea

I need to look for "two different values anywhere in a worksheet"

Not sure how it's 'backwards', you did specify specific cells and didn't mention a range.

Anyway, if you want to find Apple in a range and Pear in a range you need to use Find twice.

Dim rngFnd1 As Range
Dim rngFnd2 As Range

     Set rngFnd1 = Range("A1:A100").Find("Apple")
     Set rngFnd2 = Range("A1:A100").Find("Pear")

    If Not(rngFnd1 Is Nothing)  And Not(rngFnd2 Is Nothing) Then

               MsgBox "Apple found in " & rngFnd1.Address               
               MsgBox "Pear found in " & rngFnd2.Address
    End If

Select allOpen in new window

Conversely you could reverse the loops and do this as well

Sub myTermSearch()
Dim mySearch
Dim c As Range
mySearch = Array("Apple", "Pear")
Debug.Print "Searching Range: "; ActiveSheet.UsedRange.Address
For Each c In ActiveSheet.UsedRange
    For Each term In mySearch
        If InStr(1, c.Value, term) > 0 Then
            Debug.Print c.Address; "="; c.Value
        End If
    Next term
Next c
End Sub

Select allOpen in new window

thanks

i went with something like this instead, which i came up

find (apple)
find (pear)

range =  WorksheetFunction.Sum(Range(Cells(somethingOffset.Row, apple.Column).Address, Cells(somethingOffset.Row, pear.Column).Address))