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

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(xlCellTypeConst ants)
Set rr = Intersect(Selection, r)
comma = ","
dot = "."
For Each cel In rr
cel.Value = Replace(cel.Value, comma, dot)
Next
End Sub

--
Gary''s Student - gsnu200792


"DianeG" wrote:

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

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" wrote:

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(xlCellTypeConst ants)
Set rr = Intersect(Selection, r)
comma = ","
dot = "."
For Each cel In rr
cel.Value = Replace(cel.Value, comma, dot)
Next
End Sub

--
Gary''s Student - gsnu200792


"DianeG" wrote:

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

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
--
Gary''s Student - gsnu200792


"DianeG" wrote:

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" wrote:

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(xlCellTypeConst ants)
Set rr = Intersect(Selection, r)
comma = ","
dot = "."
For Each cel In rr
cel.Value = Replace(cel.Value, comma, dot)
Next
End Sub

--
Gary''s Student - gsnu200792


"DianeG" wrote:

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

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

Regards

Diane

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200792


"DianeG" wrote:

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" wrote:

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(xlCellTypeConst ants)
Set rr = Intersect(Selection, r)
comma = ","
dot = "."
For Each cel In rr
cel.Value = Replace(cel.Value, comma, dot)
Next
End Sub

--
Gary''s Student - gsnu200792


"DianeG" wrote:

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

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" wrote:

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
--
Gary''s Student - gsnu200792


"DianeG" wrote:

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" wrote:

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(xlCellTypeConst ants)
Set rr = Intersect(Selection, r)
comma = ","
dot = "."
For Each cel In rr
cel.Value = Replace(cel.Value, comma, dot)
Next
End Sub

--
Gary''s Student - gsnu200792


"DianeG" wrote:

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

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

http://www.microsoft.com/office/comm...&lang=en&cr=US
--
Gary''s Student - gsnu200792


"DianeG" wrote:

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" wrote:

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
--
Gary''s Student - gsnu200792


"DianeG" wrote:

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" wrote:

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(xlCellTypeConst ants)
Set rr = Intersect(Selection, r)
comma = ","
dot = "."
For Each cel In rr
cel.Value = Replace(cel.Value, comma, dot)
Next
End Sub

--
Gary''s Student - gsnu200792


"DianeG" wrote:

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

Ok, thanks so much for you help

"Gary''s Student" wrote:

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

http://www.microsoft.com/office/comm...&lang=en&cr=US
--
Gary''s Student - gsnu200792


"DianeG" wrote:

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" wrote:

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
--
Gary''s Student - gsnu200792


"DianeG" wrote:

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" wrote:

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(xlCellTypeConst ants)
Set rr = Intersect(Selection, r)
comma = ","
dot = "."
For Each cel In rr
cel.Value = Replace(cel.Value, comma, dot)
Next
End Sub

--
Gary''s Student - gsnu200792


"DianeG" wrote:

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

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


"DianeG" wrote in message
...
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

On our worksheets adding values with commas gives a result of zero as th
evalues are taken as text


"Bob Phillips" wrote:

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


"DianeG" wrote in message
...
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

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.

--
__________________________________
HTH

Bob

"DianeG" wrote in message
...
On our worksheets adding values with commas gives a result of zero as th
evalues are taken as text


"Bob Phillips" wrote:

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


"DianeG" wrote in message
...
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

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"DianeG" wrote in message
...
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" wrote:

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
--
Gary''s Student - gsnu200792


"DianeG" wrote:

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" wrote:

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(xlCellTypeConst ants)
Set rr = Intersect(Selection, r)
comma = ","
dot = "."
For Each cel In rr
cel.Value = Replace(cel.Value, comma, dot)
Next
End Sub

--
Gary''s Student - gsnu200792


"DianeG" wrote:

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