Using the FIND method to find two or more values in simultaneously

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
928
Office Version
  1. 365
Platform
  1. Windows
cell B25: "My brother has a farm that grows figs and grapes. He is very wealthy."
Can you use FIND to find these two or more values, assigned to variables, rather than run two separate FINDs? Specifically, x = figs, y = grapes.
within, at the beginning or at the end of any cell text.

This is analogous to using AUTOFILTER with the xlAND operator. AUTOFILTER does work great but it is very slow
over a data set with 30,000+ rows of data. The FIND method seems to execute much faster, but I can only figure out how to make it work for
single value searches at one time. How this code might look: (this doesn't work. Its just an illustration)
Code:
Dim x, y as string
Dim c as range
X = Me.TextBox1.Value ' from a userform input
y = Me.Textbox2.value ' from a userform input
 Set c = .FIND(x AND y  LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False)
If Not C Is Nothing Then 
'do something
The result would be displayed in the userform Textbox3: "figs and grapes found in cell B25"
Here's the userform:
FIGS AND GRAPES.jpg


Thanks for anyone's help. I hope this can be done.
cr
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Try it like
Excel Formula:
Find(x & "*" & y
 
Upvote 0
Solution
Glad to help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,225,754
Messages
6,186,826
Members
453,377
Latest member
JoyousOne

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