Hi all,
If I record a macro that does a find / replace on a sheet
and then record another one that does a Find / Replace on
the entire workbook, they are identical!!

How can I code a Find / Replace statement that will
always perform on all sheets in my workbook?

e.g.
This is the one for the sheet:
Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Cells.Replace What:="aaaa", Replacement:="bbbbb",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

..... and this is the one for the workbook:

Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Cells.Replace What:="aaaa", Replacement:="bbbbb",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False


The only way I can make it work, is to set the switch in
the Find / Replace Dialog box on the EDIT Menu bar.
Anyone know how to switch that on in the macro??

Thanks for any help ...

Best regards,
Robert

I am using Excel ver 11 by the way


-----Original Message-----
Hi all,
If I record a macro that does a find / replace on a
sheet
and then record another one that does a Find / Replace
on
the entire workbook, they are identical!!

How can I code a Find / Replace statement that will
always perform on all sheets in my workbook?

e.g.
This is the one for the sheet:
Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Cells.Replace What:="aaaa", Replacement:="bbbbb",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

..... and this is the one for the workbook:

Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Cells.Replace What:="aaaa", Replacement:="bbbbb",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False


The only way I can make it work, is to set the switch in
the Find / Replace Dialog box on the EDIT Menu bar.
Anyone know how to switch that on in the macro??

Thanks for any help ...

Best regards,
Robert

.

I am using Excel ver 11 by the way


-----Original Message-----
Hi all,
If I record a macro that does a find / replace on a
sheet
and then record another one that does a Find / Replace
on
the entire workbook, they are identical!!

How can I code a Find / Replace statement that will
always perform on all sheets in my workbook?

e.g.
This is the one for the sheet:
Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Cells.Replace What:="aaaa", Replacement:="bbbbb",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

..... and this is the one for the workbook:

Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Cells.Replace What:="aaaa", Replacement:="bbbbb",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False


The only way I can make it work, is to set the switch in
the Find / Replace Dialog box on the EDIT Menu bar.
Anyone know how to switch that on in the macro??

Thanks for any help ...

Best regards,
Robert

.

try this
Sub replaceall()
Sheets.Select
Cells.Replace What:="aaa", Replacement:="bbb
Sheets(1).Select
End Sub

--
Don Guillett
SalesAid Software

"Bony_Pony" wrote in message
...
Hi all,
If I record a macro that does a find / replace on a sheet
and then record another one that does a Find / Replace on
the entire workbook, they are identical!!

How can I code a Find / Replace statement that will
always perform on all sheets in my workbook?

e.g.
This is the one for the sheet:
Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Cells.Replace What:="aaaa", Replacement:="bbbbb",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

.... and this is the one for the workbook:

Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Cells.Replace What:="aaaa", Replacement:="bbbbb",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False


The only way I can make it work, is to set the switch in
the Find / Replace Dialog box on the EDIT Menu bar.
Anyone know how to switch that on in the macro??

Thanks for any help ...

Best regards,
Robert

Don hi and thank you for your help!

That worked - except now I have the other problem - when
I want to restrict a find / replace to a single sheet, it
now changes across the wole book - I tried selecting only
the sheet I wanted it to act on but it still changes all
in everything. Any ideas?

regards,
Robert


-----Original Message-----
try this
Sub replaceall()
Sheets.Select
Cells.Replace What:="aaa", Replacement:="bbb
Sheets(1).Select
End Sub

--
Don Guillett
SalesAid Software

"Bony_Pony" wrote
in message
...
Hi all,
If I record a macro that does a find / replace on a
sheet
and then record another one that does a Find / Replace
on
the entire workbook, they are identical!!

How can I code a Find / Replace statement that will
always perform on all sheets in my workbook?

e.g.
This is the one for the sheet:
Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Cells.Replace What:="aaaa", Replacement:="bbbbb",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

.... and this is the one for the workbook:

Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Cells.Replace What:="aaaa", Replacement:="bbbbb",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False


The only way I can make it work, is to set the switch
in
the Find / Replace Dialog box on the EDIT Menu bar.
Anyone know how to switch that on in the macro??

Thanks for any help ...

Best regards,
Robert



.

Then just use a different macro or comment out the 1st and last lines.

--
Don Guillett
SalesAid Software

