Find a specific string in a cell

**Jeroen1000** · 10-12-2009, 06:06 AM

Hi eveyone

I have absolutely no idea how to get starting on this one. I've got a long string in cell B1.

At some point there is the word "oms:SomeString," (without the quotes). I need to know whether SomeString is somewhere in the active sheet or not (the workbook running the VBA-code is not the active sheet)

I can't just compare the cell B1 because it contains multiple words. Any hints are very welcome.

Cheers,

Jeroen

**Jeroen1000** · 10-12-2009, 07:40 AM

I've been doing some thinking. This piece of code searches for the string

  Cells.Find(What:="SomeStringl", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

But I can't get it to return true of false. But it has given me more ideas on how to search the forums. I bet this question has been answered before.

**Richard Schollar** · 10-12-2009, 07:59 AM

Hi

Set a reference to the found cell and then check whether the found cell actually exists (ie whether the searched for term was found):

Dim rFound As Range
Set rFound = Nothing
Set rFound = Activesheet.Cells.Find(What:="SomeStringl", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)       


'note no .Activate on the end of the above statement!!

'now check if the term was found:

If Not rFound Is Nothing then   'if it isn't nothing then it's something ie it was found
  'search term found
Else
  'term not found
End If

Make sense?

Richard[/code]