Find words in cells

[mps]

I have a worksheet in one column is a list of book titles. In other columns I have other information linked to the specific titles eg. ISBN, price, number in stock.

I am trying to write a VBA macro to allow the user to search for titles containing specific words eg. searching for all titles containing the words 'Day' and 'Rain' in them. The macro would then copy all these book titles and their relevant information ie. ISBNs, prices etc. to a different worksheet.
Can anyone give me help in doing this? The VBA Find function if seaching for a string containing "Day Rain" would only find titles with the words in that specific order ie. it wouldn't find a title of "A Day in the Rain". How can I write a macro that would find the title "A Day in the Rain" from the string "Day Rain"???

 

[Greg Truby]

You want to use Excel's built-in tools if you can. And in this cause AutoFilter works nicely. Try doing this interactively with Autofilter and the CONTAINS operator while having the macro recorder turned on and you'll get some good clues on syntax.

Also, which version of Excel are you using?

 

[mps]

Thanks for the reply. I am using excel 2010 and the users of the worksheet will use the same. I am still a bit of novice when it comes to excel and am picking things up as I go along when I need them - know any good links to instructions for learning about autofilter?

 

[Greg Truby]

know any good links to instructions for learning about autofilter?

I'm sure you can find some with Google. But in all honesty, I'd just get me a nice block of data (no blank rows, no blank columns) that has some columns with text values, some columns with numeric data, and at least one column of dates. Then turn on FILTER (they renamed it from "Autofilter" to "Filter" in XL2007) and just play and play and play. Lots of super cool abilities. Truly a tool you should familiarize yourself with. If you like books, then check out Bill's store (Bill is "MrExcel" and a heck of a nice guy).

 

[mps]

Thanks. I just had a quick play and you are right it is a very cool feature I must get to know! I also had a quick look at the VBA reference in Excel. Correct me if I am wrong but you can only specify 2 criteria to seach for. If the user wanted to search for more that two words from a title this wouldn't work. I really need the user to be able to supply any number of words from the title up to and including a complete title and then copy only the titles containing those words. I don't think that autofilter can do this can it???

 

[mikerickson]

The AutoFilter is easier to access, but AdvancedFilter will do what you want.

 

[Greg Truby]

As Mike has pointed out, it's starting to sound like Advanced Filter is the better tool for this. You can use the same field name more than once to filter for multiple terms on the same field.

Let's say you had this list.

<TABLE style="WIDTH: 114pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=152><COLGROUP><COL style="WIDTH: 114pt; mso-width-source: userset; mso-width-alt: 5558" width=152><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; WIDTH: 114pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; HEIGHT: 15pt; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" height=20 width=152>Name</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20>gone with the wind</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>gone in sixty seconds</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20>gone baby gone</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>inherit the wind</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20>wind chill</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>wind in the willows</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20>is the wind gone?</TD></TR></TBODY></TABLE>

Criteria for any filim containing "gone"

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #da9694 0.5pt solid; BACKGROUND-COLOR: #c0504d; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #da9694 0.5pt solid; BORDER-RIGHT: #da9694 0.5pt solid" class=xl66 height=20 width=64>Name</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #da9694 0.5pt solid; BORDER-LEFT: #da9694 0.5pt solid; BACKGROUND-COLOR: #f2dcdb; HEIGHT: 15pt; BORDER-TOP: #da9694 0.5pt solid; BORDER-RIGHT: #da9694 0.5pt solid" class=xl65 height=20>*gone*</TD></TR></TBODY></TABLE>

for any film containing "wind"

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #da9694 0.5pt solid; BACKGROUND-COLOR: #c0504d; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #da9694 0.5pt solid; BORDER-RIGHT: #da9694 0.5pt solid" class=xl66 height=20 width=64>Name</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #da9694 0.5pt solid; BORDER-LEFT: #da9694 0.5pt solid; BACKGROUND-COLOR: #f2dcdb; HEIGHT: 15pt; BORDER-TOP: #da9694 0.5pt solid; BORDER-RIGHT: #da9694 0.5pt solid" class=xl65 height=20>*wind*</TD></TR></TBODY></TABLE>

for any file containing both "gone" and "wind" but any order

<TABLE style="WIDTH: 98pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=130><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2413" width=66><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #da9694 0.5pt solid; BACKGROUND-COLOR: #c0504d; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #da9694 0.5pt solid; BORDER-RIGHT: #da9694 0.5pt solid" class=xl66 height=20 width=64>Name</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #da9694; BACKGROUND-COLOR: #c0504d; WIDTH: 50pt; BORDER-TOP: #da9694 0.5pt solid; BORDER-RIGHT: #da9694 0.5pt solid" class=xl66 width=66>Name</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #da9694 0.5pt solid; BORDER-LEFT: #da9694 0.5pt solid; BACKGROUND-COLOR: #f2dcdb; HEIGHT: 15pt; BORDER-TOP: #da9694 0.5pt solid; BORDER-RIGHT: #da9694 0.5pt solid" class=xl65 height=20>*gone*</TD><TD style="BORDER-BOTTOM: #da9694 0.5pt solid; BORDER-LEFT: #da9694; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #da9694 0.5pt solid; BORDER-RIGHT: #da9694 0.5pt solid" class=xl65>*wind*</TD></TR></TBODY></TABLE>

for any file containing "gone" and "wind" but gone must come first:

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #da9694 0.5pt solid; BACKGROUND-COLOR: #c0504d; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #da9694 0.5pt solid; BORDER-RIGHT: #da9694 0.5pt solid" class=xl66 height=20 width=64>Name</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #da9694 0.5pt solid; BORDER-LEFT: #da9694 0.5pt solid; BACKGROUND-COLOR: #f2dcdb; HEIGHT: 15pt; BORDER-TOP: #da9694 0.5pt solid; BORDER-RIGHT: #da9694 0.5pt solid" class=xl65 height=20>*gone*wind*</TD></TR></TBODY></TABLE>

for any films containing "gone" OR "wind" (all of them)

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #da9694 0.5pt solid; BORDER-LEFT: #da9694 0.5pt solid; WIDTH: 48pt; FONT-FAMILY: Calibri; BACKGROUND: #c0504d; HEIGHT: 15pt; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #da9694 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #da9694 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #C0504D none" class=xl66 height=20 width=64>Name</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #da9694 0.5pt solid; BORDER-LEFT: #da9694 0.5pt solid; BACKGROUND-COLOR: #f2dcdb; HEIGHT: 15pt; BORDER-TOP: #da9694 0.5pt solid; BORDER-RIGHT: #da9694 0.5pt solid" class=xl65 height=20>*gone*</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #da9694 0.5pt solid; BORDER-LEFT: #da9694 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #da9694 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>*wind*</TD></TR></TBODY></TABLE>

 

[mps]

Thanks guys that helps greatly. In terms of VBA I am thinking that I will use the 'split' function to split the string into individual words in an array and use this array to build my criteria for the advanced filter on a worksheet. I can use the advanced filter with this criteria to produce the list of titles containing the requested words. From my playing with the advanced filter so far (not in VBA yet) it seems as if when choosing the option to copy the results of the filtered list, its destination has to be on the same worksheet as the list you are filtering (not sure if this is the same in VBA?). This means that I can copy the results I need to the other required worksheet.

 

[Greg Truby]

If you observe the message box, you'll note that it says you can only copy to the active worksheet. If you activate the target sheet first, you can then copy to it.

 

[mps]

Cheers Greg - I think I must need new glasses I totally missed that emphasis on the message box about the 'active' sheet. I will experiment again!!