Macro from 2005 need help for 2010

Jega

New Member
Joined
May 2, 2012
Messages
7
Hi I have an old macro that needs to be converted to version 2012. Can anyone help me or is it going to be too complicated.
I copied it into a new macro but to no avail.
I would also like a smiley face button in excel to activate it.
Thanks in advance for any help.
Here it is:

Sub CleanUp()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:eek:ffice:smarttags" /><st1:PersonName w:st="on">'</st1:PersonName><o:p></o:p>
<st1:PersonName w:st="on">'</st1:PersonName> CleanUp Macro<o:p></o:p>
<st1:PersonName w:st="on">'</st1:PersonName> Macro recorded 17/10/2005 by <st1:PersonName w:st="on">Charmaine Beckles</st1:PersonName><o:p></o:p>
<st1:PersonName w:st="on">'</st1:PersonName><o:p></o:p>
Selection.Find.ClearFormatting<o:p></o:p>
Selection.Find.Replacement.ClearFormatting<o:p></o:p>
With Selection.Find<o:p></o:p>
.Text = "&apos;"<o:p></o:p>
.Replacement.Text = "<st1:PersonName w:st="on">'</st1:PersonName>"<o:p></o:p>
.Forward = True<o:p></o:p>
.Wrap = wdFindAsk<o:p></o:p>
.Format = False<o:p></o:p>
.MatchCase = False<o:p></o:p>
.MatchWholeWord = False<o:p></o:p>
.MatchWildcards = False<o:p></o:p>
.MatchSoundsLike = False<o:p></o:p>
.MatchAllWordForms = False<o:p></o:p>
<o:p> </o:p>
End With<o:p></o:p>
<o:p> </o:p>
Selection.Find.Execute Replace:=wdReplaceAll<o:p></o:p>
Selection.Find.ClearFormatting<o:p></o:p>
Selection.Find.Replacement.ClearFormatting<o:p></o:p>
With Selection.Find<o:p></o:p>
.Text = "&"<o:p></o:p>
.Replacement.Text = "&"<o:p></o:p>
.Forward = True<o:p></o:p>
.Wrap = wdFindAsk<o:p></o:p>
.Format = False<o:p></o:p>
.MatchCase = False<o:p></o:p>
.MatchWholeWord = False<o:p></o:p>
.MatchWildcards = False<o:p></o:p>
.MatchSoundsLike = False<o:p></o:p>
.MatchAllWordForms = False<o:p></o:p>
<o:p> </o:p>
End With<o:p></o:p>
<o:p> </o:p>
Selection.Find.Execute Replace:=wdReplaceAll<o:p></o:p>
Selection.Find.ClearFormatting<o:p></o:p>
Selection.Find.Replacement.ClearFormatting<o:p></o:p>
With Selection.Find<o:p></o:p>
.Text = """<o:p></o:p>
.Replacement.Text = "<st1:PersonName w:st="on">'</st1:PersonName>"<o:p></o:p>
.Forward = True<o:p></o:p>
.Wrap = wdFindAsk<o:p></o:p>
.Format = False<o:p></o:p>
.MatchCase = False<o:p></o:p>
.MatchWholeWord = False<o:p></o:p>
.MatchWildcards = False<o:p></o:p>
.MatchSoundsLike = False<o:p></o:p>
.MatchAllWordForms = False<o:p></o:p>
<o:p> </o:p>
End With<o:p></o:p>
<o:p> </o:p>
Selection.Find.Execute Replace:=wdReplaceAll<o:p></o:p>
<o:p></o:p>
<o:p> </o:p>
End Sub<o:p></o:p>
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Jega

New Member
Joined
May 2, 2012
Messages
7
How do I get it to work in excel? They told me it once did.
Thanks
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
What other code is in the workbook? Does some other procedure call CleanUp? If not it will never have worked in Excel.
 

Jega

New Member
Joined
May 2, 2012
Messages
7

ADVERTISEMENT

No other code is in the workbook. It is a code to clean up all the &apos in the worksheets.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I don'tknow what you mean by &apos but to remove all ampersands try:

Code:
Sub RemoveAmpersands()
    Cells.Replace What:="&", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub
 

Jega

New Member
Joined
May 2, 2012
Messages
7

ADVERTISEMENT

That may work. I copied that code into my personal.xlsb file. I changed a couple of things:

Sub CleanUP()
Cells.Replace What:="&apos", Replacement:="'", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

When I ran it, nothing appeared to happen.
I need it to run it once a week. Each week there will be sheets added. So, I would end up with many worksheets at the end of the month. I need this to catch all the &apos and replace them with ' and maybe a message at the end to say how many replacements it made so I know it is complete.

Thank you so much for your help
I have succeded in doing this with a Run Macro. but when I run the macro again, it doesn't seem to work.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Have you tried recording a macro while replacing the text manually? What VBA code does it give you?
 

Jega

New Member
Joined
May 2, 2012
Messages
7
Sub CleanUp()
'
' CleanUp Macro
'
'
Sheets(Array("20120401117", "20120401126", "20120401128", "20120402199", _
"20120404149", "20120409145", "20120411155", "20120412137", "20120412169", _
"20120413157", "20120415115", "20120415116", "20120415122", "20120416126", _
"20120416173", "20120416187", "20120416193", "20120417174", "20120418121", _
"20120418162", "20120418169", "20120418188", "20120418195", "20120419123", _
"20120419172")).Select
Sheets("20120401117").Activate
Sheets(Array("20120420112", "20120420129", "20120420175", "20120423146", _
"20120423175", "20120427156", "20120427168", "20120429133", "20120429135", _
"20120430174")).Select Replace:=False
Cells.Replace What:="&apos", Replacement:="'", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub


It does not select all the worksheets. In fact nothing happens. I guess I will just have to do it manually :(
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try:

Code:
Sub CleanUp()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Cells.Replace What:="&apos", Replacement:="'", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    Next ws
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,126,971
Messages
5,621,890
Members
415,864
Latest member
cybid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top