Find and Replace Problem

[jborges]

I trying to program a Subroutine to replace a "." for a "," in a column of imported data in order to convert this text into numbers with decimals:

1.0000 --> 1,0000

If I do manually or with a Macro I get the result, if I use the same code than in the Macro for the subroutine I get an undesired result:

1.0000 --> 10000

Do you any idea ?

[NateO]

Not sure, should

1,0000

really be

 10,000.00?

Or do you want the , to represent a decimal?

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-04-29 15:45 ]

[jborges]

I would like teh "," to represent a decimal separator.

Thanks

On 2002-04-29 15:44, NateO wrote:
Not sure, should

really be

 10,000.00?

Or do you want the , to represent a decimal?

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-04-29 15:45 ]

[NateO]

Howdy, better late than never? I assume your imported numbers are operating as text for some sort of visual effect (making numeric formatting a bogus option). To keep the numbers as text and create the effect you're looking for, I used the following procedure:

Sub rplc()

Dim cl As Range

For Each cl In [a1:a500] 'change to your range

    If Not IsEmpty(cl.Value) Then _

    cl.Value = "'" & Application.Substitute(cl.Value, ".", ",")

Next cl

End Sub