|
Pesky little problem with no solution?
Hi all,
I posted this before Christmas but didn't receive any answers so I thought I'd try again. In the EDIT, REPLACE ... / OPTIONS screen, is a little dropdown box that tells EXCEL to search the sheet or workbook. I run a macro that does a find / replace of text in the workbook but the scope of the search is limited by this dropdown. In other words, I cannot seem to over rule this manual dropdown entry via code. Is there a VB property that is associated with this drop down box? I've looked really hard for this and have been unable to locate it. All the best and hoping for a solution, Robert |
Record a macro while you vary these options, and examine the generated code.
Jerry Bony Pony wrote: Hi all, I posted this before Christmas but didn't receive any answers so I thought I'd try again. In the EDIT, REPLACE ... / OPTIONS screen, is a little dropdown box that tells EXCEL to search the sheet or workbook. I run a macro that does a find / replace of text in the workbook but the scope of the search is limited by this dropdown. In other words, I cannot seem to over rule this manual dropdown entry via code. Is there a VB property that is associated with this drop down box? I've looked really hard for this and have been unable to locate it. All the best and hoping for a solution, Robert |
Hi,
Thanks but like I said, I have done all the obvious things. Regards, Robert -----Original Message----- Record a macro while you vary these options, and examine the generated code. Jerry Bony Pony wrote: Hi all, I posted this before Christmas but didn't receive any answers so I thought I'd try again. In the EDIT, REPLACE ... / OPTIONS screen, is a little dropdown box that tells EXCEL to search the sheet or workbook. I run a macro that does a find / replace of text in the workbook but the scope of the search is limited by this dropdown. In other words, I cannot seem to over rule this manual dropdown entry via code. Is there a VB property that is associated with this drop down box? I've looked really hard for this and have been unable to locate it. All the best and hoping for a solution, Robert . |
Hi Robert
there is no VBA setting to change from current sheet to all sheets, you'll need to cycle through all sheets in the workbook in your code if you want to replace throughout the whole workbook., e.g. Sub ReplWholeWB() Dim Wks As Worksheet For Each Wks In Worksheets Wks.Cells.Replace What:="AAAAA", Replacement:="CCCCC", _ searchOrder:=xlByRows, MatchCase:=False Next Wks End Sub --- Hope this helps Cheers JulieD "Bony Pony" wrote in message ... Hi, Thanks but like I said, I have done all the obvious things. Regards, Robert -----Original Message----- Record a macro while you vary these options, and examine the generated code. Jerry Bony Pony wrote: Hi all, I posted this before Christmas but didn't receive any answers so I thought I'd try again. In the EDIT, REPLACE ... / OPTIONS screen, is a little dropdown box that tells EXCEL to search the sheet or workbook. I run a macro that does a find / replace of text in the workbook but the scope of the search is limited by this dropdown. In other words, I cannot seem to over rule this manual dropdown entry via code. Is there a VB property that is associated with this drop down box? I've looked really hard for this and have been unable to locate it. All the best and hoping for a solution, Robert . |
Hi Julie,
Thank you for your help. Dang!! You'd think that with all of the useless properties in Excel VB, you think they'd replicate useful full menu functionality. Ah well ... Thanks very much for confirming my own findings though. Have a great day, Robert -----Original Message----- Hi Robert there is no VBA setting to change from current sheet to all sheets, you'll need to cycle through all sheets in the workbook in your code if you want to replace throughout the whole workbook., e.g. Sub ReplWholeWB() Dim Wks As Worksheet For Each Wks In Worksheets Wks.Cells.Replace What:="AAAAA", Replacement:="CCCCC", _ searchOrder:=xlByRows, MatchCase:=False Next Wks End Sub --- Hope this helps Cheers JulieD "Bony Pony" wrote in message ... Hi, Thanks but like I said, I have done all the obvious things. Regards, Robert -----Original Message----- Record a macro while you vary these options, and examine the generated code. Jerry Bony Pony wrote: Hi all, I posted this before Christmas but didn't receive any answers so I thought I'd try again. In the EDIT, REPLACE ... / OPTIONS screen, is a little dropdown box that tells EXCEL to search the sheet or workbook. I run a macro that does a find / replace of text in the workbook but the scope of the search is limited by this dropdown. In other words, I cannot seem to over rule this manual dropdown entry via code. Is there a VB property that is associated with this drop down box? I've looked really hard for this and have been unable to locate it. All the best and hoping for a solution, Robert . . |
Hi Robert
i'm not overly surprised, AFAIK the functionality to search the whole workbook was only introduced to the find / replace screen in ver 2002 - so maybe in the next version they'll remember to "update" the VBA code. Cheers JulieD "Bony Pony" wrote in message ... Hi Julie, Thank you for your help. Dang!! You'd think that with all of the useless properties in Excel VB, you think they'd replicate useful full menu functionality. Ah well ... Thanks very much for confirming my own findings though. Have a great day, Robert -----Original Message----- Hi Robert there is no VBA setting to change from current sheet to all sheets, you'll need to cycle through all sheets in the workbook in your code if you want to replace throughout the whole workbook., e.g. Sub ReplWholeWB() Dim Wks As Worksheet For Each Wks In Worksheets Wks.Cells.Replace What:="AAAAA", Replacement:="CCCCC", _ searchOrder:=xlByRows, MatchCase:=False Next Wks End Sub --- Hope this helps Cheers JulieD "Bony Pony" wrote in message ... Hi, Thanks but like I said, I have done all the obvious things. Regards, Robert -----Original Message----- Record a macro while you vary these options, and examine the generated code. Jerry Bony Pony wrote: Hi all, I posted this before Christmas but didn't receive any answers so I thought I'd try again. In the EDIT, REPLACE ... / OPTIONS screen, is a little dropdown box that tells EXCEL to search the sheet or workbook. I run a macro that does a find / replace of text in the workbook but the scope of the search is limited by this dropdown. In other words, I cannot seem to over rule this manual dropdown entry via code. Is there a VB property that is associated with this drop down box? I've looked really hard for this and have been unable to locate it. All the best and hoping for a solution, Robert . . |
| All times are GMT +1. The time now is 11:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com