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

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,

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

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,