IF cells(row,column) contain "this word" ??? Then return t..

Hi,
I'm a new user working on a database using excel, and I want to make a
search feature.
example, some cells contain "Mark Sungkar" , "Mark Hopeless" , "Mark And
Friends"

this code doesn't work :
x = Sheets("database").Cells(Rows.Count, "B").End(xlUp).Row

For z = 2 To x
If Sheets("database").Cells(z, 2) = "Mark" Then
'return the cell contains "Mark" with his full name
end if
Next z

the "x" counts cells contain data
Thank You for any help
Regards,

Dim myR As Range
Dim myC As Range
With Sheets("database")
.Cells(2, 2).CurrentRegion.AutoFilter Field:=2, Criteria1:="=*mark*"
Set myR = Intersect(.UsedRange, .Range("B2:B" & Rows.Count)) _
.SpecialCells(xlCellTypeVisible)
MsgBox "These cells: " & myR.Address & Chr(10) & "All contain the word
""Mark"""
For Each myC In myR
MsgBox myC.Address & " contains """ & myC.Text & """"
Next myC
End With

You don't say what you want to do with the Text, so I just show the values
in msgboxes.

HTH,
Bernie
MS Excel MVP


"meiftan" <meiftan.TakeThisOut@discussions.microsoft.com> wrote in message
news:DC22357D-9603-4EBB-857D-F8D8DFBD8C6D@microsoft.com...
> Hi,
> I'm a new user working on a database using excel, and I want to make a
> search feature.
> example, some cells contain "Mark Sungkar" , "Mark Hopeless" , "Mark And
> Friends"
>
> this code doesn't work :
> x = Sheets("database").Cells(Rows.Count, "B").End(xlUp).Row
>
> For z = 2 To x
> If Sheets("database").Cells(z, 2) = "Mark" Then
> 'return the cell contains "Mark" with his full name
> end if
> Next z
>
> the "x" counts cells contain data
> Thank You for any help
> Regards,