austrian number formats

[DianeG]

Hi

A client of mine works in the UK but deals with Austrian colleagues who use
a comma instead of decimal point in their calulations. When he receives the
spreadsheets he can't calculate in them. I was thinking around the lines of
using find and replace to swap the commas but I wondered if anybody else had
a more efficient way of doing it

Thanks in advance

Diane

 

[Gary''s Student]

If you have data that looks like:
12,34
and want to convert it into:
12.34

Then select the cells in question and run:

Sub commaconverter()
Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
Set rr = Intersect(Selection, r)
comma = ","
dot = "."
For Each cel In rr
cel.Value = Replace(cel.Value, comma, dot)
Next
End Sub

 

[DianeG]

Thanks for the response , I'm probably going to ask a very silly question
now, but as I don't use VBA, where do I type this in?

Regards

Diane

 

[Gary''s Student]

Not a silly question at all:


Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To use the macro from the normal Excel window:

1. ALT-F8
2. Select the macro
3. Touch Run



To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

For this macro:
1. install the macro
2. select an area to convert
3. run the macro


Update the post if you have any problems

 

[DianeG]

Thank you so much, I'll try it right now!!

Regards

Diane

 

[DianeG]

Brilliant, it works perfectly!! Thank you, How do I now put it into the
personal macro workbook to make this available to all books?

Regards

Diane

 

[Gary''s Student]

I am not 100% certain. Create a new post in:

http://www.microsoft.com/office/com...rosoft.public.excel.programming&lang=en&cr=US

 

[DianeG]

Ok, thanks so much for you help

I am not 100% certain. Create a new post in:

http://www.microsoft.com/office/com...rosoft.public.excel.programming&lang=en&cr=US

 

[Bob Phillips]

Doesn't Excel automatically account for that, I have never found a problem
that I recall.

 

[DianeG]

Doesn't Excel automatically account for that, I have never found a problem
that I recall.

 

[Bob Phillips]

Doesn't that suggest that they are formatting the cells as text and typing
in the commas? I can format a cell as text and enter 123.45 and still do
math on it. If I am correct, correcting the problem means that you can swap
workbooks with no hassle.

 

[Bernard Liengme]

First we need to see if you have a Personal workbook
Assuming not Xl2007: use Windows | Unhide. DO you see Personal.xls? IS so,
secret it.
Now copy Gary's stuff to a new module in that file just as you did before.

No Personal file? Use Tools | Macros | Record and specify you want recorded
macro in Personal file
Do a simple thing like selecting a cell and copying it to another cell; then
turn off recording.
Now you have a personal file

This second method save worrying about where you XLSTART folder lives; Excel
will always but the file Personal file into the right place
best wishes