• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

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")
0
newparadigmz
Asked:
newparadigmz
  • 2
  • 2
  • 2
  • +3
2 Solutions
 
NorieCommented:
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

0
 
Anthony BerenguelCommented:
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?
0
 
Saqib Husain, SyedEngineerCommented:
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.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
newparadigmzAuthor Commented:
@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"
0
 
Bruce CadizQuality SpecialistCommented:
Here's an example of how to do it without using Find

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

Open in new window


Debug Output:

Searching Range: $A$3:$A$6
SearchTerm: Apple
$A$5=Apple
SearchTerm: Pear
$A$6=Pear
0
 
NorieCommented:
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

0
 
Bruce CadizQuality SpecialistCommented:
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

0
 
dlmilleCommented:
My two cents ;)

This code searches the active workbook's active sheet for all occurrances of Apple (the cell contains the full word "Apple") then checks to see if the adjacent (on the right) cell has "Pear" as its value.  If it does, it adds it to a range that contains the list of Apple/Pear matches.

At the end, you are prompted with the Apple/Pear ranges in the worksheet.  That's contained in the variable rFound.

PS - if you only want to search column D for Apples, then look at E for the Pear match, just change line 14 to:
    Set rFind = wks.Range("D:D").Find(what:="Apple", LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext)

And line 26 to:

            Set rFind = wks.Range("D:D").Find(what:="Apple", after:=rFind, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext)

Here's your code (which looks for Apple/Pear matches anywhere - not just column D:E)

Option Explicit

Sub findApplePear()
Dim wkb As Workbook
Dim wks As Worksheet
Dim rFind As Range
Dim rFound As Range
Dim firstAddress As String


    Set wkb = ActiveWorkbook
    Set wks = wkb.ActiveSheet
    
    Set rFind = wks.Cells.Find(what:="Apple", LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext)
    If Not rFind Is Nothing Then
        firstAddress = rFind.Address
        Do
        
            If rFind.Offset(, 1).Value = "Pear" Then
                If rFound Is Nothing Then
                    Set rFound = Union(rFind, rFind.Offset(, 1))
                Else
                    Set rFound = Union(rFind, rFind.Offset(, 1), rFound)
                End If
            End If
            Set rFind = wks.Cells.Find(what:="Apple", after:=rFind, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext)
        Loop While Not rFind Is Nothing And firstAddress <> rFind.Address
    
        If Not rFound Is Nothing Then
            MsgBox "The range where Apple/Pear are next to each other, Apple then Pear on the right is: " & rFound.Address
        Else
            MsgBox "Found Apples but no matching Pears"
        End If
    Else
        MsgBox "No Apples found"
    End If
    
            
End Sub

Open in new window


See attached demonstration workbook.

Dave
ApplePear-r1.xls
0
 
newparadigmzAuthor Commented:
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))
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now