Comma replacement

[Raul Sousa]

I am in Europe. I use by default coma as decimal separator.
In a particular situation I need to replace the coma by point. I have this
code:
Columns("p").Select
Selection.NumberFormat = "General"
Selection.Replace What:=",", Replacement:="."
But nothing is happening. Column P still has comma as decimal separator.

What am I doing wrong?

 

[abumustapha]

on the sheet that you are working on, select the column , or range of cells,
set the format to general. then go to 'Edit', 'Replace', put a ',' in the
Find What and a '.' in the Replace With and then hit 'Replace All'.
I think that should work.

 

[T Lavedas]

I am in Europe. I use by default coma as decimal separator.
In a particular situation I need to replace the coma by point. I have this
code:
Columns("p").Select
Selection.NumberFormat = "General"
Selection.Replace What:=",", Replacement:="."
But nothing is happening. Column P still has comma as decimal separator.

What am I doing wrong?

I think this is failing because the comma is not actually part of the
contents of the cell, unless it is formatted as Text. In all number
formats the comma is 'virtual' - for display purposes only. In such
case, you will either need to build a Custom format that replaces the
comma with a dot or the cells will need to be formatted as text and
actual periods inserted were desired (probably not the way you want to
go).

Tom Lavedas
===========
http://members.cox.net/tglbatch/wsh/

 

[Raul Sousa]

Unfortunately it does not work.
If do this operation by code, it does not work, Even if I format that column
as text.

But,
If I do it manually, this is, select the column, and set the format to
general. Then go to 'Edit', 'Replace', put a ',' in the Find what and a '.'
in the Replace With and then hit 'Replace All'.
It works

I think it should also work with this code.

 

[T Lavedas]

Unfortunately it does not work.
If do this operation by code, it does not work, Even if I format that column
as text.

But,
If I do it manually, this is, select the column, and set the format to
general. Then go to 'Edit', 'Replace', put a ',' in the Find what and a '.'
in the Replace With and then hit 'Replace All'.
It works

I think it should also work with this code.

If the comma is actually in the text in the column, which your
statements about the manual operation proves is true, then the code
technique should work. A recorded macro for the manual operation
results in the following ...

Columns("P").Select
Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False

I think the significant difference is the LookAt:=xlPart, which is
needed to find commas as *part* of another string. Maybe that's the
problem.

Tom Lavedas
===========
http://members.cox.net/tglbatch/wsh/