excel spreadsheet

[Guest]

I have created a budget with dollars and cents, however I would like to have
the cell round the number. For instance, if I type in $5.45 I'd like the
cell value to be $5.00; If I type $117.88, I'd like it to read $118.00.
How do I accomplish this?

 

[iliace]

You can use this formula:

=MROUND(A1,1)

Where A1 is the cell where you enter the data.

If you want this to happen automatically in the cell you're editing,
you will need a worksheet function like this (paste this in your
worksheet's code module):

Private Sub Worksheet_Change(ByVal Target As Range)
If (Len(Target.Value) And isNumber(Target.Value)) Then
Target.Value = _
Application.WorksheetFunction.MRound(Target.Value, 1)
Target.NumberFormat = _
"_($* #,##0.00_);_($* (#,##0.00);_($* " & _
Chr(34) & " - " & Chr(34) & "??_);_(@_)"
End If
End Sub

Private Function isNumber(num As Variant) As Boolean
Dim temp As Double

On Error Resume Next
temp = num - 1
On Error GoTo 0

isNumber = (Err.Number = 0)
Err.Clear
End Function

Additionally, you may want to define a named range in which you're
entering these figures, so that the event doesn't trigger for
something that's a number but not a budget amount (such as time). I
also put in an autoformat to display the number as currency, and to
ignore blank cells.

 

[iliace]

OOPS! I always forget this... sorry. Replace the Sub
Worksheet_Change with the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Excel.Range
Application.EnableEvents = False

For Each rng In Target
If (Len(rng.Value) And isNumber(rng.Value)) Then
rng.Value = _
Application.WorksheetFunction.MRound(rng.Value, 1)
rng.NumberFormat = _
"_($* #,##0.00_);_($* (#,##0.00);_($* " & _
Chr(34) & " - " & Chr(34) & "??_);_(@_)"
End If
Next rng

Application.EnableEvents = True
End Sub

 

[David Biddulph]

=ROUND(A1,0)

 

[Guest]

Hi Angie.
Simply, try this macro to be pasted in the worksheet's code module, like
Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub 'only column A
If Not IsNumeric(Target.Value) Then
MsgBox "----> Only numbers, please! <----"
Target.Value = ""
Exit Sub
End If
Target.Value = Int(Target.Value + 0.5)
End Sub

Regards,
Eliano