Replace dot to comma using a macro doesn't work!!!

[Guest]

Hi!

I'm getting nut's at this...
Have a workbook wich is picking up external data (currency) from a website.
It gives me the values with a dot in them (i.e. 12.345)
That's not usable when I want to convert the currency to another one since
Excel can only calculate with comma values.
Ok, so I use search/replace and replace the dots with commas.
Works perfect!
Now I want to make a macro out of it so I don't have to find the columns and
convert manually every time.
I record the macro and do what I normally do when I replace the dots.
Works fine.
I can see the values change the way they should when recording the macro,
but then when I run my macro it doesn't do what it should!!!

For example:
I have a value of 1.031 one at 1.1053 and another of 1.079
When replacing manually the result will be 1,031 and 1,1053 and 1,079 but
when running my recorded macro the end result is 1031 and 11053 and 1079!!!!!!
What's wrong??
Have tried formatting my cells in all different ways but it still ends up
with the same error all the time...

Is it a bug or is it that I should take a course in MS Excel??

Please help me out....

Regards
/Chris

 

[Guest]

Try this:

Sub con()
Dim s As String
s = Selection.Value
s = Application.Substitute(s, ".", ",")
Selection.Value = s
End Sub

Select the cells and then run the sub

 

[Guest]

to prevent Excel from re-formatting the value, try:

Sub con2()
Dim s As String
Dim r As Range
For Each r In Selection
With r
s = .Value
s = Application.Substitute(s, ".", ",")
.Clear
.NumberFormat = "@"
.HorizontalAlignment = xlRight
.Value = s
End With
Next
End Sub