Checking if a cell contains a certain word

[Ruky]

Hello,
I have cells A1 to A50 and there are different types and lengths of text in each cell. But some cells have a word in common (say the word is "apple").

I want to loop through each cell to check for the word apple and if there's a match another set of commands are activated in the VBA. I am just not aware of any function in VBA that will allow me to check for the word I want to find the range of cells.

Does anyone know if such a function exist? If yes, what's the syntax for this.

Thanks,

 

[njimack]

The VBA function is Instr...

Sub a()
Dim r As Range

For Each r In Range("A1:A50")
    If InStr(1, r, "apple", 1) > 0 Then
        'your code here
    End If
Next r
End Sub

 

[HalfAce]

Assuming you won't be searching for "apple" every time, perhaps this slight tweak to njimack's suggestion...

Sub aa()
Dim SearchTerm As String, r As Range
SearchTerm = InputBox("Enter the string to search for...", "Search Term")
For Each r In Range("A1:A50")
    If InStr(r, SearchTerm) Then
        'your code here
        MsgBox "Found " & SearchTerm & " in " & r.Value
    End If
Next r
End Sub

Hope it helps.

EDIT:
If you want to make this so the inputbox entry is not case sensitive, add this line to the very top of your code module:
Option Compare Text

 

[Ruky]

Works great. Thanks!!! :D