different number format

[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

 

[Orlando Magalhães Filho]

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

 

[daniele]

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

 

[Orlando Magalhães Filho]

Hi Eric van Uden,

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

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

Regards,

Orlando

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.

 

[daniele]

Thanks, Orlando and Eric!

D

Hi Eric van Uden,

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

Regards,

Orlando


to