"Bony_Pony" wrote in message
...
Don hi and thank you for your help!

That worked - except now I have the other problem - when
I want to restrict a find / replace to a single sheet, it
now changes across the wole book - I tried selecting only
the sheet I wanted it to act on but it still changes all
in everything. Any ideas?

regards,
Robert


-----Original Message-----
try this
Sub replaceall()
Sheets.Select
Cells.Replace What:="aaa", Replacement:="bbb
Sheets(1).Select
End Sub

--
Don Guillett
SalesAid Software

"Bony_Pony" wrote
in message
...
Hi all,
If I record a macro that does a find / replace on a
sheet
and then record another one that does a Find / Replace
on
the entire workbook, they are identical!!

How can I code a Find / Replace statement that will
always perform on all sheets in my workbook?

e.g.
This is the one for the sheet:
Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Cells.Replace What:="aaaa", Replacement:="bbbbb",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

.... and this is the one for the workbook:

Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Cells.Replace What:="aaaa", Replacement:="bbbbb",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False


The only way I can make it work, is to set the switch
in
the Find / Replace Dialog box on the EDIT Menu bar.
Anyone know how to switch that on in the macro??

Thanks for any help ...

Best regards,
Robert



.

Hi Don,
That's what I did but the replace still defaults to the
whole sheet. The only way I can restrict it, is via the
Find / Replace option on the Menu bar and if I set the
options to Sheet, it works for the sheet. I need to
switch this via VB though.

I have 2 situations - one where I will change the entire
contents of a workbook and other times that I want to
change a single sheet.

There are two seperate macros that control this.

I think Excel remembers the last Find / Replace selection
you made and applies it until it is changed manually.
What do you think?

Regards,
Robert
-----Original Message-----
Then just use a different macro or comment out the 1st
and last lines.

--
Don Guillett
SalesAid Software

"Bony_Pony" wrote
in message
...
Don hi and thank you for your help!

That worked - except now I have the other problem -
when
I want to restrict a find / replace to a single sheet,
it
now changes across the wole book - I tried selecting
only
the sheet I wanted it to act on but it still changes
all
in everything. Any ideas?

regards,
Robert


-----Original Message-----
try this
Sub replaceall()
Sheets.Select
Cells.Replace What:="aaa", Replacement:="bbb
Sheets(1).Select
End Sub

--
Don Guillett
SalesAid Software

"Bony_Pony"
wrote
in message
...
Hi all,
If I record a macro that does a find / replace on a
sheet
and then record another one that does a Find /
Replace
on
the entire workbook, they are identical!!

How can I code a Find / Replace statement that will
always perform on all sheets in my workbook?

e.g.
This is the one for the sheet:
Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Cells.Replace What:="aaaa",
Replacement:="bbbbb",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

.... and this is the one for the workbook:

Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Cells.Replace What:="aaaa",
Replacement:="bbbbb",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False


The only way I can make it work, is to set the
switch
in
the Find / Replace Dialog box on the EDIT Menu bar.
Anyone know how to switch that on in the macro??

Thanks for any help ...

Best regards,
Robert



.



.

try this

Sub replaceActiveSheetOnly()
ActiveSheet.Cells.Replace What:="aaa", Replacement:="bbb"
End Sub

--
Don Guillett
SalesAid Software

"Bony_Pony" wrote in message
...
Hi Don,
That's what I did but the replace still defaults to the
whole sheet. The only way I can restrict it, is via the
Find / Replace option on the Menu bar and if I set the
options to Sheet, it works for the sheet. I need to
switch this via VB though.

I have 2 situations - one where I will change the entire
contents of a workbook and other times that I want to
change a single sheet.

There are two seperate macros that control this.

I think Excel remembers the last Find / Replace selection
you made and applies it until it is changed manually.
What do you think?

Regards,
Robert
-----Original Message-----
Then just use a different macro or comment out the 1st
and last lines.

--
Don Guillett
SalesAid Software

"Bony_Pony" wrote
in message
...
Don hi and thank you for your help!

That worked - except now I have the other problem -
when
I want to restrict a find / replace to a single sheet,
it
now changes across the wole book - I tried selecting
only
the sheet I wanted it to act on but it still changes
all
in everything. Any ideas?

