Find specific text within a cell

[raeannb]

So...I know I'm a noob, but I feel like I shouldn't be having so much trouble with this! I have two columns, one with a longish string, and the other with an ID number. I basically just want to know whether or not the string in the first column contains a specific phrase. For example:

<TABLE style="WIDTH: 152pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=203><COLGROUP><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5083" width=139><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 104pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl60 height=20 width=139>String</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl60 width=64>ID</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl60 height=20>xxxx WORD1 xxxxxxx</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl60>A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl60 height=20>xxxx WORD1 xxxxxxx</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl60>B</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl60 height=20>xxxxx WORD2 xx</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl60>B</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl60 height=20>xxxxx WORD2 xx</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl60>C</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl60 height=20>xxxx WORD1 xxxxxxx</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl60>D</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl60 height=20>xxxxx WORD2 xx</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl60>D</TD></TR></TBODY></TABLE>

The x's just represent miscellanious characters, while the values "WORD1" and "WORD2" are the terms I'm searching for. I basically just want to know how (with VB) I can look at the string and determine whether or not my desired word is there. I've tried this:

For i = 1 to x
If Cells(i+1, 1).Find(what:="WORD1", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False) Is Not Nothing Then
...do other stuff
End If
Next i

But it's not returning any finds (perhaps because I'm only searching in one cell? I want to do stuff with the result on a row-by-row basis, so I don't want to search the whole column at once...). Thoughts?

Thank you!!

 

[Weaver]

have a look at the =INSTR() vb function - it might be more suitable.

 

[Marcelo Branco]

Maybe

For i = 1 to x
    If [B][COLOR=blue]Not[/COLOR][/B] Cells(i+1, 1).Find(what:="WORD1", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False) [B][COLOR=blue]Is Nothing[/COLOR][/B] Then
    ...do other stuff
    End If
Next i

HTH

M.

 

[machopicho]

Try

Sub test()
For r = 1 To 6
Set c = Cells(r, 1).Find("WORD1", LookIn:=xlValues)
If Not c Is Nothing Then
i = i + 1
End If
Next
MsgBox ("This word is present " & i & " time")
End Sub

 

[raeannb]

Thanks for all of your help! INSTR() ended up working wonderfully (I also discovered that I had been referencing the wrong column pretty much the whole time I was working on this....of course). Thanks everyone!!