Solved

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

Posted on 2012-03-14
9
388 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

831 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