Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Create Macro to Display a 0 not 0.00 in all Cells (2000)

    Hi,

    Right I'm a novice at Excel Macros, so please be gentle with me....Anyway,

    I need to generate a universal Macro (Within Excel (IE accessable by all opened Files with the Program)) to change 0.00 to 0 in all number cells when run.
    It would also be useful to remove .00 where a number is whole, but preserve and display up to 12 Decimal Places. The number also need to display Commas for the 1,000,000 etc.

    The File can be up to 100,000 rows x 50 Columns.

    This problem is generated by a formatted Output from Access

    Any Assistance would be greatfuly received.

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

    Re: Create Macro to Display a 0 not 0.00 in all Cells (2000)

    Wouldn't it be easier to perform an unformatted export from Access?

    Note: Excel versions prior to Excel 2007 cannot handle 100,000 rows. The number of rows in a worksheet is 65,536.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Create Macro to Display a 0 not 0.00 in all Cells (2000)

    Yes, Your Right of Course, but I need to the Spreadsheet to be in Font: Verdena, plus ordered and I like the control of Field Format being with Access rather than falling foul of the Excel Cellular system.

    To be honest I'm also Curious about the Structure (syntax) of a macro for Universal Changes on a Spreadsheet, including all Cells.

    Can it be Done???

  4. #4
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Create Macro to Display a 0 not 0.00 in all Cells (2000)

    Ok no Problem Were below 60,000 for the moment it'll be a few weeks before the database gets above and I'm generally only changing upto 100 rows at a time.

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

    Re: Create Macro to Display a 0 not 0.00 in all Cells (2000)

    If you're going to do some formatting in Excel anyway (whether manually or through a macro), there is no need to do a formatted export - you can easily apply the Verdana font in Excel.

    Another point: if you want to display whole numbers without decimals, and fractional numbers with up to 12 decimal places, you'll have to apply an individual custom format to each cell. A macro to do that will be extremely slow for large spreadsheets. I think you should reconsider whether you really need this. Who is going to look at the individual numbers in a spreadsheet with tens of thousands of rows? Fancy formatting is useful if you're creating a small table for presentation purposes, not for huge data tables.

  6. #6
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Create Macro to Display a 0 not 0.00 in all Cells (2000)

    Problem is that I've just put a system in for the Access side of things and his "nibship" is asking for specificallythe reformat for presentation. Generally we'll be running smaller files only 100 rows so it shouldn't be a problem speed wise. No worries I'll sort it out some other way. [img]/forums/images/smilies/biggrin.gif[/img] (Thats a cheeky grin by the way) But it would be nice to get that; select compare and cross-reference code format for an excel Macro, if you can give some pointers......
    Pretty Please.

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

    Re: Create Macro to Display a 0 not 0.00 in all Cells (2000)

    There was something familiar about this question, and now I know why: I already wrote the macro for you: see <post:=639,806>post 639,806</post:>. <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Create Macro to Display a 0 not 0.00 in all Cells (2000)

    Hi graliv1,

    If the data are being imported by Excel from a data file (eg csv format), then any leading or trailing 0s are likely to be stripped from the data by default. The easiest way to approximate what you want with the imported data would be to apply a custom cell format in Excel for the whole of the range you want affected. For example, custom formatting the range with '#,##0.0###########;-#,##0.0###########;0' will return all your numbers with thousands separators and up to 12 decimals, 0s as '0' and any other whole numbers with a single 0 decimal (eg 1.0).

    If that's not good enough, the following re-working of Hans' macro should do it for you fairly quickly:
    Sub FormatCustom()
    Dim oCell As Range
    Selection.NumberFormat = "#,##0.0###########"
    For Each oCell In Selection
    If IsNumeric(oCell) Then
    If oCell = Int(oCell) Then oCell.NumberFormat = "#,##0"
    End If
    Next oCell
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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