| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| macro - replace | New Users to Excel | |||
| macro to replace | Excel Discussion (Misc queries) | |||
| Macro-replace | Excel Discussion (Misc queries) | |||
| Replace Macro | Excel Discussion (Misc queries) | |||
| Macro to replace * with / | Excel Programming | |||
Hybrid Mode
