Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Replace - Replace with Blank Space | Excel Discussion (Misc queries) | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
Using Find and Replace to replace " in a macro | Excel Programming | |||
Replace method - cannot find any data to replace | Excel Programming | |||
Code to search and replace info on modules | Excel Programming |