Find & Replace function not fully working

[Blesbie]

Dear Forum members,

I do have a question to the find& replace function. I would like to replace "." by "',"
In the initial file all cells are "general"

When I replace everything manually, it works as it should.
But if I record the macro and run it, numbers below 1 are correct (they stay in format "general"). Numbers 1 and higher become "number"-format, but without a comma, so they are way too big.

Do you have an idea where this is coming from and what I can to against it?

Recorded macro:

Please Login or Register  to view this content.

The problem:
Capture.PNG

I really hope for some help. Thanks a lot in advance
Greetings
Moni

[6StringJazzer]

Can you please attach the file instead of a screenshot?

[mjr veverka]

I have the same.
After inserting a "comma", the system recognizes a "dot" as a symbol of a grouping of digits.
Use "Replace" as a worksheet function, i.e.:

Please Login or Register  to view this content.

Example from MS:

Please Login or Register  to view this content.

or equivalent code, i.e.:

Please Login or Register  to view this content.

e.g.

Please Login or Register  to view this content.

[Blesbie]

Hej,
thanks for the quick reply.

@6StringJazzer, here is a part of the file. I guess more is not necessary, because it's quite a lot of data...

@porucha vevrku , Unfortunately I don't understand how the code works that you posted and the replace-line results in type mismatch

Today I also changed the language settings of my computer. I don't know, if it's related, but since then also the manual find&replace is not working anymore and gives just a really long number with two decimal separation comma. I guess it's some setting with decimal things, even when I switched this option off. But the macro problem is far more important.
Thanks
Moni

[mjr veverka]

Restore your original system settings, such you usually work on and try this below:
(Select array (data with dots) then choose macro)

Please Login or Register  to view this content.

[Blesbie]

Hej,
thanks, the code runs only without any error, if there are only cells with numbers marked. It results now for every number in the same, but unfortunately wrong order of magnitude.

27650.247 results with the macro in 27650247,00
0.0088 results with the macro in 88
Manually it works as it should be.

Does somebody understands what's happening there? Of course I can divide my numbers simply by 10000, but I would prefer to understand the problem as well.

Thanks :-)
Moni

[xlnitwit]

Hi,

I think the problem is the conflict between your regional settings and VBA's US regional settings. Perhaps try

Please Login or Register  to view this content.

since VBA will treat the full stop as a decimal separator but the replaced value will appear with your regional comma.

[jindon]

Try

Please Login or Register  to view this content.

[mjr veverka]

... 27650.247 results with the macro in 27650247,00
0.0088 results with the macro in 88 ...
Manually it works as it should be.

What are practically your original system settings in the text separator, decimal separator and symbol of a grouping of digits /sign grouping of numbers ?