How to write VBA code to search for a value through a database in Excel

[l2urhwhc4u]

Hello,

I really need help with this problem I'm facing. I'm writing a recipe guide program using VBA form. I got to the part where the user can type in an ingredient and I need the program to search through the Ingredients column in my data excel file.
For example, the user wants to look for recipe that contains ORANGE JUICE in the ingredient list.
If the ingredient is found in a cell, say E5, then I need to have a code that would be able to pick up the name of the recipe that contains the ingredients (name would be in D5). I attached a picture of how my data base look.

Also, since I have to use VBA form, I'm currently using a list box to output all of the results that the program can find from the database.

I tried to record macro and using the Find option in Excel but the result didn't turn out very well. The code I found
Range("D2:D19100").Select
Selection.Find(What:="Orange Juice", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

need to have a Selection.FindNext(After:=ActiveCell).Activate after every line and there's no way the program can look at the column before it for the name and output it in my list box.

I really need help with this problem.

Thank you for your time reading this.

[Guido Geurs]

dear,

Attached is a excel macro which find the ingredients in a list like yours.

If you have still problems, please let me know.

br,

[lorong]

uhm let me ask first...
1.) are you using ADODC,DAO or ODBC?
2.) is your problem displaying the content of your table with specified values?

here is a sample using ADODC
first put adodc
next use jet engine to connect to database after that code this

with adodc1
.recordsource = "select from (your table name) where (field name) = ' " & (textbox or other ways to compare) & " ' "
'/note the formate for where is single cote(') then double cote(") amperes AND(&) then your field to be searched(i.e. text1.text) then do the reverse amperes AND(&) double cote(") single cote(')
.refresh
if .BOF = false then
.recordset.movefirst
end if
while not .recordset.EOF then

list1.additem = .recordset.fields(the field you want to display)

wend
end with


thats all.. email me if you still got probs using adodc ([e-mail])

[jaffindale]

@Guido Geurs
YOU-ARE-AWESOME!!! THANKS!
I didn't knew the INSTR function.
You saved me a lot of work.