Posted to microsoft.public.excel.worksheet.functions
|
|
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
|