ExcelBanter - VB Find and Replace

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - VB Find and Replace (https://www.excelbanter.com/excel-worksheet-functions/7488-vbulletin-find-replace.html)

Bony_Pony

VB Find and Replace

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

Bony_Pony

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

.

Bony_Pony

Don Guillett

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

Bony_Pony

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

.

Don Guillett

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

.

Bony_Pony

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

.

.

Don Guillett

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

.

.

.

Don Guillett

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

.

.

.

Don Guillett

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

.

.

.

All times are GMT +1. The time now is 12:03 AM.