Searching for a words in a column from a list of words.

[Scott]

Hi,

Not sure if this is possible, but hope someone can help.

I'm trying to create a macro that will look at column A from row 10 to 100
for certain words. These words would be in a list, i.e. "Word One", "Word
Two", "Word Three", "Word Four", "Word five", "Word six", but also this is
not case sensitive, so it would look at the text if it was upper or lower
case. Could these words be in an array or not, I'm not very good at writing
macros so please bare with me.
Then the cell that contains this word would make the same row but column D
active, if you know what I mean.

Hope someone can help,
Thanks
Best regards,
Scott

 

[sivrik]

Hi Scott,

This might help you get started at least. I'm not sure
what you want to do when you find a match so now it just
displays a message box with the row number of the match.
Here goes:

Add a commandbutton to the sheet and put this code in its
click event:

Dim astrList(5) As String
Dim lngUpperBound As Long
Dim strText As String
Dim lngIndex As Long
Dim lngRow As Long

'set the upperbound of the array
'(number of words it'll contain)
lngUpperBound = 5

'add words to the array
astrList(0) = "boB"
astrList(1) = "George"
astrList(2) = "woRd"
astrList(3) = "match?"
astrList(4) = "YES"

'look for any matches in col1
For lngRow = 10 To 100
strText = UCase(ActiveSheet.Cells(lngRow, 1).Value)
For lngIndex = 0 To lngUpperBound - 1
If UCase(astrList(lngIndex)) = strText Then
'match - do what you want here
MsgBox "Found a word in row " & lngRow
End If
Next
Next

HTH,
sivrik.

 

[Scott]

Thanks Sivrik,

This works great, but one more question , is it possible to search a cell
that contain the words
i.e. instead of searching for a cell that only contains the word boB, search
for a cell that contains boB that has other characters in the cell as. For
example if a cell contains "Bob was here", then it would still pick this up.

Hope you can make sense of all this and help,
Thanks
Best regards,
Scott

Hi Scott,

This might help you get started at least. I'm not sure
what you want to do when you find a match so now it just
displays a message box with the row number of the match.
Here goes:

Add a commandbutton to the sheet and put this code in its
click event:

Dim astrList(5) As String
Dim lngUpperBound As Long
Dim strText As String
Dim lngIndex As Long
Dim lngRow As Long

'set the upperbound of the array
'(number of words it'll contain)
lngUpperBound = 5

'add words to the array
astrList(0) = "boB"
astrList(1) = "George"
astrList(2) = "woRd"
astrList(3) = "match?"
astrList(4) = "YES"

'look for any matches in col1
For lngRow = 10 To 100
strText = UCase(ActiveSheet.Cells(lngRow, 1).Value)
For lngIndex = 0 To lngUpperBound - 1
If UCase(astrList(lngIndex)) = strText Then
'match - do what you want here
MsgBox "Found a word in row " & lngRow
End If
Next
Next

HTH,
sivrik.

 

[Tom Ogilvy]

change

If UCase(astrList(lngIndex)) = strText Then

to

If Instr(1,strText,astrList(lngIndex),1) Then

 

[Scott]

Thanks very much Tom,

Maybe you can help me on my next problem.
Now that it has found the cell with this word in, I need to select the cell
in the same row in column D, and then create a SUM, in cell D120, that will
add the value of D13 to the selected cell -1 (up 1 cell from the selected
cell). I'm having a real problem with this, giving me a headache.

Hope you can help,
Thanks very much
Best regards,
Scott



change

If UCase(astrList(lngIndex)) = strText Then

to

If Instr(1,strText,astrList(lngIndex),1) Then

 

[Tom Ogilvy]

Dim astrList(5) As String
Dim lngUpperBound As Long
Dim strText As String
Dim lngIndex As Long
Dim lngRow As Long

'set the upperbound of the array
'(number of words it'll contain)
lngUpperBound = 5

'add words to the array
astrList(0) = "boB"
astrList(1) = "George"
astrList(2) = "woRd"
astrList(3) = "match?"
astrList(4) = "YES"

'look for any matches in col1
For lngRow = 10 To 100
strText = UCase(ActiveSheet.Cells(lngRow, 1).Value)
For lngIndex = 0 To lngUpperBound - 1
If Instr(1,strText,astrList(lngIndex),1) Then
'match - do what you want here
MsgBox "Found a word in row " & lngRow
Range("D120").Value = Range("D13") +Cells(lngRow-1,4).Value
End If
Next
Next

As written, the value in D120 will be overwritten each time a row is found
containing one of the words - but you didn't give any information on whether
there could be multiple matches or not and if there are, what you want to do
then.