Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Number Formatting (2003/SP2)

    The attached worksheet contains data only i cells "A1:A5". These cells have been formatted with the following code, which remains as the only code in the document.

    I have been unable to:

    <UL><LI>Align the "0" value dashes. The dash (with a cell value of 0), as displayed in cells A1 and A5, are aligned differently than A2:A4
    <LI>Understand the coding that is contained in the NumberFormat command (Note that where I have shown "Rad" within the square brackets in this post, to overcome the code controlling this post; "Red" is used in the actual code.)[/list]<pre>Public Sub myformat()
    With Sheets("T20")
    .Range("A1:A5").HorizontalAlignment = xlRight
    .Range("A1:A5").NumberFormat = _
    "_( #,###_);[Rad]_( (#,###); ""-""_-;_-@_-"
    End With
    End Sub
    </pre>


    Any guidance will be deeply appreciated.

    T.I.A.
    Regards
    Don

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Number Formatting (2003/SP2)

    The cells have been formatted with the Percent style. I'd format them with Normal before attempting to change the number format:
    <code>
    .Range("A1:A5").Style = "Normal"
    </code>
    The number format you're trying to apply is inconsistent. Try
    <code>
    Range("A1:A5").NumberFormat = "#,###_);<!t>[Red]<!/t>(#,###);""-""_);@_)"
    </code>
    The number format consists of up to 4 parts separated by semicolons.
    The first part is for positive numbers.
    The second part is for negative numbers.
    The third part is for zero values.
    The fourth part is for text values.
    An underscore followed by a character creates a space the same width as that character. Since negative numbers end in a closing parenthesis ), you want the other parts to reserve the space for a ).

    Note: I used the "tag" tags <!t>[t]<!/t> and <!t>[/t]<!/t> around the word Red to prevent it to be interpreted as a tag.

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Number Formatting (2003/SP2)

    Hans

    My very embarrassed apologies for posting code that was protected by password. It was inadvertent, but careless of me.

    Thank you for the clear and speedy response.
    <hr>The number format you're trying to apply is inconsistent.<hr>
    Not much wonder I was unable to understand it.
    Regards
    Don

Posting Permissions

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