CODEWITHSUNDEEP
excelvba
honkin
honkin

Reputation: 113

How to remove decimals on whole numbers

In Excel for Mac, I need to remove the zeros and decimal point from whole numbers generated by a formula. The VBA code basically takes any fraction typed and applies the formula =FRACTION+1, so 5/2 becomes 3.50, 15/8 is 2.88 and so on.

But when the fractions entered are going to be whole numbers, like 7/1, 10/1 etc, the whole numbers have .00 at the end.

How is it possible for that not to happen?

I am unsure if it something to be done post the VBA code doing its job, or whether it could be incorporated into the original code.

Just to let you know, if I format the cell to Custom and General after it has been converted, this solves the problem, but this cannot be done beforehand, as if the cell is Custom and General, when a fraction is entered, it becomes a date.

So here is the code I have. It works perfectly except for the fact that whole numbers have 2 decimal places.

Private Sub Worksheet_Change(ByVal Target As Range)
  On Error GoTo err_handler

  Application.EnableEvents = False

  If Target.CountLarge > 1 Then GoTo exit_handler
  If Target = Empty Then GoTo exit_handler

  If Not Intersect(Target, Columns("P")) Is Nothing Then
    With Target
      .NumberFormat = "@"
      .Value = CDbl(Evaluate(.Value & "+1"))
      .NumberFormat = "0.00"
    End With
  End If

exit_handler:
  Application.EnableEvents = True
Exit Sub

err_handler:
  MsgBox Err.Number & ": " & Err.Description
  Resume exit_handler
End Sub

cheers

Upvotes: 0

Views: 1313

Answers (2)

honkin
honkin

Reputation: 113

Thanks so much to @NautMeg who came up with this simple fix to my problem

If (.value - Fix(.value)) = 0 Then
        .NumberFormat = "0"
      Else
        .NumberFormat = "0.00"
      End If

Upvotes: 0

NautMeg
NautMeg

Reputation: 141

A quick if statement can sort this. Just change your line .NumberFormat = "0.00" to the following:

      If (.value - Fix(.value)) = 0 Then
        .NumberFormat = "0"
      Else
        .NumberFormat = "0.00"
      End If

The Fix() function removes the decimal part of the fraction so by deducting this from the original value, you're left with the decimal portion only. If what remains is zero then the number is a whole number and can be formatted accordingly.

Upvotes: 1

Related Questions