Macro Only works Once

[kerry_ja]

Hi all,

I have a problem with macros. I have a macro that used to work as
desired, and now it only works the first time Excel is opened, then it
doesn't run at all:
Sub RemoveLetters()
Selection.Replace What:="a", Replacement:="", MatchCase:=False
Selection.Replace What:="b", Replacement:="", MatchCase:=False
Selection.Replace What:="c", Replacement:="", MatchCase:=False
Selection.Replace What:="d", Replacement:="", MatchCase:=False
Selection.Replace What:="e", Replacement:="", MatchCase:=False
Selection.Replace What:="f", Replacement:="", MatchCase:=False
Selection.Replace What:="g", Replacement:="", MatchCase:=False
Selection.Replace What:="h", Replacement:="", MatchCase:=False
Selection.Replace What:="i", Replacement:="", MatchCase:=False
Selection.Replace What:="j", Replacement:="", MatchCase:=False
Selection.Replace What:="k", Replacement:="", MatchCase:=False
Selection.Replace What:="l", Replacement:="", MatchCase:=False
Selection.Replace What:="m", Replacement:="", MatchCase:=False
Selection.Replace What:="n", Replacement:="", MatchCase:=False
Selection.Replace What:="o", Replacement:="", MatchCase:=False
Selection.Replace What:="p", Replacement:="", MatchCase:=False
Selection.Replace What:="q", Replacement:="", MatchCase:=False
Selection.Replace What:="r", Replacement:="", MatchCase:=False
Selection.Replace What:="s", Replacement:="", MatchCase:=False
Selection.Replace What:="t", Replacement:="", MatchCase:=False
Selection.Replace What:="u", Replacement:="", MatchCase:=False
Selection.Replace What:="v", Replacement:="", MatchCase:=False
Selection.Replace What:="w", Replacement:="", MatchCase:=False
Selection.Replace What:="x", Replacement:="", MatchCase:=False
Selection.Replace What:="y", Replacement:="", MatchCase:=False
Selection.Replace What:="z", Replacement:="", MatchCase:=False
Selection.Replace What:="~*", Replacement:="", MatchCase:=False
Selection.Replace What:="--*", Replacement:="", MatchCase:=False
Selection.Replace What:="-", Replacement:="0", LookAt:=xlWhole,
MatchCase:=False
End Sub

If I close Excel, and then open it again, the macro works once, then
doesn't work again, until I close and reopen Excel.

Any idea why this is?

 

[Don Guillett]

try this idea
Sub replaceletters()
With Selection
For i = 97 To 122
.Replace what:=Chr(i), replacement:="" 'a-z
.Replace what:=Chr(42), replacement:="" '*
.Replace what:=Chr(45), replacement:="" '-
Next i
End With
End Sub

 

[kerry_ja]

Hi Don,

That's a good idea to shorten the code, but has no effect on the
problem I am encountering.

 

[kerry_ja]

Hi JLGWhiz

That is not true, I call the macro, by having it assigned to a button

 

[Dave Peterson]

Since your code runs against the current selection, maybe you don't have the
correct stuff selected when you click the button?????????

 

[kerry_ja]

Hi Dave,

No that's not the problem. If I add Range("B3:B14").Select (for
example) before the rest of the code, it still doesn't work, however if
I close the work book, and then open it again, the code works once, but
not again.

 

[Dave Peterson]

Replace and Find like to remember the last settings that you've used--either in
code or manually.

Try adding all the parms to your .replace statement that you want. Don't trust
that the existing parms are what you need for your .replace.

expression.Replace(What, Replacement, LookAt, SearchOrder, MatchCase,
MatchByte, SearchFormat, ReplaceFormat)

 

[kerry_ja]

Hi Dave,

That was it, the problem was the last line of code caused it to look at
Whole, but for most of the code I actually only wanted it to look at
any part. Thanks. My revised code is now:
Sub RemoveLetters()
Dim intChar As Integer
With Selection
For intChar = 97 To 122
.Replace What:=Chr(intChar), Replacement:="",
MatchCase:=False, LookAt:=xlPart 'a-z
Next intChar
.Replace What:="~*", Replacement:=""
.Replace What:="--*", Replacement:=""
.Replace What:="-", Replacement:="0", LookAt:=xlWhole
End With
End Sub

 

[Dave Peterson]

I would still specify all the parms on each .replace.