Solved

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

Posted on 2012-03-14
9
387 Views
Last Modified: 2012-03-28
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
Comment
Question by:newparadigmz
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 37720986
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
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 37720994
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37721092
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
 

Author Comment

by:newparadigmz
ID: 37721100
@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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 7

Accepted Solution

by:
Bruce Cadiz earned 250 total points
ID: 37721166
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
 
LVL 33

Expert Comment

by:Norie
ID: 37721177
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
 
LVL 7

Expert Comment

by:Bruce Cadiz
ID: 37721200
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
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 250 total points
ID: 37721236
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
 

Author Closing Comment

by:newparadigmz
ID: 37778778
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now