Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check if cells contain the word "Thailand", return "TRUE" | Excel Worksheet Functions | |||
Disabling "wrap text" neuters alt-return line feeds within cell | Excel Discussion (Misc queries) | |||
Copy column range of "single word" cells with spaces to a single c | Excel Discussion (Misc queries) | |||
cells formatted to tick when text value "Y" if or null if "N" | Excel Discussion (Misc queries) | |||
Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) |