Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Big Tony
 
Posts: n/a
Default 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
  #2   Report Post  
Don Guillett
 
Posts: n/a
Default

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

"Big Tony" wrote in message
...
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



  #3   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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 ,
"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

  #4   Report Post  
Big Tony
 
Posts: n/a
Default

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

"Big Tony" wrote in message
...
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




  #5   Report Post  
Don Guillett
 
Posts: n/a
Default

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

--
Don Guillett
SalesAid Software

"JE McGimpsey" wrote in message
...
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 ,
"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





  #6   Report Post  
Don Guillett
 
Posts: n/a
Default

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

"Big Tony" wrote in message
...
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

"Big Tony" wrote in message
...
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






  #7   Report Post  
Don Guillett
 
Posts: n/a
Default

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

"JE McGimpsey" wrote in message
...
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 ,
"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



  #8   Report Post  
JE McGimpsey
 
Posts: n/a
Default

In article ,
"Don Guillett" 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.
  #9   Report Post  
Big Tony
 
Posts: n/a
Default

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

"JE McGimpsey" wrote in message
...
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 ,
"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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 12:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"