IF Statement for checking if a cell contains a certain word

[jerryr0125]

Hi - I am looking for an example of an example of a macro that would do the following:

It would an IF statement...
* Check to see if a cell contains a certain word (say "water").
* The word can be anywhere within the cell (say cell A2)
* If the cell contains the word THEN set cell B2 to a value (say W2).

The word 'water' can be anywhere in the cell A2 (such as "water in the well" or "there is water in the cup")

Thoughts ?? thanks - jerry

 

[mumps]

Try:

Sub jerryr0125()
    If InStr(Range("A2"), "water") Then
        Range("B2").Value = Range("W2").Value
    End If
End Sub

 

[Scott T]

This assumes you do not care about case (can be upper or lower case) and you want something to happen if the word is not found. If you do not want anything to happen when the word is not found you can remove the else lines of code

Sub findword()


If InStr(UCase(Range("A2")), "WATER") Then
    Range("B2") = "W2"
Else
 'What should happen if water is not found?
 MsgBox ("water not found")
End If