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

[chazrab]

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

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

 

[Joe4]

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:

If (InStr(x,"apples")>0) Or (InStr(x,"oranges")>0) Or (InStr(x,"grapes")>0) Then
    'do something
End If

 

[6StringJazzer]

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.

 

[Joe4]

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

 

[chazrab]

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

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

 

[Joe4]

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.

 

[chazrab]

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

 

[6StringJazzer]

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.

 

[Joe4]

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