Searching multiple text columns for an array of keywords

[dw1011]

I am trying to search two different columns that contain text, and flag another column in the same row if one the keywords I am looking for is contained within the text of either of the two columns. If the text does contain a keyword, I want it to mark a column with an “X”, and if not, I want it to mark with “-“.

I need search for several different keywords. For example I might be interested in flagging derivations of the word “cycle”, so one of the indexes in my keyword array would be set to = “cycl” in order to be able to flag several words such as cycle, motorcycle, cycling, etc. The text columns contain several words, not just a single word.


I cannot seem to get the array to work properly. Each time I run the code, I only get cells marked “-“when many of them should be marked with an “x”. I think I may be doing something incorrectly with the vbTextCompare? I hope I have explained this well enough, and I appreciate any suggestions.


Dim keyword(1 To 10) As String

keyword(1) = "cycl"
keyword(2) = "class"
keyword(3) = "tab"
etc…


For index = 1 To 25
Do Until IsEmpty(Cells(rowNum, 1))

searchString = Cells(rowNum, 9)


'Search column 1 for keywords

If InStr(1, searchString, keyword(index), vbTextCompare > 1) Then
Cells(rowNum, 17) = "X"
Else
Cells(rowNum, 17) = "-"
End If

'Search column 2 for keywords

If InStr(1, searchString, keyword(index), vbTextCompare > 1) Then
Cells(rowNum, 17) = "X"
Else
Cells(rowNum, 17) = "-"
End If

rowNum = rowNum + 1

Loop

Next index




Thanks!

 

[ranman256]

Just use:
InStr(searchString, keyword(index))> 0

 

[dw1011]

That was a simple fix. Thanks for the tip!