Macro: find a set of words in a string

[pennep]

Hi,
I am writing a macro which can find if a cell in Column B contains the words in column A. If it does, it writes result in column C.
eg. in the following table, B3 has word 'not' so 'Found' is displayed in C3.
Goal: [TABLE="width: 304"]
<tbody>[TR]
[TD][TABLE="width: 368"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Look for [/TD]
[TD]Look into [/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]not[/TD]
[TD]Sunday is hot.[/TD]
[TD]Not found[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]cold[/TD]
[TD]Monday is not hot.[/TD]
[TD]Found[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]winter[/TD]
[TD]Tuesday is not cold.[/TD]
[TD]Found[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]like[/TD]
[TD]It is raining.[/TD]
[TD]Not found[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]It is neither hot nor cold.[/TD]
[TD]Found[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]cold[/TD]
[TD]Found[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]not[/TD]
[TD]Found[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I manage to write a code that does the task only if cells exactly match. eg B7 and B8.

Sub FindExistance()
Dim ws As Excel.Worksheet
Set ws = ActiveSheet 'assign active sheet to ws
Set objRange = ws.Range("A1").EntireColumn '
i = 1
Do Until ws.Cells(i, 2).Value = ""
strName = ws.Cells(i, 2).Value
Set objSearch = objRange.Find(strName)
If objSearch Is Nothing Then
ws.Cells(i, 3).Value = "Not Found"
Else
ws.Cells(i, 3).Value = "Found"
End If
i = i + 1
Loop
End Sub

I couldn't manage to get it work if only one word match the string. I tried using InStr but couldn't get it done.
Thanks in advance.
P

 

[Marcelo Branco]

You can try a formula instead of a macro

Put in C2
=IF(ISNUMBER(LOOKUP(9.99E+307,SEARCH($A$2:$A$5,B2))),"Found","Not Found")
copy down

Hope this helps

M.

 

[pennep]

Thank you for the suggestion, but my intention is not to use a formula. My ultimate aim is to list out the words in a separate file and use the script to find those words in another file.
The given example is only to simplify the task.
Anyone?!?