Replacing dots with commas using macro

[maxest]

Hi,

I have a problem and maybe somebody can give me some good advice.

The problem is that I need to replace delimiter dots with commas,
otherwise numbers will be handeled as text. When I simply use Replace
function from Edit menu, it work perfectly - dots will be replaced with
commas and text will be converted to numbers.

I tried to record this function as a macro, but it doesn't work in
macros. It replaces dots with commas, but text won't be converted to
numbers and I can't use them in functions as SUM etc. Its strange
because it works when done manually but doesn't work in recorded macro.

How can I fix that?

Thank you!

 

[Guest]

It sounds like you have the comma switch taken care of. The following
changes the specified range to number format:
Sub ConvertNum()
Range("A1:A10").Select
Selection.NumberFormat = "0.00"
End Sub

hope that helps

 

[maxest]

No, it didnt help. I'll try to explain that situation better.

I'm using commas as decimal separator, but data that I work with, come
from another application (web-based) that uses dots as separator. When
paste data tables to a Excel chart all numbers are handeled as text. M
objective is to convert them to the numbers.

After using Replace function in macro it replaces dots with commas, bu
numres are still text and can't be ussed in financial functions. I
helps when I manually edit cell (F2) and hit Enter after that, but
cant edit all 500 cells manually.

When I use Replace function from Edit menu manually, it work perfectl
and I dont have problem with number that are in text format.

I have tried also to change format of cell in every way but it doesn
work. Work only F and Enter

 

[Guest]

Sorry about that, I got the commas and dots switched around. The number
format you are looking to use is european style (well pretty much anywhere
other than the U.S. and canada use it). In order to have a number (0.00) be
formatted like this > 0,00 and be used as a number in excel you've got to
change your global settings.

Add this into your code:
'You may want to put this in the workbook_open event of the workbook
'That way you don't have to think about it.
With Application
.DecimalSeparator = ","
.ThousandsSeparator = "."
.UseSystemSeparators = False
End With
'Now you can use the following.
'Don't worry about the dot because the globals are changed
Range("A1:A10").Select
Selection.NumberFormat = "0.00"

'You will also want to reset your globals.
'Possibly on a workbook_beforeclose event