Changing point to comma in numbers, macro gives another result compared to manually changing

[RobbertN]

Hello,

I'm importing data containing numbers in a single row into excel from another source.
The numbers are written with a point (.) as a decimal sign (for example; 1.20 , 231.1, 9.0)
Every cell contains only one number though.
I'm located in The Netherlands, so I have to convert those numbers, so the point is replaced by a comma-sign.

I've found a way to do this, by selecting the numbers, changing the cell type to text, using the replace function to search for every point and to replace it by a comma. After this, I change the cell type to number.

This solves the full problem, so I thought I would record this into a macro.
I recorded the macro, and used the same procedure.
After this, I pasted the same dataset into excel, and executed the macro.
Unfortunately, this resulted in numbers formatted as text. (indicated with the familliar green little triangle)

To me, it seems the same procedure on the same data gives different results if executed with a macro instead of manually... How can I solve this?

 

[VBA Geek]

you change your excel settings directly from VBA (don't remember where they're located if you wish to do it manually but they're somewhere) and not having to worry about it anymore

Sub chg()
    Application.DecimalSeparator = ","
    Application.ThousandsSeparator = "."
End Sub

 

[RobbertN]

You mean, instead of changing the data, I let excel use a point as the decimal separator?

 

[VBA Geek]

yes, it will use a . as decimal separator and a , for thousands

you must also include this line of code

Application.UseSystemSeparators = False

 

[RobbertN]

I'm sorry, but that is not really an option here... Partly because i'm used to using comma's, and because the data must be reported with comma's after the calculations in Excel...

 

[VBA Geek]

ok, select your numbers and try then

Sub ChangeCommas()
    Selection.Replace ".", ",", xlPart, xlByRows, False
End Sub

 

[RobbertN]

Thanks for the reply, this does change the points into comma's, but the numbers are formatted as text... This is the main problem I wrote about in the opening post. The result is excel won't carry out calculations with "text stored numbers"...

(image)

 

[VBA Geek]

ok one more try:

Sub ChangeCommas()
    Selection.Value = Application.Transpose(Evaluate(Replace("Transpose(SUBSTITUTE(@,"","","".""))", "@", Selection.Address)))
End Sub

 

[RobbertN]

Yes!! This works!

Thank you very much!