Macro to Find & Replace
 

In Excel 2002+ when you hit [CTRL-F] to do a find, it allows you the
option of searching within the active sheet or the entire workbook.
Once you start a search and you specify, "Sheet" or "Workbook", that
information is stored for later searches, so when you hit [CTRL-F]
again, the search criteria will default to what you had specified
previously. This is an issue with my macros because they were designed
based on the assumption that a find could ONLY search the active sheet.

Example:

Sheets("Sheet10").Select
Sheets("Sheet10").Range("B7:F10").Select
Selection.Replace What:="25", Replacement:="40", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Problem:

If someone is running this macro from their computer who has previously
set the Find & Replace to search within the "Workbook", this part of
the macro above will replace any instances of "25" and replaces it with
"40" within the ENTIRE workbook, when I need it somehow specified to
replace in ONLY the active sheet.

In other words, I want to be able to set the search criteria to "Sheet"
or "Workbook" through Excel VB to control where it looks. Even though
I tried specifying ActiveSheet.Replace it STILL replaces through the
entire workbook.

Is there perhaps a "Within:=" tag? Could I do something like the
following:

Selection.Replace What:="25", Replacement:="40", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, Within:=Sheet

Macro to Find & Replace
 

Give this a whirl...

dim rng as range

on error resume next
set rng = Sheets("Sheet10").Range("B7:F10")
on error goto 0
if rng is nothing then
msgbox "Sorry but the replacement failed."
else
rng.Replace _
What:=25, _
Replacement:=40, _
LookAt:=xlPart, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
end if
--
HTH...

Jim Thomlinson


" wrote:

In Excel 2002+ when you hit [CTRL-F] to do a find, it allows you the
option of searching within the active sheet or the entire workbook.
Once you start a search and you specify, "Sheet" or "Workbook", that
information is stored for later searches, so when you hit [CTRL-F]
again, the search criteria will default to what you had specified
previously. This is an issue with my macros because they were designed
based on the assumption that a find could ONLY search the active sheet.

Example:

Sheets("Sheet10").Select
Sheets("Sheet10").Range("B7:F10").Select
Selection.Replace What:="25", Replacement:="40", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Problem:

If someone is running this macro from their computer who has previously
set the Find & Replace to search within the "Workbook", this part of
the macro above will replace any instances of "25" and replaces it with
"40" within the ENTIRE workbook, when I need it somehow specified to
replace in ONLY the active sheet.

In other words, I want to be able to set the search criteria to "Sheet"
or "Workbook" through Excel VB to control where it looks. Even though
I tried specifying ActiveSheet.Replace it STILL replaces through the
entire workbook.

Is there perhaps a "Within:=" tag? Could I do something like the
following:

Selection.Replace What:="25", Replacement:="40", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, Within:=Sheet

Macro to Find & Replace
 

Hendy,

I believe that performing a Find method resets "Within" to "Sheet", so try it this way:

Dim myC As Range
Set myC = Cells.Find(What:="25", LookAt:=xlPart)
Sheets("Sheet10").Select
Sheets("Sheet10").Range("B7:F10").Select
Selection.Replace What:="25", Replacement:="40", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


HTH,
Bernie
MS Excel MVP


wrote in message ups.com...
In Excel 2002+ when you hit [CTRL-F] to do a find, it allows you the
option of searching within the active sheet or the entire workbook.
Once you start a search and you specify, "Sheet" or "Workbook", that
information is stored for later searches, so when you hit [CTRL-F]
again, the search criteria will default to what you had specified
previously. This is an issue with my macros because they were designed
based on the assumption that a find could ONLY search the active sheet.

Example:

Sheets("Sheet10").Select
Sheets("Sheet10").Range("B7:F10").Select
Selection.Replace What:="25", Replacement:="40", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Problem:

If someone is running this macro from their computer who has previously
set the Find & Replace to search within the "Workbook", this part of
the macro above will replace any instances of "25" and replaces it with
"40" within the ENTIRE workbook, when I need it somehow specified to
replace in ONLY the active sheet.

In other words, I want to be able to set the search criteria to "Sheet"
or "Workbook" through Excel VB to control where it looks. Even though
I tried specifying ActiveSheet.Replace it STILL replaces through the
entire workbook.

Is there perhaps a "Within:=" tag? Could I do something like the
following:

Selection.Replace What:="25", Replacement:="40", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, Within:=Sheet