Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Format Cells To Show Dash For Zero

  1. #1
    Join Date
    12th March 2007
    Posts
    128

    Format Cells To Show Dash For Zero

    In excel if you press F5\Special, you will be given to select cells with some criteria but there is no criteria to select cells with zero value in a selected range. Is any such option is available in excel or a macro is needed for the same?

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,714

    Re: Select Cells With Zero Value In A Range

    Why not AutoFilter to show only zeros then select visible cells. Or, if copying simply select all rows and copy. By default only non-filtered cells are copied.

  3. #3
    Join Date
    12th March 2007
    Posts
    128

    Re: Select Cells With Specified Value

    Using Auto filter option will not serve the purpose as I have an addin which converts a number into special number format which I want display in "dash" format Also the data are scattered through out the sheets. Using Auto filter will make the task tedious since it will equal to selecting a particular cell with zero value and then clicking "," to convert 0 to -.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,714

    Re: Select Cells With Specified Value

    What is you are trying to do exactly? Seems to me all you need is standard Excel number format to show 0 as -

  5. #5
    Join Date
    12th March 2007
    Posts
    128

    Re: Select Cells With Specified Value

    Actually I am using the following number format code
    VB:
    Public Sub IndianNumberStyle() 
        Dim cell As Range 
        Dim mFormat As String 
        For Each cell In Selection 
            If cell.Value > 0 Then 
                mFormat = "[>=10000000]#\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0" 
            ElseIf cell.Value < 0 Then 
                If cell.Value >= -99999 Then 
                    mFormat = "(#,##0);(#,##0)" 
                Else 
                    mFormat = "[<=-10000000](#\,##\,##\,##0);[<=-100000](##\,##\,##0);(##,##0)" 
                End If 
            End If 
            cell.NumberFormat = mFormat 
        Next cell 
         
    End Sub 
    
    
    THis when applied to the numbers changes - to 0 whereas I want that 0 shall be displayed as -

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    24th December 2004
    Location
    Nanaimo, Vancouver Island, British Columbia, Canada
    Posts
    2,464

    Re: Select Cells With Specified Value

    Hi,

    Add a line to convert the zero?

    VB:
    If cell.Value > 0 Then 
        mFormat = "[>=10000000]#\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0" 
    ElseIf cell.Value < 0 Then 
        If cell.Value >= -99999 Then 
            mFormat = "(#,##0);(#,##0)" 
        Else 
            mFormat = "[<=-10000000](#\,##\,##\,##0);[<=-100000](##\,##\,##0);(##,##0)" 
        End If 
         'add for zero value
    ElseIf cell.Value = 0 Then 
        cell = "-" 
        cell.HorizontalAlignment = xlRight 
    End If 
    
    
    Cheers,

    dr

    "Questions, help and advice for free, small projects by donation. large projects by quote"

    http://www.ExcelVBA.joellerabu.com

  7. #7
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,714

    Re: Select Cells With Specified Value

    Why not the standard Excel accounting format so the cell stays as a number? The code for the NumberFormat would be;

    _-* #,##0_-;-* #,##0_-;_-* "-"_-;_-@_-

  8. #8
    Join Date
    12th March 2007
    Posts
    128

    Re: Select Cells With Specified Value

    Finally I have rectified the code as below:
    VB:
    Public Sub IndianNumberStyle() 
        Dim cell As Range 
        Dim mFormat As String 
        For Each cell In Selection 
            If cell.Value > 0 Then 
                mFormat = "[>=10000000]#\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0" 
            ElseIf cell.Value < 0 Then 
                If cell.Value >= -99999 Then 
                    mFormat = "(#,##0);(#,##0)" 
                Else 
                    mFormat = "[<=-10000000](#\,##\,##\,##0);[<=-100000](##\,##\,##0);(##,##0)" 
                End If 
            End If 
            If cell.Value = 0 Then 
                cell = "-" 
                cell.HorizontalAlignment = xlRight 
            End If 
            cell.NumberFormat = mFormat 
        Next cell 
    End Sub 
    
    
    Is this allright?

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,714

    Re: Select Cells With Specified Value

    ....
    Why not the standard Excel accounting format so the cell stays as a number? The code for the NumberFormat would be;

    _-* #,##0_-;-* #,##0_-;_-* "-"_-;_-@_-

  10. #10
    Join Date
    12th March 2007
    Posts
    128

    Re: Format Cells To Show Dash For Zero

    I cant use standard Excel accounting format as the comma situation differs from my code. change comes from umbers having six or more digits.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 2 users browsing this thread. (0 members and 2 guests)

Possible Answers

  1. Parse After Each Dash
    By deusaduro in forum EXCEL HELP
    Replies: 12
    Last Post: August 9th, 2007, 17:10
  2. Double Dash in Formulas
    By SerenityNetwork in forum EXCEL HELP
    Replies: 17
    Last Post: September 4th, 2006, 10:31
  3. Show texts string after a dash
    By Susan Soo in forum EXCEL HELP
    Replies: 2
    Last Post: August 11th, 2005, 14:58
  4. Only show text string before a dash
    By Susan Soo in forum EXCEL HELP
    Replies: 3
    Last Post: August 6th, 2005, 23:55
  5. Removing a - (dash) at start and end of string
    By darkdarkdark in forum EXCEL HELP
    Replies: 6
    Last Post: May 14th, 2005, 01:53

Bookmarks

Posting Permissions

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