View Full Version : Solved: check entire cell for a word
lwolfe
06-21-2006, 07:35 AM
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.:banghead:
Jacob Hilderbrand
06-21-2006, 07:49 AM
Try this:
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
Joe Heym
06-21-2006, 07:50 AM
use the find function if the result of the function is >0 then you have found the text your looking for
snicho
06-21-2006, 08:48 AM
The instr function works well for this situation:
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
lwolfe
06-21-2006, 10:47 AM
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
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
lucas
06-21-2006, 12:34 PM
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
06-21-2006, 12:43 PM
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
06-21-2006, 01:43 PM
Fixed moved the check point from the Do down to the loop
old
Do until is empty(Activecell.value)
code:
loop
New
Do
code:
Loop until isEmpty(activecell.value)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.