Looping Macro to Find and Mark

[Big Tony]

Hi All,

I am looking for a macro that will search a variable length column to find a
specific embedded character string (regardless of case, i.e., "Display",
"display", "DISPLAY" are the same) and then color code the found cell (any
color) and place an "X" in the cell one column to the right of the found cell
in the same row. Any help would be appreciated.

Wish list: I would like to set the macro in motion to search all
worksheets. Search column will always be the same on each worksheet. Will
only search one column.


Thank you,
Tony

 

[Don Guillett]

You could have looked in vba help to find the basic code for findnext and
then modified as below.

Sub searchandmark()
For Each ws In Worksheets
With ws.Columns(6)
Set c = .Find("display", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
c.Offset(0, 1) = "x"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next ws
End Sub

 

[JE McGimpsey]

Note that meeting the OP's requirements for case insensitivity and
matching an embedded string aren't guaranteed using the provided code.
One must explicitly set the LookAt and MatchCase arguments or settings
of previous searches could give the wrong results. Better:

Public Sub searchandmark()
Dim ws As Worksheet
Dim c As Range
Dim firstAddress As String
For Each ws In Worksheets
With ws.Columns(6)
Set c = .Find( _
What:="display", _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
c.Offset(0, 1) = "x"
Set c = .FindNext(c)
Loop While c.Address <> firstAddress
End If
End With
Next ws
End Sub


Note also that the "While Not c Is Nothing" in MS's example code:

Loop While Not c Is Nothing And c.Address <> firstAddress

is superfluous - this loop only executes if c is Not Nothing and it
continues to loop around the search range - if c was found once,
FindNext will find it again.

 

[Big Tony]

Thanks Don. That works great. Next I tried removing the "For" clause to run
macro in one worksheet and keep getting error 424. I also removed "Next ws"

 

[Don Guillett]

JE.
I did test with Display,display,DISPLAY.

 

[Don Guillett]

Sub searchandmarkoneWS()

with columns(6)
Set c = .Find("display", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
c.Offset(0, 1) = "x"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End Sub

 

[Don Guillett]

Actually, yours is best depending on the prior search being different. So,
looking at part and match case false would be better.

 

[JE McGimpsey]

I did test with Display,display,DISPLAY.

Hmm... Since the LookAt and LookIn arguments do get set, I didn't look
at the MatchCase argument - but on my current version, too, MatchCase
seems to be reset to False unless explicitly specified.

Don't know whether that's a bug in XL or an error in Help, but I'll bug
it to MS.

 

[Big Tony]

Thanks to both of you. Don I used your one sheet option and included the
missing "End With". I got the desired results using both your code and JE's.

Thanks again,
Tony