Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Sep 2003
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How all negative numbers round to 0 (Excel 2000)

    Hello all, i have a sheet with a few numbers positive and negative. Now i want that all negative numbers that i type in that sheet change to 0
    So if i type in -50 it must change to 0. Is this possible??

    Gerwin

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: How all negative numbers round to 0 (Excel 2000)

    Add this code to the worksheet object (not a normal module) in VB:

    <pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rCell As Range
    Application.EnableEvents = False
    For Each rCell In Target
    If rCell.Value < 0 Then rCell.Value = 0
    Next
    Application.EnableEvents = True
    Set rCell = Nothing
    End Sub</pre>


    Steve

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How all negative numbers round to 0 (Excel 2000)

    If you only want to show them as zeroes, but calculate with their real value, use this custom format:
    General;"0"
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Star Lounger
    Join Date
    Sep 2003
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How all negative numbers round to 0 (Excel 2000)

    General;"0" is nice but when i type a positive number you can't see him???
    how can i change that?

    Regards Gerwin

  5. #5
    Star Lounger
    Join Date
    Sep 2003
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How all negative numbers round to 0 (Excel 2000)

    steve your vb code is not working by my sheet, i think i do something wrong.
    I go to the vb editor and then i past your macro in ThisWorkbook.

    What goes wrong?? because nothing happens

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How all negative numbers round to 0 (Excel 2000)

    Worked fine for me.

    BTW: the code Steve gave you belongs behind the worksheet that needs the zeroes. Rightclick its tab and choose view code. Paste it there.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How all negative numbers round to 0 (Excel 2000)

    The code does NOT go in ThisWorkbook, it goes in the module behind the sheet that needs the minuses changed to zero. Right click on the sheet tab and select View Code from the pop up menu. Paste the code there.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •