Solved: check entire cell for a word

[lwolfe]

I need to check a cell for a word such as "StruckBy". the Problem is that in some of the cells the word will be followed by other text. I can find the cells where it is the only word but not when followed by other text.

[Jacob Hilderbrand]

Try this:

[vba]
Option Explicit

Sub FindCells()

Dim StrFind As String
Dim FirstAddress As String
Dim Cel As Range
Dim RngSearch As Range

StrFind = "StruckBy"
Set RngSearch = ActiveSheet.Cells
Set Cel = RngSearch.Find(What:=StrFind, LookIn:=xlValues, _
LookAt:=xlPart, MatchCase:=False)
If Not Cel Is Nothing Then
FirstAddress = Cel.Address
Do
MsgBox Cel.Address
Set Cel = RngSearch.FindNext(Cel)
Loop While Not Cel Is Nothing And Cel.Address <> FirstAddress
End If

Set Cel = Nothing
Set RngSearch = Nothing

End Sub
[/vba]

[Joe Heym]

use the find function if the result of the function is >0 then you have found the text your looking for

[snicho]

The instr function works well for this situation:

[vba]Sub stringsearch()

Dim SearchString, SearchChar, MyPos
SearchString = ActiveCell.Value ' String to search in.
SearchChar = "StruckBy"

' A textual comparison starting at position 1
If InStr(1, SearchString, SearchChar, 1) > 0 Then
MsgBox "Cell contains search text"
Else
MsgBox "Search text not found in cell"
End If

End Sub

[/vba]

[lwolfe]

Thanks everyone this was my first posting and it was a great help.
I had to modify Jakes code to allow me loop through a list of items and get a count each item, Works great.

Thanks Again.
Lee J. Wolfe


[vba]
Sub FindCells()

Dim StrFind As String
Dim FirstAddress As String
Dim Cel As Range
Dim RngSearch As Range
Dim test As String
Dim Value As Integer

Range("V36").Select
Do
test = ActiveCell.Text
StrFind = test
Set RngSearch = ActiveSheet.Cells

Select Case StrFind
Case StrFind

Set Cel = RngSearch.Find(What:=StrFind, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)

If Not Cel Is Nothing Then
FirstAddress = Cel.Address
Value = -1

Do
Set Cel = RngSearch.FindNext(Cel)
Value = Value + 1
Loop While Not Cel Is Nothing And Cel.Address <> FirstAddress

End If

ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = Value

Set Cel = Nothing
Set RngSearch = Nothing
End Select

ActiveCell.Offset(1, -1).Activate

Loop Until IsEmpty(ActiveCell.Value)

End Sub
[/vba]

[lucas]

Hello Lee,
Glad you got it working. I enclosed your code in vba tags for easier reading. Just highlight your code when posting and hit the vba button.

Also to mark your thread solved use the thread tools at the top of the page

[lwolfe]

after running my code I have found a bug it continues down to the next cell and enters a count of 2 for the empty cell?? So I will let you know what I figure out.

[lwolfe]

Fixed moved the check point from the Do down to the loop

old
[VBA]
Do until is empty(Activecell.value)
code:
loop
[/VBA]
New
[VBA]
Do
code:
Loop until isEmpty(activecell.value)
[/VBA]