ExcelBanter - can I replace all with a macro?

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

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - can I replace all with a macro? (https://www.excelbanter.com/excel-programming/314184-can-i-replace-all-macro.html)

can I replace all with a macro?

Hi,
Can I replace (all "i" characters with "ý") and (all "g" characters with
"ð") and (all "u" characters with "ü") within all cells of all Excel sheets
in a workbook with a VB macro?
TIA

can I replace all with a macro?

Record a macro in Excel that does it, and you will then know you can.

--

HTH

RP

"J_J" wrote in message
...
Hi,
Can I replace (all "i" characters with "ý") and (all "g" characters with
"ð") and (all "u" characters with "ü") within all cells of all Excel
sheets
in a workbook with a VB macro?
TIA

can I replace all with a macro?

Hi Bob,
Thanks for your suggestion. Yes the below code "partly" does the job
'--------------------------------
Sub Macro1()
' Macro1 Macro
' Macro recorded 20.10.2004 by J_J
Cells.Replace What:="i", Replacement:="ý", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False
Cells.Replace What:="g", Replacement:="ð", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False
Cells.Replace What:="u", Replacement:="ü", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False
End Sub
'--------------------------------
but I need to do it far "all" existing Sheets in the workbook all in one
go...
Thanks
J_J

"Bob Phillips" wrote in message
...
Record a macro in Excel that does it, and you will then know you can.

--

HTH

RP

"J_J" wrote in message
...
Hi,
Can I replace (all "i" characters with "ý") and (all "g" characters with
"ð") and (all "u" characters with "ü") within all cells of all Excel
sheets
in a workbook with a VB macro?
TIA

can I replace all with a macro?

J_J,

This is untested, but fingers-crossed

Sub Macro1()
Dim sh As Worksheet
For Each sh In Activeworkbook.Worksheets
ReplaceAll sh, "i","y"
ReplaceAll sh, "g", "ð"
ReplaceAll sh, "u", "ü"
Next sh
End Sub

Private Sub ReplaceAll(sh As Worksheet, pWhat As String, pBy As String)
sh.Cells.Replace What:="i", _
Replacement:="ý", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

End Sub

--

HTH

RP

"J_J" wrote in message
...
Hi Bob,
Thanks for your suggestion. Yes the below code "partly" does the job
'--------------------------------
Sub Macro1()
' Macro1 Macro
' Macro recorded 20.10.2004 by J_J
Cells.Replace What:="i", Replacement:="ý", LookAt:=xlPart, SearchOrder
_
:=xlByRows, MatchCase:=False
Cells.Replace What:="g", Replacement:="ð", LookAt:=xlPart, SearchOrder
_
:=xlByRows, MatchCase:=False
Cells.Replace What:="u", Replacement:="ü", LookAt:=xlPart, SearchOrder
_
:=xlByRows, MatchCase:=False
End Sub
'--------------------------------
but I need to do it far "all" existing Sheets in the workbook all in one
go...
Thanks
J_J

"Bob Phillips" wrote in message
...
Record a macro in Excel that does it, and you will then know you can.

--

HTH

RP

"J_J" wrote in message
...
Hi,
Can I replace (all "i" characters with "ý") and (all "g" characters
with
"ð") and (all "u" characters with "ü") within all cells of all Excel
sheets
in a workbook with a VB macro?
TIA

can I replace all with a macro?

oops

Private Sub ReplaceAll(sh As Worksheet, pWhat As String, pBy As String)
sh.Cells.Replace What:=pWhat, _
Replacement:=pBy, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

End Sub

--

HTH

RP

"Bob Phillips" wrote in message
...
J_J,

This is untested, but fingers-crossed

Sub Macro1()
Dim sh As Worksheet
For Each sh In Activeworkbook.Worksheets
ReplaceAll sh, "i","y"
ReplaceAll sh, "g", "ð"
ReplaceAll sh, "u", "ü"
Next sh
End Sub

Private Sub ReplaceAll(sh As Worksheet, pWhat As String, pBy As String)
sh.Cells.Replace What:="i", _
Replacement:="ý", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

End Sub

--

HTH

RP

"J_J" wrote in message
...
Hi Bob,
Thanks for your suggestion. Yes the below code "partly" does the job
'--------------------------------
Sub Macro1()
' Macro1 Macro
' Macro recorded 20.10.2004 by J_J
Cells.Replace What:="i", Replacement:="ý", LookAt:=xlPart,
SearchOrder
_
:=xlByRows, MatchCase:=False
Cells.Replace What:="g", Replacement:="ð", LookAt:=xlPart,
SearchOrder
_
:=xlByRows, MatchCase:=False
Cells.Replace What:="u", Replacement:="ü", LookAt:=xlPart,
SearchOrder
_
:=xlByRows, MatchCase:=False
End Sub
'--------------------------------
but I need to do it far "all" existing Sheets in the workbook all in one
go...
Thanks
J_J

"Bob Phillips" wrote in message
...
Record a macro in Excel that does it, and you will then know you can.

--

HTH

RP

"J_J" wrote in message
...
Hi,
Can I replace (all "i" characters with "ý") and (all "g" characters
with
"ð") and (all "u" characters with "ü") within all cells of all Excel
sheets
in a workbook with a VB macro?
TIA

can I replace all with a macro?

I think Bob meant:
Private Sub ReplaceAll(sh As Worksheet, pWhat As String, pBy As String)
sh.Cells.Replace What:=pWhat, _
Replacement:=bBy, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End Sub

--
If I've mis-understood the question please tell me.

HTH

ijb

Remove nospam from my e-mail address to talk direct

Not MCSD, MVP, TLA, P&P, PCMCIA, etc just trying to help

"Bob Phillips" wrote in message
...
J_J,

This is untested, but fingers-crossed

Sub Macro1()
Dim sh As Worksheet
For Each sh In Activeworkbook.Worksheets
ReplaceAll sh, "i","y"
ReplaceAll sh, "g", "ð"
ReplaceAll sh, "u", "ü"
Next sh
End Sub

Private Sub ReplaceAll(sh As Worksheet, pWhat As String, pBy As String)
sh.Cells.Replace What:="i", _
Replacement:="ý", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

End Sub

--

HTH

RP

"J_J" wrote in message
...
Hi Bob,
Thanks for your suggestion. Yes the below code "partly" does the job
'--------------------------------
Sub Macro1()
' Macro1 Macro
' Macro recorded 20.10.2004 by J_J
Cells.Replace What:="i", Replacement:="ý", LookAt:=xlPart,
SearchOrder
_
:=xlByRows, MatchCase:=False
Cells.Replace What:="g", Replacement:="ð", LookAt:=xlPart,
SearchOrder
_
:=xlByRows, MatchCase:=False
Cells.Replace What:="u", Replacement:="ü", LookAt:=xlPart,
SearchOrder
_
:=xlByRows, MatchCase:=False
End Sub
'--------------------------------
but I need to do it far "all" existing Sheets in the workbook all in one
go...
Thanks
J_J

"Bob Phillips" wrote in message
...
Record a macro in Excel that does it, and you will then know you can.

--

HTH

RP

"J_J" wrote in message
...
Hi,
Can I replace (all "i" characters with "ý") and (all "g" characters
with
"ð") and (all "u" characters with "ü") within all cells of all Excel
sheets
in a workbook with a VB macro?
TIA

can I replace all with a macro?

Course I did, and I also spotted it <vbg

Bob

"ijb" wrote in message
...
I think Bob meant:
Private Sub ReplaceAll(sh As Worksheet, pWhat As String, pBy As String)
sh.Cells.Replace What:=pWhat, _
Replacement:=bBy, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

End Sub

--
If I've mis-understood the question please tell me.

HTH

ijb

Remove nospam from my e-mail address to talk direct

Not MCSD, MVP, TLA, P&P, PCMCIA, etc just trying to help

"Bob Phillips" wrote in message
...
J_J,