different number format

  • Thread starter Thread starter daniele
  • Start date Start date
D

daniele

Hi, this is probably an easy question but I am at a loss been an excel
newbie.

I have XP with my local regional settings, where the number format is
1.000,29 (onethousand and 29/100)
on a specific excel sheet I need to revert these settings, in order to paste
a large amount of data
1000.29 (onethousand and 29/100)

anybody can suggest how to do this?

TIA
 
Hi Daniele,

I think you shouldn't change your setting, paste the data and then use the
code below to fix the numbers:

Sub TextToNumberOnActiveColumn()
Dim r As Integer
Dim c As Range
r = ActiveCell.EntireColumn.Range("A1").CurrentRegion.Rows.Count
ActiveCell.EntireColumn.Range("A1").Resize(r, 1).NumberFormat =
"#,##0.00"
For Each c In ActiveCell.EntireColumn.Range("A1").Resize(r, 1).Cells
If IsNumeric(c.Value) Then c.FormulaR1C1 = c.Value
Next
End Sub


HTH
 
Hi Orlando,

I warned you I am a newbie :-)

could you tell me:
a. what would this code do, and
b. how *exactly* should I use it?

muito obrigado

Daniele
 
Hello Daniele,

As I don't see Orlando's answer, I will try to offer some quick help:

Copy his code (from "Sub" Through "End Sub"). Use Ctrl+C.
Richtclick on the sheettab of the sheet you pasted the data in, or where you
want to change the format.
Click the bottom option (show code).
Paste the code. Use Ctrl+V.
Make sure the lines are pasted correctly. The e-mail process sometimes cuts
up lines and puts returns where they shouldn't be (for your present
purpose). E.g.: I tried this and found the line:

ActiveCell.EntireColumn.Range("A1").Resize(r, 1).NumberFormat ="#,##0.00"

Had been cut after the equal sign and showed up red in Excel. Just put the
cursor in the right place and hit delete to remove the line break.

Doing this brought you into the Visual Basic Editor. Now close the editor.

In the worsheet, activate a column with data that you want to correct. to do
this, just click the right column header, the button-grey cell with the
capital letter (A through IV).
Then from the worksheet hit Alt+F8 (Or Extra>Macro>Macro's), select
Orlando's TextToNumberOnActiveColumn macro and click Execute.

It will correct the formatting for that column in the way you asked.

Hope he doesn't mind me barging in, and hope this helps you.

Have a beautiful day.


--
Eric van Uden
at the foot of the 'bridge too far'


daniele said:
Hi Orlando,

I warned you I am a newbie :-)

could you tell me:
a. what would this code do, and
b. how *exactly* should I use it?

muito obrigado

Daniele
Click to expand...
 
Hi Eric van Uden,
Hope he doesn't mind me barging in, and hope this helps you.
Click to expand...
No problem for me. If Daniele understood your detailed and correct
instruction, I'll be glad.

Regards,

Orlando


Eric van Uden said:
Hello Daniele,

As I don't see Orlando's answer, I will try to offer some quick help:

Copy his code (from "Sub" Through "End Sub"). Use Ctrl+C.
Richtclick on the sheettab of the sheet you pasted the data in, or where you
want to change the format.
Click the bottom option (show code).
Paste the code. Use Ctrl+V.
Make sure the lines are pasted correctly. The e-mail process sometimes cuts
up lines and puts returns where they shouldn't be (for your present
purpose). E.g.: I tried this and found the line:

ActiveCell.EntireColumn.Range("A1").Resize(r, 1).NumberFormat ="#,##0.00"

Had been cut after the equal sign and showed up red in Excel. Just put the
cursor in the right place and hit delete to remove the line break.

Doing this brought you into the Visual Basic Editor. Now close the editor.

In the worsheet, activate a column with data that you want to correct. to do
this, just click the right column header, the button-grey cell with the
capital letter (A through IV).
Then from the worksheet hit Alt+F8 (Or Extra>Macro>Macro's), select
Orlando's TextToNumberOnActiveColumn macro and click Execute.

It will correct the formatting for that column in the way you asked.

Hope he doesn't mind me barging in, and hope this helps you.

Have a beautiful day.
Click to expand...
 
Thanks, Orlando and Eric!

D

Orlando Magalhães Filho said:
Hi Eric van Uden,

No problem for me. If Daniele understood your detailed and correct
instruction, I'll be glad.

Regards,

Orlando


to
Click to expand...
 
Back
Top