Find and Replace no longer working

[ShuStar]

Hi Legends,

The code below was working but now for some reason it doesn't seem to be working. Does anyone know why this could be or how to fix it (or even a better fool-proof code?)

All the code does is: look in Cell B4 and if its contains characters like ?!,.#$%^ etc - remove these.

What I mean by not working:
-When i first wrote it a few weeks back it worked fine (both individually when pressing F8 and when executing my overall macro)
-However recently, I noticed at times it would sometimes look like its executing the code line but obviously not removing any characters (and no debug prompts) - therefore to make sure it properly finds and replaces characters, I incorporated the loop so that it does this 5 times - this seemed to have had fixed that niggly issue
-but now it just doesn't work at all - even looping 5 times or when individually pressing F8 to test if the code works it doesnt remove any of the characters - it doesnt prompt with the error debug message either

Just seems for some reason its stopped functioning even though no changes made to the code, or my windows/excel version... :-s

Any ideas to fix it would be greatly appreciated.

Sub RemoveCharacters()

'Code removes following characters ...;-<>:!@#$%^&()-_+=.,~`?*/\[]{}|


Dim i As Long

Sheets("Test Sheet").Select


        For i = 1 To 5

            Range("B4").Replace "~*", ""
            Range("B4").Replace "~?", ""
            Range("B4").Replace "~~", ""
            Range("B4").Replace "`", ""
            Range("B4").Replace "/", ""
            Range("B4").Replace "\", ""
            Range("B4").Replace "[", ""
            Range("B4").Replace "]", ""
            Range("B4").Replace "{", ""
            Range("B4").Replace "}", ""
            Range("B4").Replace "|", ""
            Range("B4").Replace "!", ""
            
            Sheets("Test Sheet").Range("B4").Replace _
            What:=".", Replacement:="", _
             SearchOrder:=xlByColumns, MatchCase:=True
            
            Sheets("Test Sheet").Range("B4").Replace _
            What:=";", Replacement:="", _
             SearchOrder:=xlByColumns, MatchCase:=True
            
            Sheets("Test Sheet").Range("B4").Replace _
            What:="-", Replacement:="", _
             SearchOrder:=xlByColumns, MatchCase:=True
            
            Sheets("Test Sheet").Range("B4").Replace _
            What:="<", Replacement:="", _
             SearchOrder:=xlByColumns, MatchCase:=True
            
            Sheets("Test Sheet").Range("B4").Replace _
            What:=">", Replacement:="", _
             SearchOrder:=xlByColumns, MatchCase:=True
            
            Sheets("Test Sheet").Range("B4").Replace _
            What:=":", Replacement:="", _
             SearchOrder:=xlByColumns, MatchCase:=True
            
            Sheets("Test Sheet").Range("B4").Replace _
            What:="!", Replacement:="", _
             SearchOrder:=xlByColumns, MatchCase:=True
            
            Sheets("Test Sheet").Range("B4").Replace _
            What:="@", Replacement:="", _
             SearchOrder:=xlByColumns, MatchCase:=True
            
            Sheets("Test Sheet").Range("B4").Replace _
            What:="#", Replacement:="", _
             SearchOrder:=xlByColumns, MatchCase:=True
            
            Sheets("Test Sheet").Range("B4").Replace _
            What:="$", Replacement:="", _
             SearchOrder:=xlByColumns, MatchCase:=True
            
            Sheets("Test Sheet").Range("B4").Replace _
            What:="%", Replacement:="", _
             SearchOrder:=xlByColumns, MatchCase:=True
            
             Sheets("Test Sheet").Range("B4").Replace _
            What:="^", Replacement:="", _
             SearchOrder:=xlByColumns, MatchCase:=True
            
             Sheets("Test Sheet").Range("B4").Replace _
            What:="&", Replacement:="", _
             SearchOrder:=xlByColumns, MatchCase:=True
            
              Sheets("Test Sheet").Range("B4").Replace _
            What:="(", Replacement:="", _
             SearchOrder:=xlByColumns, MatchCase:=True
            
             Sheets("Test Sheet").Range("B4").Replace _
            What:=")", Replacement:="", _
             SearchOrder:=xlByColumns, MatchCase:=True
            
             Sheets("Test Sheet").Range("B4").Replace _
            What:="-", Replacement:="", _
             SearchOrder:=xlByColumns, MatchCase:=True
            
             Sheets("Test Sheet").Range("B4").Replace _
            What:="_", Replacement:="", _
             SearchOrder:=xlByColumns, MatchCase:=True
            
            Sheets("Test Sheet").Range("B4").Replace _
            What:="+", Replacement:="", _
             SearchOrder:=xlByColumns, MatchCase:=True
            
            Sheets("Test Sheet").Range("B4").Replace _
            What:="=", Replacement:="", _
             SearchOrder:=xlByColumns, MatchCase:=True
            
            Sheets("Test Sheet").Range("B4").Replace _
            What:=".", Replacement:="", _
             SearchOrder:=xlByColumns, MatchCase:=True
            
            Sheets("Test Sheet").Range("B4").Replace _
            What:=",", Replacement:="", _
             SearchOrder:=xlByColumns, MatchCase:=True
            
            Sheets("Test Sheet").Range("B4").Replace _
            What:="~", Replacement:="", _
             SearchOrder:=xlByColumns, MatchCase:=True
            
        Next i

