Excel-formatting

[Chi]

Hi,

Would you please show me how I can format the cells appear like the below
example?
Ex: if I enter to a cell 100, it will become 10
If I enter to a cell 95, it will become 9.5
And if I enter to a cell 90, it will show 9.

Thanks
Chi

Chi

 

[TGV]

Tools-Options-Edit-Fixed Decimal - Uncheck the check box or change the Places
value as "0".

TGV

 

[TGV]

Tools-Options-Edit-Fixed Decimal - Uncheck the check box or change the Places
value as "0".

TGV

 

[TGV]

Tools-Options-Edit-Fixed Decimal - Uncheck the check box or change the Places
value as "0".

TGV

 

[Chi]

Hi TGV,

Thank you for your help, but It didn't work, not thing happen, after I
uncheck the check box.
Chi

 

[Gord Dibben]

The cell values won't change after you uncheck the FD option.

Once entered, they are fixed at that value.

Re-enter numbers.


Gord Dibben MS Excel MVP

 

[Chi]

Hi Gord,

It works!!! Thank you for your explanation. In my case, I need only few
columns, instead of whole sheet. Is there away to do that?
I am using 2007 so would you please also show me where the Fixed Decimal is?

Thanks
Chi

 

[Gord Dibben]

Office Button>Excel Options>Advanced>Editing Options>Automatically insert a
decimal point.

You would have to use event code for just a few columns.

I can provide that. Which columns are you talking about?

Your first post is a bit ambiguous or some of us are misreading it.

What exactly do you want?

Enter 100 and it becomes 10?

Enter 95 and it becomes 9.5?

That's what I got from your initial description.


Gord

 

[Chi]

Hi Gord,

Sorry if my first post is a bit ambiguous.

Yes! Enter 100 and it becomes 10
Enter 95 and it becomes 9.5


I would like column D, C and E work that way -(Enter 100 and it becomes 10
and enter 95 and it becomes 9.5)


Thank you very much for your patience.

Chi

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range(Target(1).Address), _
Range("C:C, D, E:E")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
With Target
.Value = .Value / 10
End With
endit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code"

Copy/paste the above into that sheet module.

Alt + q to return to the Excel window.


Gord

 

[Chi]

Hi Gord,

Thank you for your help!
I copied the code and used it. However, all I see was the numbers “0"
appeared when I put the cursor in the C, D or E columns.

Please help!
Chi

 

[Chi]

Hi TGV,

I changed the Places value as -1 and it worked! It affected whole sheet. In
my case, I only would like the numbers changed in columns D, C, and E.
Thanks
Chi

 

[Chi]

Good Morning Gord,
It works!!! I tried to test the code many times and now I know it works!
However, I need to put the cursor in the cell C2 “2 times†to make it works.

Ex: As soon as I put the cursor in the C2, the number "0" appeared/then I
enter 145/place the cursor out off C2/then put it in C2 again/the number 14.5
appeared.

I wish that I enter 145, it will be 15.5 as soon as I place the cursor out
off C2

Thanks
Chi

 

[Chi]

Good Morning Gord,
It works!!! I tried to test the code many times and now I know it works!
However, I need to put the cursor in the cell C2 “2 times†to make it works.

Ex: As soon as I put the cursor in the C2, the number "0" appeared/then I
enter 145/place the cursor out off C2/then put it in C2 again/the number 14.5
appeared.

I wish that I enter 145, it will be 14.5 as soon as I place the cursor out
off C2

Thanks
Chi

 

[Gord Dibben]

Have you altered the code I posted?

Perhaps you changed it to

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Which would give you zero when the cell was first selected.


Gord

 

[Chi]

Hi Gord,

It works perfectly!!! Sorry. Since i don't know anything about coding, I
accidently changed the code. You were right. Thank you very much for your
patience and help!
Chi

 

[Gord Dibben]

Good to hear you're sorted out.

When given code, you should paste it into the module as is.

Any editing can be done on ranges etc. but the code will have been tested
for the type of event so that should not be changed in most cases.


Gord