Replace dot with comma with macro

I've created a small macro to replace a dot with a comma sign, but it doesn't work in all the cells I want to make the replacement. See attached file.

When pressing the button, the macro is supposed to replace the dot to a comma sign in the selected cell. In cells A3 and A4 it works, but in cells A1 and A2 the comma sign is placed after the decimals. I've tried to switch cell format between General and Number and also the number of decimals when using the Number format, but can't see any difference.

Originally Posted by royUK

It won't work if you haven't selected the cells first

The first two cells are formatted as Number, the next as General. This will affect the display

All the four cells have the General format before running the macro, but after selecting each cell and pressing the button I get the cell formats you describe. So from what I can see, the cells are identical by format and the same macro is used in all cells, I can't understand why the outcome differs???

Originally Posted by royUK

I've tried re-formatting the cells within the code

Please Login or Register  to view this content.

This doesn't work for me but it could be my version of Excel

It doesn't work for me neither.

What is your system separator set to?
Is it the continental system 1.000,00 or the UK/USA system 1,000.00?

What do you expect the result in each cell to return?

Originally Posted by teylyn

Hello Idde,

I'm with Marcol here. You probably use regional language settings with different delimiters for decimal points than US or UK format (JBeaucaire is probably US and RoyUK is, well, UK).

This will make it difficult to test your macro, unless someone uses exactly the same regional settings.

Please take a look in your Windows Control Panel and let us know what these settings are. Then the helpers here can apply the same settings and inspect the file and the macro under the same premises.

cheers,

Hi,

See attached screenshot for language options.

Originally Posted by Marcol

Aye. I had a problem like this when doing some work for a Danish company.
There was a lot of trial and error to get it right and it was a while ago and I can't remember the final solution.

Try this workbook, I'm not saying it will work for you, but if it throws errors when you open it post a screenshot of the result.
Save the file on your system and post the copy and I'll see if that jogs my memory,
The problem always recurred after saving on another system.

Cheers

[EDIT]
There will possibly also be a problem with formula, the delimiter you are likely to be using will be ";" (semi-colon) not "," (comma)

I didn't get any errors when I opened the workbook and from what I could see it looked OK. I saved it to my desktop and attached it below.

Originally Posted by snb

See what happens if the rightmost number isn't zero.

Hi,

It didn't make any difference for me.

Originally Posted by Marcol

I think the problem could be with the decimal separator settings in Excel.

Check this
Office Button > Excel Options > Advanced
What are your DecimalSeparator and ThousandsSeparator set to?

Try this

Please Login or Register  to view this content.

The loop is just to see if you get the same result as me.

I used your code, but there was no difference, 63.0000 became 630000,0000.

I have the Use system separators checked, with a comma as Decimal separator and a dot as Thousands separator in each gray marked field.

Marcol and snb,

Thanks for the help, but I still get, for example, the first value to be 630000, instead of 63,0000 that I want :S