How can I search for words in a list with a macro?

  • Thread starter Thread starter Ahreum
  • Start date Start date
A

Ahreum

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!
 
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!
Click to expand...

"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."
 
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 -
Click to expand...
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 -
 
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.
Click to expand...


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)))
 
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 -
Click to expand...


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

Thank you!
 
Back
Top