Getting info from FIND/REPLACE
 

I run something like:

Sub Macro2()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
Cells.Replace What:="xxx", Replacement:="yyy"
Next
End Sub

This runs over many, many sheets. Most sheets don't have the string. Is
there any way I can return something from Replace to tell me if any
replacements were actually made in a given sheet?

I would like to break out of the loop once I get to the sheet that contained
the string in question.
--
Gary's Student

Getting info from FIND/REPLACE
 

Hi Gary

Not using Replace, but does the following help?

Sub Macro2()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
Set rng = Cells.Find(what:="xxx")
If Not rng Is Nothing Then
Cells.Replace what:="xxx", Replacement:="yyy"
MsgBox "found&replaced"
Exit Sub
End If
Next
End Sub

Richard

PS: Season's Greetings to you & your family!


Gary''s Student wrote:
I run something like:

Sub Macro2()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
Cells.Replace What:="xxx", Replacement:="yyy"
Next
End Sub

This runs over many, many sheets. Most sheets don't have the string. Is
there any way I can return something from Replace to tell me if any
replacements were actually made in a given sheet?

I would like to break out of the loop once I get to the sheet that contained
the string in question.
--
Gary's Student

Getting info from FIND/REPLACE
 

This is excellent and fully meets my needs.


You have given me something far better than a lump of coal. Merry Christmas.
--
Gary's Student


"RichardSchollar" wrote:

Hi Gary

Not using Replace, but does the following help?

Sub Macro2()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
Set rng = Cells.Find(what:="xxx")
If Not rng Is Nothing Then
Cells.Replace what:="xxx", Replacement:="yyy"
MsgBox "found&replaced"
Exit Sub
End If
Next
End Sub

Richard

PS: Season's Greetings to you & your family!


Gary''s Student wrote:
I run something like:

Sub Macro2()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
Cells.Replace What:="xxx", Replacement:="yyy"
Next
End Sub

This runs over many, many sheets. Most sheets don't have the string. Is
there any way I can return something from Replace to tell me if any
replacements were actually made in a given sheet?

I would like to break out of the loop once I get to the sheet that contained
the string in question.
--
Gary's Student

Getting info from FIND/REPLACE
 

Just wrap the 'for each ws' around this code..

Sub ReplaceIt()
Dim Ct As Long, ws As Worksheet
Set ws = ActiveSheet 'required for testing only
'formula for the whole cell
'Ct = [countif(C2:C14,"*xxx*")]
'formula for xlPart - so this is the one we use ;)
Ct = [Sumproduct(Len(C2:C14)-Len(Substitute(C2:C14,"xxx","")))]
If Ct < 0 Then
[C2:C14].Replace What:="xxx", _
Replacement:="yyy", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
MsgBox Ct & " matches found"
Else
MsgBox "No matches found for " & ws.Name
End If
End Sub


... and don't forget to replace the hard coded range above.


Regards
Robert McCurdy
"Gary''s Student" wrote in message ...
I run something like:

Sub Macro2()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
Cells.Replace What:="xxx", Replacement:="yyy"
Next
End Sub

This runs over many, many sheets. Most sheets don't have the string. Is
there any way I can return something from Replace to tell me if any
replacements were actually made in a given sheet?

I would like to break out of the loop once I get to the sheet that contained
the string in question.
--
Gary's Student

Getting info from FIND/REPLACE
 

Thank you Robert and have a merry Christmas.
--
Gary's Student


"Robert McCurdy" wrote:

Just wrap the 'for each ws' around this code..

Sub ReplaceIt()
Dim Ct As Long, ws As Worksheet
Set ws = ActiveSheet 'required for testing only
'formula for the whole cell
'Ct = [countif(C2:C14,"*xxx*")]
'formula for xlPart - so this is the one we use ;)
Ct = [Sumproduct(Len(C2:C14)-Len(Substitute(C2:C14,"xxx","")))]
If Ct < 0 Then
[C2:C14].Replace What:="xxx", _
Replacement:="yyy", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
MsgBox Ct & " matches found"
Else
MsgBox "No matches found for " & ws.Name
End If
End Sub


... and don't forget to replace the hard coded range above.


Regards
Robert McCurdy
"Gary''s Student" wrote in message ...
I run something like:

Sub Macro2()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
Cells.Replace What:="xxx", Replacement:="yyy"
Next
End Sub

This runs over many, many sheets. Most sheets don't have the string. Is
there any way I can return something from Replace to tell me if any
replacements were actually made in a given sheet?

I would like to break out of the loop once I get to the sheet that contained
the string in question.
--
Gary's Student