PDA

View Full Version : Partial cell contents matching



Poo_Drop
12-13-2007, 11:06 AM
I want to sort through a column of names and highlight each cell in the column that contains that name. I have written a formula that will let me do this, but only if the whole cell matches. For example,

Cell(1,1) = Bill
Cell(2,1) = Bill / Jane

My current code will match (1,1) but not (2,1). I hope that makes sense.

Here's my current code:
Sub Find_Yourself()
Dim sRet As String
sRet = InputBox("Please enter your name", "Name")

If sRet Is Nothing Then
Exit Sub

Dim rFoundCell As Range

Set rFoundCell = Range("B1")
For i = 1 To WorksheetFunction.CountIf(Columns(2), sRet)
Set rFoundCell = Columns(2).Find(What:=sRet, After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
With rFoundCell
.Interior.ColorIndex = 35
End With
Next i

Cells(6, 2).Select
End Sub
Thanks for any help! I've been working on this for about two days now.

Bob Phillips
12-13-2007, 11:56 AM
Sub Find_Yourself()
Dim sRet As String
Dim mpFirst As String

sRet = InputBox("Please enter your name", "Name")

If sRet = "" Then Exit Sub

Dim rFoundCell As Range

With Columns(2)

Set rFoundCell = .Find( _
What:=sRet, After:=Range("B1"), _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not rFoundCell Is Nothing Then
mpFirst = rFoundCell.Address
Do
rFoundCell.Interior.ColorIndex = 35
Set rFoundCell = .FindNext(rFoundCell)
Loop While Not rFoundCell Is Nothing And rFoundCell.Address <> mpFirst
End If
End With

Cells(6, 2).Select
End Sub

Poo_Drop
12-13-2007, 11:58 AM
Genious! That does exactly what I want. Thank you so much!

Poo_Drop
12-13-2007, 12:51 PM
Thanks a lot for you help, but you please explain this line to me. There are so many keywords jammed together its like reading java
Loop While Not rFoundCell Is Nothing And rFoundCell.Address <> mpFirst
Thanks!

Bob Phillips
12-13-2007, 03:07 PM
It is just looping until either we have found no other instance of the value, or we have found the first one again.