Find and Replace in VBA
 

So I've got this code to perform a find-and-replace in an Excel
workbook:

Cells.Replace What:="This", Replacement:="That", _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

Works fine, except that in order to have it perform the find-and-
replace on all worksheets in the workbook, I have to type CTRL-H and
change the "Within" property from "Sheet" to "Workbook". The next time
I open Excel, that property is automatically changed back to "Sheet."

Is there a way to set the "Within" property for find-and-replace in
VBA?

Thanks!

Find and Replace in VBA
 

Assuming that line of code does what you want, just replace it with this
loop...

For Each WS In Worksheets
WS.Cells.Replace What:="This", Replacement:="That", _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next

and use this line of code to declare the WS variable...

Dim WS As Worksheet

The above loop will loop through each worksheet and run the Replace method
on their cells automatically.

--
Rick (MVP - Excel)


"septimus" wrote in message
...
So I've got this code to perform a find-and-replace in an Excel
workbook:

Cells.Replace What:="This", Replacement:="That", _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

Works fine, except that in order to have it perform the find-and-
replace on all worksheets in the workbook, I have to type CTRL-H and
change the "Within" property from "Sheet" to "Workbook". The next time
I open Excel, that property is automatically changed back to "Sheet."

Is there a way to set the "Within" property for find-and-replace in
VBA?

Thanks!

Find and Replace in VBA
 

Try the below which works for the entire workbook

Sub Macro()
Dim ws As Worksheet
For Each ws In Sheets
ws.Cells.Replace What:="This", Replacement:="That", _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next
End Sub

--
Jacob


"septimus" wrote:

So I've got this code to perform a find-and-replace in an Excel
workbook:

Cells.Replace What:="This", Replacement:="That", _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

Works fine, except that in order to have it perform the find-and-
replace on all worksheets in the workbook, I have to type CTRL-H and
change the "Within" property from "Sheet" to "Workbook". The next time
I open Excel, that property is automatically changed back to "Sheet."

Is there a way to set the "Within" property for find-and-replace in
VBA?

Thanks!

.

Find and Replace in VBA
 

Thanks, guys. That worked beautifully -- for a while. Now all of a
sudden I run the code and it does nothing. I haven't changed it at all
since it worked.
Anybody have any ideas? Here's the code:

Sub UpdateVariableInfo()
'Update all text that changes from school to school.
Dim ws As Worksheet

'Loop through each worksheet and run a find-and-replace to update
all variable data.
For Each ws In Sheets

'Find and replace all instances of the school name
FindAndReplace ws, "This", "That"

Next

End Sub

Sub FindAndReplace(ws As Worksheet, strFrom As String, strTo As
String)
'Run a find and replace operation on the designated worksheet.

ws.Cells.Replace What:=strFrom, Replacement:=strTo, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

End Sub



On Jan 26, 10:45*pm, Jacob Skaria
wrote:
Try the below which works for the entire workbook

Sub Macro()
Dim ws As Worksheet
For Each ws In Sheets
ws.Cells.Replace What:="This", Replacement:="That", _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next
End Sub

--
Jacob

"septimus" wrote:
So I've got this code to perform a find-and-replace in an Excel
workbook:

* * * * Cells.Replace What:="This", Replacement:="That", _
* * * * * * * * LookAt:=xlPart, SearchOrder:=xlByColumns, _
* * * * * * * * MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

Works fine, except that in order to have it perform the find-and-
replace on all worksheets in the workbook, I have to type CTRL-H and
change the "Within" property from "Sheet" to "Workbook". The next time
I open Excel, that property is automatically changed back to "Sheet."

Is there a way to set the "Within" property for find-and-replace in
VBA?

Thanks!

.