Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find replace within sheet or workbook
Is there a way to change the search within feild in the replace command from
"sheet" to "workbook"? ie something like: Cells.Replace What:="a", Replacement:="b", Within:=xlSheet The within option in replace doesn't seem to be covered in VB Cells.Replace yet Cells.Replace is effected by what ever the Within option is set to using the normal find/replace command in excel. The only other idea I have come up with is using sendkeys but sendkeys may not be very robust. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
find replace within sheet or workbook
Not sure where your "Within" comes from. According to help on this subject,
(may vary slightly depending on your version of XL): "expression.Replace(What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte, SearchFormat, ReplaceFormat) expression Required. An expression that returns a Range object. What Required Variant. The string you want Microsoft Excel to search for. Replacement Required Variant. The replacement string. LookAt Optional Variant. Can be one of the following XlLookAt constants: xlWhole or xlPart....." So if you want to perform the replace for all WSs in the WB, loop through the WS collection Dim WS As Worksheet For Each WS In ThisWorkbook.Worksheets WS.Cells.Replace "a", "b" Next NickHK "mcphc" wrote in message ... Is there a way to change the search within feild in the replace command from "sheet" to "workbook"? ie something like: Cells.Replace What:="a", Replacement:="b", Within:=xlSheet The within option in replace doesn't seem to be covered in VB Cells.Replace yet Cells.Replace is effected by what ever the Within option is set to using the normal find/replace command in excel. The only other idea I have come up with is using sendkeys but sendkeys may not be very robust. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
find replace within sheet or workbook
Hi
Why not do For each wks in Activeworkbook.worksheets wks.Activate Cells.Replace What:="a", Replacement:="b", Within:=xlSheet next wks The wks.Activate may not be required. regards Paul On Apr 23, 11:30 am, mcphc wrote: Is there a way to change the search within feild in the replace command from "sheet" to "workbook"? ie something like: Cells.Replace What:="a", Replacement:="b", Within:=xlSheet The within option in replace doesn't seem to be covered in VB Cells.Replace yet Cells.Replace is effected by what ever the Within option is set to using the normal find/replace command in excel. The only other idea I have come up with is using sendkeys but sendkeys may not be very robust. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
find replace within sheet or workbook
OK the within part of the statment:
Cells.Replace What:="a", Replacement:="b", Within:=xlSheet does not exist, I just used it as an example of what would be "nice" to have. If you go into normal excel and click edit|replace and have the options cexpanded in the find/replace dialog you will see a field called "Within" giving the option to replace text in the active sheet or all sheets. As far as I know this can only be changed in excel. When you use this statement in VB Cells.Replace What:="a", Replacement:="b" if "within" is set to "Workbook" all sheets will have "a" replaced by "b". So if it is set to "Workbook", doing this For each wks in Activeworkbook.worksheets wks.Activate Cells.Replace What:="a", Replacement:="b" next wks is not necessary. My problem is if you what to use this: Cells.Replace What:="a", Replacement:="b" on the active sheet only and the "Within" setting is set to "Workbook", all sheets will be changed. So how can you make sure the "Within" setting is set to "Sheet" in VB? Like I said before the closest I've come to doing this is with sendkeys " wrote: Hi Why not do For each wks in Activeworkbook.worksheets wks.Activate Cells.Replace What:="a", Replacement:="b", Within:=xlSheet next wks The wks.Activate may not be required. regards Paul On Apr 23, 11:30 am, mcphc wrote: Is there a way to change the search within feild in the replace command from "sheet" to "workbook"? ie something like: Cells.Replace What:="a", Replacement:="b", Within:=xlSheet The within option in replace doesn't seem to be covered in VB Cells.Replace yet Cells.Replace is effected by what ever the Within option is set to using the normal find/replace command in excel. The only other idea I have come up with is using sendkeys but sendkeys may not be very robust. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
find replace within sheet or workbook
There's no option in VBA that allows you to change this setting.
It's either Sendkeys or a giant leap of faith. mcphc wrote: OK the within part of the statment: Cells.Replace What:="a", Replacement:="b", Within:=xlSheet does not exist, I just used it as an example of what would be "nice" to have. If you go into normal excel and click edit|replace and have the options cexpanded in the find/replace dialog you will see a field called "Within" giving the option to replace text in the active sheet or all sheets. As far as I know this can only be changed in excel. When you use this statement in VB Cells.Replace What:="a", Replacement:="b" if "within" is set to "Workbook" all sheets will have "a" replaced by "b". So if it is set to "Workbook", doing this For each wks in Activeworkbook.worksheets wks.Activate Cells.Replace What:="a", Replacement:="b" next wks is not necessary. My problem is if you what to use this: Cells.Replace What:="a", Replacement:="b" on the active sheet only and the "Within" setting is set to "Workbook", all sheets will be changed. So how can you make sure the "Within" setting is set to "Sheet" in VB? Like I said before the closest I've come to doing this is with sendkeys " wrote: Hi Why not do For each wks in Activeworkbook.worksheets wks.Activate Cells.Replace What:="a", Replacement:="b", Within:=xlSheet next wks The wks.Activate may not be required. regards Paul On Apr 23, 11:30 am, mcphc wrote: Is there a way to change the search within feild in the replace command from "sheet" to "workbook"? ie something like: Cells.Replace What:="a", Replacement:="b", Within:=xlSheet The within option in replace doesn't seem to be covered in VB Cells.Replace yet Cells.Replace is effected by what ever the Within option is set to using the normal find/replace command in excel. The only other idea I have come up with is using sendkeys but sendkeys may not be very robust. -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
find replace within sheet or workbook
Was afraid that may be the case.
Thanks anyway "Dave Peterson" wrote: There's no option in VBA that allows you to change this setting. It's either Sendkeys or a giant leap of faith. mcphc wrote: OK the within part of the statment: Cells.Replace What:="a", Replacement:="b", Within:=xlSheet does not exist, I just used it as an example of what would be "nice" to have. If you go into normal excel and click edit|replace and have the options cexpanded in the find/replace dialog you will see a field called "Within" giving the option to replace text in the active sheet or all sheets. As far as I know this can only be changed in excel. When you use this statement in VB Cells.Replace What:="a", Replacement:="b" if "within" is set to "Workbook" all sheets will have "a" replaced by "b". So if it is set to "Workbook", doing this For each wks in Activeworkbook.worksheets wks.Activate Cells.Replace What:="a", Replacement:="b" next wks is not necessary. My problem is if you what to use this: Cells.Replace What:="a", Replacement:="b" on the active sheet only and the "Within" setting is set to "Workbook", all sheets will be changed. So how can you make sure the "Within" setting is set to "Sheet" in VB? Like I said before the closest I've come to doing this is with sendkeys " wrote: Hi Why not do For each wks in Activeworkbook.worksheets wks.Activate Cells.Replace What:="a", Replacement:="b", Within:=xlSheet next wks The wks.Activate may not be required. regards Paul On Apr 23, 11:30 am, mcphc wrote: Is there a way to change the search within feild in the replace command from "sheet" to "workbook"? ie something like: Cells.Replace What:="a", Replacement:="b", Within:=xlSheet The within option in replace doesn't seem to be covered in VB Cells.Replace yet Cells.Replace is effected by what ever the Within option is set to using the normal find/replace command in excel. The only other idea I have come up with is using sendkeys but sendkeys may not be very robust. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Set the Find and Replace, Options, 'Within' default to Workbook | Excel Discussion (Misc queries) | |||
Find and Replace within workbook - from Visual basic | Excel Programming | |||
find and replace within workbook macro | Excel Discussion (Misc queries) | |||
find and replace, within workbook | Excel Discussion (Misc queries) | |||
Find / Replace sheets vs workbook in VB | Excel Worksheet Functions |