Using the FIND method with 'OR' = x = last days OR latter days, etc.

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
911
Office Version
  1. 365
Platform
  1. Windows
As title shows. Easy to use FIND with one variable value of x, such as x = "apples". What about asking Excel to
use the FIND method to FIND x = "apples" OR "oranges" OR "grapes" if all could be contained in one cell.

cell A5 = "the tree is full of white grapes and it is ready to be picked" FIND = "grapes"
cell A6 = "the basket is full of red grapes and oranges and it is ready to be stored" FIND = "grapes"
requirement to work right: x would have to have multiple values
Code:
Dim x as string, c as range
Userform1.Textbox1.value = "apples [OR] grapes [OR] oranges"  
a button would allow placing the OR operator between each value, telling Excel to FIND any value between the OR Boolean operator

x = Me.TextBox1.Value
 Set C = .FIND(X, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False)
If Not C Is Nothing Then
do something
I tried to make this as simple as possible but complete enough to understand what I need FIND to do.

I would prefer to use FIND than MATCH or anything else.

Thanks for anyone's help. Hopefully this has been done before.

cr
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
When using VBA, I prefer to use the INSTR function. If it does not find the value, it will simply return a 0.
See: InStr Function

So, to check for any one of three values, you could use something like:
VBA Code:
If (InStr(x,"apples")>0) Or (InStr(x,"oranges")>0) Or (InStr(x,"grapes")>0) Then
    'do something
End If
 
Upvote 0
It appears that the OP has not shown us the With statement that has certainly got to be there to use the syntax .Find.

Joe4's solution is good for a single value but you will have to adapt this by using a loop through every cell in the range you want to search.
 
Upvote 0
@Joe4's solution is good for a single value but you will have to adapt this by using a loop through every cell in the range you want to search.
Look a little closer, my solution is actually searching for one of three values, not one.

If they are only looking for a few values, it should work just fine (it should not be too cumbersome to use this method to search for every 5-7 options).
If they have more than 10, or a dynamically changing list, then I agree that a different method should probably be utlilized.
Unfortunately, based on the original post, we really don't have a good idea of how many values they are searching for.
 
Upvote 0
Look a little closer, my solution is actually searching for one of three values, not one.

If they are only looking for a few values, it should work just fine (it should not be too cumbersome to use this method to search for every 5-7 options).
If they have more than 10, or a dynamically changing list, then I agree that a different method should probably be utlilized.
Unfortunately, based on the original post, we really don't have a good idea of how many values they are searching for.
Thanks to both = at most, 3 values, i.e., x gets its value from Textbox1 on Userform1.
x = Textbox1.value = "apples, " "oranges'", "grapes"
I'm getting from what Joe4 siad the FIND code structure should be
Code:
X = Me.TextBox1.Value
With Worksheets("Sheet2").Range("E1:E31103") 'searches down 31,103 rows in col E
     If (InStr(x,"apples")>0) Or (InStr(x,"oranges")>0) Or (InStr(x,"grapes")>0) Then  '---> added at this location
          Set C = .FIND(X, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False)
               if Not C Is Nothing Then
                       rw = 1
                         firstAddress = C.Address
               Do
                  Worksheets("Sheet2").Select
                  C.Select
                  Range(Cells(C.row, 2), Cells(C.row, 7)).Copy Destination:=Sheets("FINDRESULT").Range("B" & rw)
                rw = rw + 1
                 Set C = .FindNext(C)
                 Loop While Not C Is Nothing And C.Address <> firstAddress
                 lastrow = Sheets("FINDRESULT").Range("B" & Rows.count).End(xlUp).row
   end if
Else
'MsgBox "No value found in col E"
lastrow = 0
End If
End With
The rest of this FIND code is typical, works flawlessly and is widely used. I just want to enhance the ability of FIND to
search for multiple values in one looping process. I placed the location of the if - end if code per Joe4 where I think it
should be to run this correctly in bold.
If we can get this to work right, it will retrieve values that would otherwise be missed with a one word or one term search.

Sincere thanks for both of your help, input and comments
cr
kingwood, Tx
 
Last edited:
Upvote 0
No, my thought was to use that in place of the FIND, not in addition to.
Simply loop through the range, and perform that check on each cell, and if it is true, then do whatever it is you need to with that cell.
 
Upvote 0
No, my thought was to use that in place of the FIND, not in addition to.
Simply loop through the range, and perform that check on each cell, and if it is true, then do whatever it is you need to with that cell.
You may be right - I'm so used to using the FIND method that sometimes I think there is no better way - my search data runs down
31,000 rows of across 4 columns. Will test and see.

cr
 
Upvote 0
Look a little closer, my solution is actually searching for one of three values, not one.
It is checking one value to see if it contains any of three values. My point was the one you made in your later post that your solution has to be used in a loop, checking one cell at a time.
 
Upvote 0
It is checking one value to see if it contains any of three values. My point was the one you made in your later post that your solution has to be used in a loop, checking one cell at a time.
Ah, I think we got our nomenclature mixed up. I thought you meant checking for "one value".
No worries...
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top