MS Excel find and replace macro
- by william
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
