Searching a cell for certain contents

[Derek Johansen]

I would like to check to see if a cell contains a certain string within the
whole cell. For example, if column B has a lot of data, and one of the
entries is "The cat in the hat" I would like a macro to search column B for
the word hat. I don't care what happens when it finds it at the moment, as I
believe i can establish that myself, I just need a command to search within a
cell for a certain word. I've tried using the InStr command a bit, but to no
avail!

Any help would be much appreciated! Thanks!

 

[dmoney]

Columns("B:B").Select
Selection.Find(What:="hat", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

 

[Derek Johansen]

Okay, I lied, I can't get it to do what I want with the data. It should be
fairly simple, all I want to do is edit column J according to what is in
Column B. If Column B contains "BCI" i need to change column J to "I-Joist."
This should be an easy addition I would think, but I've only been working
with VBA for a couple days now, so I'm not very proficient...

 

[dmoney]

ActiveCell.Offset(0, 8).Value = "enjoy"

 

[Derek Johansen]

I will test this when I get back to the office and let you know how it goes:
i appreciate the timely responses, thank you!

 

[Derek Johansen]

Mr. Money,
I am getting a run-time error with this code:

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

It says "Object variable or With block variable not set."
Any suggestions?

 

[Derek Johansen]

Okay, I have established that this run-time error only occurs when the string
does not occur in any cells. How do I avoid this, because I want to run the
macro on every worksheet i import, but it can't run-time error just because
there is no "BCI" found in column B