Macro idendifying keywords

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sandeman - 14 Mar 2006 14:51 GMT

Excel gurus...

I have a spreadsheet that in Column C has descriptive data all in text
form. The data might say something like "Paul is an Italian basketball
player."

What I need is for a macro to recognize the word "Italian" and place
the word "Italy" in Column E to the right of the data column. Then I'd
like to have another macro to recognize "basketball" and place
"basketball" in Column F.

However, sometimes there won't be information for Column E, in which
case the data in Column F needs to move over to Column E if E is blank.

And it needs to run for the entire set of rows.

Is this possible? Many thanks in advance.

Sandeman

Signature

Sandeman

Reply to this Message

Tom Ogilvy - 14 Mar 2006 15:15 GMT

Sub ProcessWords()
Dim v as Variant, v1 as Variant
Dim rng as Range, cell as Range
Dim i as Long, col as long
v = Array("Italian","BasketBall")
v1 = Array("Italy","BasketBall)
set rng = Range(Cells(1,3),Cells(rows.count,3).End(xlup))
for each cell in rng
col = 4
for i = lbound(v) to ubound(v)
if Instr(1,cell.Value,v(i).vbTextCompare) then
cells(cell.row,col).Value = v1(i)
col = col + 1
end if
Next
Next
End Sub

Modify v to include all the key words and v1 to include the words you want
placed to the right if a keyword is found

Code is untested and may contain typos, but should suggest a workable approach

Signature

Regards,
Tom Ogilvy

> Excel gurus...
>
[quoted text clipped - 15 lines]
>
> Sandeman

Reply to this Message

Sandeman - 14 Mar 2006 15:32 GMT

Very cool Tom. Thank you. The If, Then line is not working out and I'm
not sure what's wrong with it. Any ideas?

Tom Ogilvy Wrote:

> Sub ProcessWords()
> Dim v as Variant, v1 as Variant
[quoted text clipped - 57 lines]
> > View this thread:
> http://www.excelforum.com/showthread.php?threadid=522153

Signature

Sandeman

Reply to this Message

Tom Ogilvy - 14 Mar 2006 17:17 GMT

As I said, it my contain typos - in this case, it contained two. Here is a
tested version (based on your description).

Sub ProcessWords()
Dim v As Variant, v1 As Variant
Dim rng As Range, cell As Range
Dim i As Long, col As Long
v = Array("Italian", "BasketBall")
v1 = Array("Italy", "BasketBall")
Set rng = Range(Cells(1, 3), _
Cells(Rows.Count, 3).End(xlUp))
For Each cell In rng
col = 4
For i = LBound(v) To UBound(v)
If InStr(1, cell.Value, v(i), _
vbTextCompare) Then
Cells(cell.Row, col).Value = v1(i)
col = col + 1
End If
Next
Next
End Sub

> Very cool Tom. Thank you. The If, Then line is not working out and I'm
> not sure what's wrong with it. Any ideas?

Reply to this Message

Sandeman - 15 Mar 2006 09:12 GMT

Super. How you and others acquire this expertise, I'll never know.
This is working very well. Now I've run into an unforeseen issue. If
in the description I search for the word "Italian," the word Italy is
placed in a cell to the right. If I also look for the word "Italy,"
the word Italy is then placed two cells to the right. That means
"Italy" has been listed twice. Would it be possible to add code that
states if "Italy" has been found once, skip the next variations of the
word "Italy" and move on to the next group (e.g. basketball)? There
should be come work around perhaps?

Tom Ogilvy Wrote:

> As I said, it my contain typos - in this case, it contained two. Here
> is a
[quoted text clipped - 23 lines]
> I'm
> > not sure what's wrong with it. Any ideas?

Signature

Sandeman

Reply to this Message

Sandeman - 15 Mar 2006 17:05 GMT

Bump for help. Thank you

Reply to this Message

Sandeman - 15 Mar 2006 17:05 GMT

Bump for help. Thank you

Reply to this Message

Sandeman - 15 Mar 2006 21:47 GMT

One more bump

Reply to this Message

Sandeman - 29 Mar 2006 14:39 GMT

Looks as if I need to make some changes to the macro structure. What I
need the following macro to do is the following. If the macro looks at
a description and identifies the word "Italian", it places the word
"Italy" in a cell three columns over (column is "nation"). If it also
identifies the word "basketball," it places the word "Basketball" in a
cell four columns over (column is for "sport"). There are multiple
identifiers I want to use, but I can set that up as long as I get the
gist on how to edit the following to do this. Thanks!

Sub ProcessWords()
Dim v As Variant, v1 As Variant
Dim rng As Range, cell As Range
Dim i As Long, col As Long
v = Array("Italian", "BasketBall")
v1 = Array("Italy", "BasketBall")
Set rng = Range(Cells(1, 3), _
Cells(Rows.Count, 3).End(xlUp))
For Each cell In rng
col = 4
For i = LBound(v) To UBound(v)
If InStr(1, cell.Value, v(i), _
vbTextCompare) Then
Cells(cell.Row, col).Value = v1(i)
col = col + 1
End If
Next
Next
End Sub

Signature

Sandeman

Reply to this Message

Sandeman - 30 Mar 2006 08:49 GMT

Bump for help.

Signature

Sandeman

Reply to this Message