Finding text within a cell

M

Mike

Hi. I have a loop where I would like to search for the
text, "Item:" within the cells. For instance, I would
want it to find "Item: 7201".

What command line should I use to find cells
containing "Item:".

Thanks,
Mike.
 
T

Trev

Try something like this
assumin data is in column 1 ("A")
strSearch = "Item:"
for I = Toprow to bottomrow
If left(cells(I,1),5) =strSearch then
Msgbox "Found on Row " & I '
Exit For
End if
Next

Trev
 
R

Ron de Bruin

Hi Mike

You must use
LookAt:=xlPart in your code

Here is a example that look in column A for Item and color the cell

Sub Color_cells_in_Column()
Dim FirstAddress As String
Dim myArr As Variant
Dim rng As Range
Dim I As Long

Application.ScreenUpdating = False
myArr = Array("Item")
'You can also use more values in the Array
'myArr = Array("ron", "dave")

With Sheets("Sheet1").Range("A:A")

.Interior.ColorIndex = xlColorIndexNone
'change the fill color to "no fill" in all cells

For I = LBound(myArr) To UBound(myArr)
Set rng = .Find(What:=myArr(I), _
After:=Range("A" & Rows.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
'if you use LookIn:=xlValues it will also work with a
'a formula cell that evaluates to "ron"

If Not rng Is Nothing Then
FirstAddress = rng.Address
Do
rng.Interior.ColorIndex = 3
'make the cell red
Set rng = .FindNext(rng)
Loop While Not rng Is Nothing And rng.Address <> FirstAddress
End If
Next I
End With
Application.ScreenUpdating = True
End Sub
 
B

Bob Phillips

Hi Mike,

On Error Resume Next
oCell = Cells.Find("Item")
If Not oCell Is Nothing Then
msgbox "Item found at " & oCell.Address
End If
On Error Goto 0

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top