H
Hendy88
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
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