Link to home
Create AccountLog in
Avatar of newparadigmz
newparadigmzFlag for United States of America

asked on

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

Is there a way to use Find to look in two cells instead of just one?

like if D26 = "Apple" and E26 = "Pear"

Set Range= .Find(what"ApplePear")
Avatar of Norie
Norie

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

Open 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.
Avatar of newparadigmz

ASKER

@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"
ASKER CERTIFIED SOLUTION
Avatar of Bruce Cadiz
Bruce Cadiz
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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

Open 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

Open in new window

SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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))