Can't replace commas with period

[John]

Hi Everyone

Below are my codes that I use to clean my data coming from the internet.
They all work except the first one (Find all the commas "," and replace with a
period (.) )
Can anyone tell me why?

Option Explicit

Sub Clean_Data()

Range("J3:L60").Select
Cells.Replace What:=",", Replacement:=".", LookAt:=xlPart, SearchOrder _
:=xlByColumns
Range("J3:L60").Select
Cells.Replace What:="$", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByColumns

Range("J3:L60").Select
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByColumns

' ALT-0160
Range("J3:L60").Select
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByColumns

End Sub

Regards
John

 

[Dave Peterson]

Maybe the values in the cells don't contain commas. Maybe they're just plain
old numbers formatted to show commas (either as the thousands separator or the
decimal point????).

 

[Dave Peterson]

ps

Range("J3:L60").Select
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByColumns

could be re-written:

Range("J3:L60").Replace What:=chr(160), Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByColumns

You could drop the .select's from all your code. And the chr(160) is a little
more self-documenting.

 

[John]

Hi Dave
Thank you for your help.
The commas are decimal separators and I need to change it to a period.
I did try manually to type the commas and to see if it would remove them but to
no availed.
If I use the "Find/Replace" menu, I can make it work but I get an error message.
even if I get an error, it still makes the corrections.
Regards
John

 

[Dave Peterson]

I use the dot for my decimal point and I could change that to a comma with no
trouble.

What error do you get?

Maybe you could try just changing the commas to nothing. Depending on how your
numbers are grouped, it may even be a better choice???

 

[John]

Hi Dave
I tried changing commas to nothing,does't work.
The error message is:
"The formula you typed contains an error."
For information about fixing common formula problems, click Help.
with two more line of information.
Regards
John

 

[John]

Hi Dave
I changed the comma symbal , for Chr(44) and it seem to work.
Will need to play with it to be sure.
It's your idea with Chr(160) that made me try that.
Thanks Dave
John

 

[John]

Me again
I tried it with fresh data 3 times and it works perfectly.
This is my new line:
Range("J3:L60").Replace what:=Chr(44), Replacement:=Chr(46), LookAt:=xlPart,
SearchOrder:= xlByColumns
Many thanks Dave
Regards
John

Hi Dave
I changed the comma symbal , for Chr(44) and it seem to work.
Will need to play with it to be sure.
It's your idea with Chr(160) that made me try that.
Thanks Dave
John

 

[Dave Peterson]

I have no idea why that would matter.

Me again
I tried it with fresh data 3 times and it works perfectly.
This is my new line:
Range("J3:L60").Replace what:=Chr(44), Replacement:=Chr(46), LookAt:=xlPart,
SearchOrder:= xlByColumns
Many thanks Dave
Regards
John

 

[John]

Hi Dave

I have no idea why that would matter.<

I wish someone knew, you're telling me it's a fluke,just hope it keeps working.
Regards
John

I have no idea why that would matter.