Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Number formats in macro (Excel 2000)

    I am attempting to automate a text to column function, and I want to select these columns to have the standard "," format, but I keep getting stopped. Is this not the correct way to have columns H through L formatted to a 15 width and comma format?
    With Columns("H:L").ColumnWidth = 15
    .NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* " - "??_);_(@_)"
    End With
    I copied that from the custom format dialog box and added the quotes.

    Or, how do you do the "double clicking on border to set columns to correct width" action that you can do on the spreadsheet? I could select all the columns in the range and get them to "auto-size" and not set specific widths.

    TYIA

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

    Re: Number formats in macro (Excel 2000)

    1. You must put the width instruction on a separate line in the With ... End With block, not in the With ... line.
    2. You can use AutoFit to mimic double clicking the right border of the columns
    3. Your number format string contains quotes. To include quotes within a string, you must double them, otherwise VBA gets confused as to where the string ends.<pre>With Columns("H:L")
    .AutoFit
    .NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* "" - ""??_);_(@_)"
    End With</pre>


  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number formats in macro (Excel 2000)

    So the quotes *are* required in the property or are not required. I do not want anything but what one normally sees as the comma format.
    Thanks.

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

    Re: Number formats in macro (Excel 2000)

    Next time things don't work, try recording a macro whilst setting things up. Then study the recorded macro and use the useful parts of it. Mostly Excel will have the syntax right (but not always!).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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