Solved

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

Posted on 2012-03-14
9
396 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 34

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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

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
 
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 34

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 42

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

707 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