Hi:

I need a looping macro that goes through a column to find strings that
contain certain words.

For example) Column G contains a list of person and business names,
and I'd like to identify records that contain the words,
"corporation," "technology," "furniture," "financial," "supermarket,"
etc., all at once. Since Column G has thousands and thousands of rows,
I can't run a query for each word -- so it's really important that I
can specify many different words on query run.

When a match is found (let's say cell G5), then the corresponding H
column (cell H5) will have a "1" or some other distinction for the
match.

Thanks!

On Oct 15, 3:52*pm, Ahreum <ahreum.sk...@gmail.com> wrote:
> Hi:
>
> I need a looping macro that goes through a column to find strings that
> contain certain words.
>
> For example) Column G contains a list of person and business names,
> and I'd like to identify records that contain the words,
> "corporation," "technology," "furniture," "financial," "supermarket,"
> etc., all at once. Since Column G has thousands and thousands of rows,
> I can't run a query for each word -- so it's really important that I
> can specify many different words on query run.
>
> When a match is found (let's say cell G5), then the corresponding H
> column (cell H5) will have a "1" or some other distinction for the
> match.
>
> Thanks!

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

On Oct 15, 7:06*pm, Ron Rosenfeld <r...@nospam.net> wrote:
> On Fri, 15 Oct 2010 13:52:30 -0700 (PDT), Ahreum
>
>
>
>
>
> <ahreum.sk...@gmail.com> wrote:
> >Hi:
>
> >I need a looping macro that goes through a column to find strings that
> >contain certain words.
>
> >For example) Column G contains a list of person and business names,
> >and I'd like to identify records that contain the words,
> >"corporation," "technology," "furniture," "financial," "supermarket,"
> >etc., all at once. Since Column G has thousands and thousands of rows,
> >I can't run a query for each word -- so it's really important that I
> >can specify many different words on query run.
>
> >When a match is found (let's say cell G5), then the corresponding H
> >column (cell H5) will have a "1" or some other distinction for the
> >match.
>
> >Thanks!
>
> You can do this without a macro. *For example:
>
> H1:
> =OR(ISNUMBER(SEARCH({"corporation","technology","furniture","financial","su*permarket"},G1)))
> will return TRUE if any of the words in the list are in G1, and FALSE
> if not.
>
> You could use this in an IF statement to flag the positives however
> you like. *Or you could use it in a conditional formatting equation to
> highlight the cells with those values.- Hide quoted text -
>
Option Explicit
Sub findwords()
Dim c As Range
For Each c In Selection
If InStr(c, "corporation") > 0 Or _
InStr(c, "technology") > 0 Or _
InStr(c, "furniture") > 0 Or _
InStr(c, "furniture") > 0 Or _
InStr(c, "supermarket") > 0 Then
MsgBox c.Row
End If
Next c
End Sub> - Show quoted text -

On Oct 15, 8:06*pm, Ron Rosenfeld <r...@nospam.net> wrote:
> On Fri, 15 Oct 2010 13:52:30 -0700 (PDT), Ahreum
>
>
>
> <ahreum.sk...@gmail.com> wrote:
> >Hi:
>
> >I need a loopingmacrothat goes through a column to find strings that
> >contain certainwords.
>
> >For example) Column G contains alistof person and business names,
> >and I'd like to identify records that contain thewords,
> >"corporation," "technology," "furniture," "financial," "supermarket,"
> >etc., all at once. Since Column G has thousands and thousands of rows,
> >I can't run a query for each word -- so it's really important that I
> >can specify many differentwordson query run.
>
> >When a match is found (let's say cell G5), then the corresponding H
> >column (cell H5) will have a "1" or some other distinction for the
> >match.
>
> >Thanks!
>
> You can do this without amacro. *For example:
>
> H1:
> =OR(ISNUMBER(SEARCH({"corporation","technology","furniture","financial","supermarket"},G1)))
> will return TRUE if any of thewordsin thelistare in G1, and FALSE
> if not.
>
> You could use this in an IF statement to flag the positives however
> you like. *Or you could use it in a conditional formatting equation to
> highlight the cells with those values.


Thanks Ron!

Made some little adjustments, and it works great!
I use the =isnumber(search()) function all the time, but I didn't
realize you can specify more than one word.
Anyway, here it is for anyone who'd like to see:

=OR(ISNUMBER(SEARCH({"*corp*","*tech*","*f
urniture*","*finan*","*market*"},A2)))

On Oct 17, 1:12*pm, Don Guillett Excel MVP <dguille...@austin.rr.com>
wrote:
> On Oct 15, 7:06*pm, Ron Rosenfeld <r...@nospam.net> wrote:
>
> > On Fri, 15 Oct 2010 13:52:30 -0700 (PDT), Ahreum
>
> > <ahreum.sk...@gmail.com> wrote:
> > >Hi:
>
> > >I need a loopingmacrothat goes through a column to find strings that
> > >contain certainwords.
>
> > >For example) Column G contains alistof person and business names,
> > >and I'd like to identify records that contain thewords,
> > >"corporation," "technology," "furniture," "financial," "supermarket,"
> > >etc., all at once. Since Column G has thousands and thousands of rows,
> > >I can't run a query for each word -- so it's really important that I
> > >can specify many differentwordson query run.
>
> > >When a match is found (let's say cell G5), then the corresponding H
> > >column (cell H5) will have a "1" or some other distinction for the
> > >match.
>
> > >Thanks!
>
> > You can do this without amacro. *For example:
>
> > H1:
> > =OR(ISNUMBER(SEARCH({"corporation","technology","furniture","financial","su*permarket"},G1)))
> > will return TRUE if any of thewordsin thelistare in G1, and FALSE
> > if not.
>
> > You could use this in an IF statement to flag the positives however
> > you like. *Or you could use it in a conditional formatting equation to
> > highlight the cells with those values.- Hide quoted text -
>
> Option Explicit
> Sub findwords()
> Dim c As Range
> For Each c In Selection
> If InStr(c, "corporation") > 0 Or _
> InStr(c, "technology") > 0 Or _
> InStr(c, "furniture") > 0 Or _
> InStr(c, "furniture") > 0 Or _
> InStr(c, "supermarket") > 0 Then
> MsgBox c.Row
> End If
> Next c
> End Sub> - Show quoted text -


Hi Don:
This is very helpful.
Instead of MsgBox c.Row, how do I make the results write to the next
column?

Thank you!