regards,
Robert


-----Original Message-----
try this
Sub replaceall()
Sheets.Select
Cells.Replace What:="aaa", Replacement:="bbb
Sheets(1).Select
End Sub

--
Don Guillett
SalesAid Software

"Bony_Pony"
wrote
in message
...
Hi all,
If I record a macro that does a find / replace on a
sheet
and then record another one that does a Find /
Replace
on
the entire workbook, they are identical!!

How can I code a Find / Replace statement that will
always perform on all sheets in my workbook?

e.g.
This is the one for the sheet:
Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Cells.Replace What:="aaaa",
Replacement:="bbbbb",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

.... and this is the one for the workbook:

Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Cells.Replace What:="aaaa",
Replacement:="bbbbb",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False


The only way I can make it work, is to set the
switch
in
the Find / Replace Dialog box on the EDIT Menu bar.
Anyone know how to switch that on in the macro??

Thanks for any help ...

Best regards,
Robert



.



.

Hi,
Tried that - actually it was the first thing I tried ...
sigh!!

Same result - it is the dropdown in the find / replace
that controls it no matter what selection I use in VBA.

Thanks anyway

Regards,
Robert
-----Original Message-----
try this

Sub replaceActiveSheetOnly()
ActiveSheet.Cells.Replace What:="aaa",
Replacement:="bbb"
End Sub

--
Don Guillett
SalesAid Software

"Bony_Pony" wrote
in message
...
Hi Don,
That's what I did but the replace still defaults to the
whole sheet. The only way I can restrict it, is via
the
Find / Replace option on the Menu bar and if I set the
options to Sheet, it works for the sheet. I need to
switch this via VB though.

I have 2 situations - one where I will change the
entire
contents of a workbook and other times that I want to
change a single sheet.

There are two seperate macros that control this.

I think Excel remembers the last Find / Replace
selection
you made and applies it until it is changed manually.
What do you think?

Regards,
Robert
-----Original Message-----
Then just use a different macro or comment out the 1st
and last lines.

--
Don Guillett
SalesAid Software

"Bony_Pony"
wrote
in message
...
Don hi and thank you for your help!

That worked - except now I have the other problem -
when
I want to restrict a find / replace to a single
sheet,
it
now changes across the wole book - I tried selecting
only
the sheet I wanted it to act on but it still changes
all
in everything. Any ideas?

regards,
Robert


-----Original Message-----
try this
Sub replaceall()
Sheets.Select
Cells.Replace What:="aaa", Replacement:="bbb
Sheets(1).Select
End Sub

--
Don Guillett
SalesAid Software

"Bony_Pony"
wrote
in message
...
Hi all,
If I record a macro that does a find / replace
on a
sheet
and then record another one that does a Find /
Replace
on
the entire workbook, they are identical!!

How can I code a Find / Replace statement that
will
always perform on all sheets in my workbook?

e.g.
This is the one for the sheet:
Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Cells.Replace What:="aaaa",
Replacement:="bbbbb",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

.... and this is the one for the workbook:

Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Cells.Replace What:="aaaa",
Replacement:="bbbbb",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False


The only way I can make it work, is to set the
switch
in
the Find / Replace Dialog box on the EDIT Menu
bar.
Anyone know how to switch that on in the macro??

Thanks for any help ...

Best regards,
Robert



.



.



.

Make the manual change to sheets. Use the macros only. I just tested and
that works.

--
Don Guillett
SalesAid Software

wrote in message
...
Hi,
Tried that - actually it was the first thing I tried ...
sigh!!

Same result - it is the dropdown in the find / replace
that controls it no matter what selection I use in VBA.

Thanks anyway

Regards,
Robert
-----Original Message-----
try this

Sub replaceActiveSheetOnly()
ActiveSheet.Cells.Replace What:="aaa",
Replacement:="bbb"
End Sub

--
Don Guillett
SalesAid Software

"Bony_Pony" wrote
in message
...
Hi Don,
That's what I did but the replace still defaults to the
whole sheet. The only way I can restrict it, is via
the
Find / Replace option on the Menu bar and if I set the
options to Sheet, it works for the sheet. I need to
switch this via VB though.

I have 2 situations - one where I will change the
entire
contents of a workbook and other times that I want to
change a single sheet.

