0

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

     END sub

1 Answer 1

2

I stepped through your code and it appears that the culprit is Chr(42), which corresponds to the * symbol. When Excel encounters that, it's treating that as a wildcard and replacing all the text. If you add a simple if statement to skip 42, it gets through the first For loop without causing the observed problems. You'll need to do it again for Chr(63) (the ?) or it will give weird output again. Once both of those are accounted for, it worked just fine for me.

4
  • Thank you it worked but how do i specify it to run for a particular column?
    – william
    Oct 17, 2012 at 18:34
  • The way you have the code set up now, it runs on whatever you have selected. So if you want it run on an entire column, just select that column and then run the code.
    – techturtle
    Oct 17, 2012 at 18:39
  • but i did create a button and assigned the macro to it so when the user presses the button it should do the rest. Any suggestions please!!
    – william
    Oct 17, 2012 at 18:43
  • Not sure what suggestions you need... If you select Column A and press the button assigned to the macro, it should run your code only on Column A and not anywhere else on the rest of the sheet. It's working that way for me right now. Is yours doing something else?
    – techturtle
    Oct 17, 2012 at 18:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .