Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / 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,784

    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,784

    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
    Code:
    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?

    Code:
            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,784

    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:
    Code:
    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,784

    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 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Double Dash in Formulas
    By SerenityNetwork in forum EXCEL HELP
    Replies: 18
    Last Post: October 20th, 2015, 18:49
  2. Parse After Each Dash
    By deusaduro in forum EXCEL HELP
    Replies: 12
    Last Post: August 9th, 2007, 18:10
  3. Show texts string after a dash
    By Susan Soo in forum EXCEL HELP
    Replies: 2
    Last Post: August 11th, 2005, 15:58
  4. Only show text string before a dash
    By Susan Soo in forum EXCEL HELP
    Replies: 3
    Last Post: August 7th, 2005, 00:55
  5. Removing a - (dash) at start and end of string
    By darkdarkdark in forum EXCEL HELP
    Replies: 6
    Last Post: May 14th, 2005, 02: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