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" wrote in message
...
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,