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?

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.


On Oct 26, 3:39 pm, Angie wrote:
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?

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


On Oct 26, 4:49 pm, iliace wrote:
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.

On Oct 26, 3:39 pm, Angie wrote:



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?- Hide quoted text -

- Show quoted text -

=ROUND(A1,0)
--
David Biddulph

"Angie" wrote in message
...
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?

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


"Angie" wrote:

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?