Solved

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

Posted on 2012-03-14
9
385 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

708 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

17 Experts available now in Live!

Get 1:1 Help Now