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" <anonymous@discussions.microsoft.com> wrote in message
news:061e01c50932$89b20fd0$a401280a@phx.gbl...
> 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" <anonymous@discussions.microsoft.com> wrote
in message
>news:061e01c50932$89b20fd0$a401280a@phx.gbl...
>> 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" <anonymous@discussions.microsoft.com> wrote in message
news:067601c50938$76c68f30$a401280a@phx.gbl...
> 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" <anonymous@discussions.microsoft.com> wrote
> in message
>>news:061e01c50932$89b20fd0$a401280a@phx.gbl...
>>> 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
>>>>
>>>>.
>>>>
>>
>>
>>.
>>