Looping Macro to Find and Mark

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

--
Don Guillett
SalesAid Software
[email protected]
"Big Tony" <[email protected]> wrote in message
news:[email protected]...
> 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

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.


In article <[email protected]>,
"Don Guillett" <[email protected]> wrote:

> 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

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" wrote:

> 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
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Big Tony" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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

--
Don Guillett
SalesAid Software
[email protected]
"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
> In article <[email protected]>,
> "Don Guillett" <[email protected]> wrote:
>
> > 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

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
SalesAid Software
[email protected]
"Big Tony" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > "Big Tony" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

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

--
Don Guillett
SalesAid Software
[email protected]
"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
> In article <[email protected]>,
> "Don Guillett" <[email protected]> wrote:
>
> > 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

In article <#[email protected]>,
"Don Guillett" <[email protected]> wrote:

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

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

"Don Guillett" wrote:

> Actually, yours is best depending on the prior search being different. So,
> looking at part and match case false would be better.
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "JE McGimpsey" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> >
> > In article <[email protected]>,
> > "Don Guillett" <[email protected]> wrote:
> >
> > > 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
>
>
>