Cutting off numbers to two decimal places using a SCRIPT

T

tilt64

Hi, I would like to know if any of you would know how to change the cel
contents in the following situation:
I enter 2.1342
I want excel to change it to 2.13
not just changing the formatting, but actually changing the number t
round to the hundreths place. When I go back to that cell, I don'
want to know that there was ever a number there besides 2.13, does tha
make sense? I want it to edit my number for me.
Thanks ahead of time,
We
 
N

Nigel

Two choices

1. Either put a formula into a target cell on the worksheet referencing the
cell with the source

Range("D2").Formula = "=ROUND(B2,2)"

2. Or in program use

YourNumber = Round(YourNumber, 2)

Cheers
Nigel
 
J

JE McGimpsey

You can do it without VBA if you choose Tools/Options/Calculation and
check the Precision as displayed checkbox. However that's a global
setting for the workbook, so you may not want to use that.

If the value is a result of a calculation, wrap the calc with ROUND:

=ROUND(<your calc here>,2)

Using VBA, if the entry is made manually or remotely, put this in the
worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Application.EnableEvents = False
With Range("A1")
.Value = Application.Round(.Value, 2)
End With
Application.EnableEvents = True
End If
End Sub
 
Top