Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro: Find and replace | Excel Discussion (Misc queries) | |||
Help! - Lost function in Find & Replace! | Excel Discussion (Misc queries) | |||
Find and Replace | Excel Discussion (Misc queries) | |||
Find and replace of word causes change of font formatting | New Users to Excel | |||
find and replace | Excel Discussion (Misc queries) |