There are two seperate macros that control this.

I think Excel remembers the last Find / Replace
selection
you made and applies it until it is changed manually.
What do you think?

Regards,
Robert
-----Original Message-----
Then just use a different macro or comment out the 1st
and last lines.

--
Don Guillett
SalesAid Software

"Bony_Pony"
wrote
in message
...
Don hi and thank you for your help!

That worked - except now I have the other problem -
when
I want to restrict a find / replace to a single
sheet,
it
now changes across the wole book - I tried selecting
only
the sheet I wanted it to act on but it still changes
all
in everything. Any ideas?

regards,
Robert


-----Original Message-----
try this
Sub replaceall()
Sheets.Select
Cells.Replace What:="aaa", Replacement:="bbb
Sheets(1).Select
End Sub

--
Don Guillett
SalesAid Software

"Bony_Pony"
wrote
in message
...
Hi all,
If I record a macro that does a find / replace
on a
sheet
and then record another one that does a Find /
Replace
on
the entire workbook, they are identical!!

How can I code a Find / Replace statement that
will
always perform on all sheets in my workbook?

e.g.
This is the one for the sheet:
Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Cells.Replace What:="aaaa",
Replacement:="bbbbb",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

.... and this is the one for the workbook:

Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Cells.Replace What:="aaaa",
Replacement:="bbbbb",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False


The only way I can make it work, is to set the
switch
in
the Find / Replace Dialog box on the EDIT Menu
bar.
Anyone know how to switch that on in the macro??

Thanks for any help ...

Best regards,
Robert



.



.



.

correction. Set manually to sheet. Use this for ALL and the activesheet one
for 1 sheet.

Sub replaceal1()
For Each ws In Worksheets
ws.Cells.Replace What:="bbb", Replacement:="aaa"
Next ws
End Sub
--
Don Guillett
SalesAid Software

wrote in message
...
Hi,
Tried that - actually it was the first thing I tried ...
sigh!!

Same result - it is the dropdown in the find / replace
that controls it no matter what selection I use in VBA.

Thanks anyway

Regards,
Robert
-----Original Message-----
try this

Sub replaceActiveSheetOnly()
ActiveSheet.Cells.Replace What:="aaa",
Replacement:="bbb"
End Sub

--
Don Guillett
SalesAid Software

"Bony_Pony" wrote
in message
...
Hi Don,
That's what I did but the replace still defaults to the
whole sheet. The only way I can restrict it, is via
the
Find / Replace option on the Menu bar and if I set the
options to Sheet, it works for the sheet. I need to
switch this via VB though.

I have 2 situations - one where I will change the
entire
contents of a workbook and other times that I want to
change a single sheet.

There are two seperate macros that control this.

I think Excel remembers the last Find / Replace
selection
you made and applies it until it is changed manually.
What do you think?

Regards,
Robert
-----Original Message-----
Then just use a different macro or comment out the 1st
and last lines.

--
Don Guillett
SalesAid Software

"Bony_Pony"
wrote
in message
...
Don hi and thank you for your help!

That worked - except now I have the other problem -
when
I want to restrict a find / replace to a single
sheet,
it
now changes across the wole book - I tried selecting
only
the sheet I wanted it to act on but it still changes
all
in everything. Any ideas?

regards,
Robert


-----Original Message-----
try this
Sub replaceall()
Sheets.Select
Cells.Replace What:="aaa", Replacement:="bbb
Sheets(1).Select
End Sub

--
Don Guillett
SalesAid Software

"Bony_Pony"
wrote
in message
...
Hi all,
If I record a macro that does a find / replace
on a
sheet
and then record another one that does a Find /
Replace
on
the entire workbook, they are identical!!

How can I code a Find / Replace statement that
will
always perform on all sheets in my workbook?

e.g.
This is the one for the sheet:
Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Cells.Replace What:="aaaa",
Replacement:="bbbbb",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

.... and this is the one for the workbook:

Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Cells.Replace What:="aaaa",
Replacement:="bbbbb",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False


The only way I can make it work, is to set the
switch
in
the Find / Replace Dialog box on the EDIT Menu
bar.
Anyone know how to switch that on in the macro??

Thanks for any help ...

Best regards,
Robert



.



.



.