End Sub

 

[Fluff]

How about

   With Sheets("Test Sheet")
      .Range("B4").Replace "~*", "", xlPart, , , , False, False
      .Range("B4").Replace "~?", "", xlPart, , , , False, False
      .Range("B4").Replace "~~", "", xlPart, , , , False, False
      .Range("B4").Replace "`", "", xlPart, , , , False, False
      .Range("B4").Replace "/", "", xlPart, , , , False, False
      .Range("B4").Replace "\", "", xlPart, , , , False, False
      .Range("B4").Replace "[", "", xlPart, , , , False, False
      .Range("B4").Replace "]", "", xlPart, , , , False, False
      .Range("B4").Replace "{", "", xlPart, , , , False, False
      .Range("B4").Replace "}", "", xlPart, , , , False, False
      .Range("B4").Replace "|", "", xlPart, , , , False, False
      .Range("B4").Replace "!", "", xlPart, , , , False, False
   End With

Although you'd probably be better of with RegEx.

 

[ShuStar]

Many thanks for this Fluff. Yes this is working. Also - weirdly my original code is working too but I think I'll stick to yours as it's cleaner. This seems to be an intermittent issue, so I'll keep an eye out to see if this issue re-occurs with your code too.

Forgive my ignorance - I am not sure what you mean by ..."Although you'd probably be better of with RegEx. "

 

[Fluff]

RegEx is a method of removing unwanted characters from a string, but it's not something I know much about.

 

[ShuStar]

Ah I see, thank you, will look into this. Appreciate the help.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[JoeMo]

Many thanks for this Fluff. Yes this is working. Also - weirdly my original code is working too but I think I'll stick to yours as it's cleaner. This seems to be an intermittent issue, so I'll keep an eye out to see if this issue re-occurs with your code too.

The Range.Replace function has a number of arguments, all of which are optional except the first two (What and Replacement). Once those arguments are set they remain as defaults unless you change them. In particular, the LookAt argument is most likely the source of the erratic results you were seeing. In your case, assuming the various characters you wanted to replace were part of longer strings, if the LookAt was set to xlWhole, rather than xlPart, nothing would happen when you run the code. Fluff delberately and correctly set the LookAt to xlPart in his code. That setting was maintained when you then ran your code, so, not so weirdly, your code worked again.

 

[ShuStar]

JoeMo thank you for explaining this - makes sense now. Will stick to using Fluff's code to prevent this from happening. Thank you all.

 

[JoeMo]

JoeMo thank you for explaining this - makes sense now. Will stick to using Fluff's code to prevent this from happening. Thank you all.

You are welcome - thanks for the reply.