Looping Macro to Find and Mark

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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.
 
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"
 
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
 
Actually, yours is best depending on the prior search being different. So,
looking at part and match case false would be better.
 
Don Guillett said:
I did test with Display,display,DISPLAY.
Click to expand...

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.
 
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
 
Back
Top