MS Excel find and replace macro

Posted by william on Super User See other posts from Super User or by william
Published on 2012-10-17T16:35:48Z Indexed on 2012/10/17 17:05 UTC
Read the original article Hit count: 230

Filed under:
|

I have written a macro to remove special characters in a sheet based on ascii values but the problem with it is that it is replacing the cell content. For example p;j;h which should become p,j,h is becoming ,, (missing the data). Do I need to include any additional statements, or how else to adjust my code?

sub specialcharecters()

Dim i As Long

For i = 32 To 43
    Selection.Replace what:=Chr(i), replacement:=", ", LookAt:=xlPart, SearchOrder:= _
       xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next i

Selection.Replace what:="~*", replacement:=", ", LookAt:=xlPart, SearchOrder:= _
       xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

For i = 45 To 47
    Selection.Replace what:=Chr(i), replacement:=", ", LookAt:=xlPart, SearchOrder:= _
       xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next i

For i = 58 To 64
    Selection.Replace what:=Chr(i), replacement:=", ", LookAt:=xlPart, SearchOrder:= _
       xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next i

For i = 123 To 125
    Selection.Replace what:=Chr(i), replacement:=", ", LookAt:=xlPart, SearchOrder:= _
       xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next i

Selection.Replace what:="~~", replacement:=", ", LookAt:=xlPart, SearchOrder:= _
       xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False


END sub

© Super User or respective owner

Related posts about excel-2007

Related posts about vba