+ Reply to Thread
Results 1 to 9 of 9

VBA to format numbers with commas

  1. #1
    Forum Contributor
    Join Date
    10-28-2003
    Location
    Happyville, USA
    MS-Off Ver
    365
    Posts
    294

    VBA to format numbers with commas

    Hello everyone,

    I am having a problem inside my macro having the data in my pivot table to be correctly formated. I would like my data seperated at every thousand with a comma (example: 1,000). I have been using the code
    HTML Code: 
    DataBodyRange.NumberFormat = "#,##0
    but it is not putting the comma into the data. Any idea of what is wrong?

    Thank you so much for your help.

    Thanks

    Eddie
    Last edited by punter; 08-28-2014 at 05:30 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: VBA to format numbers with commas

    Try adding an ending quotation mark ("#,##0").

  3. #3
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: VBA to format numbers with commas

    Can you attach a sample file?

    I could not replicate the issue.
    Thanks,
    Solus


    Please remember the following:

    1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    Highlight the code in your post and press the # button in the toolbar.
    2. Show appreciation to those who have helped you by clicking below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

    "Slow is smooth, smooth is fast."

  4. #4
    Forum Contributor
    Join Date
    10-28-2003
    Location
    Happyville, USA
    MS-Off Ver
    365
    Posts
    294

    Re: VBA to format numbers with commas

    I already have the quotes around it. Maybe I am not understanding your point. Forgive me if that is the case.


    The code below is what I am using:

    HTML Code: 
    ActiveSheet.ListObjects("Data").ListColumns("Total units"). _
             DataBodyRange.NumberFormat = "#,##0"
    It should be working but doesn't seem to be.

  5. #5
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: VBA to format numbers with commas

    Can you upload a sample file?

  6. #6
    Forum Contributor
    Join Date
    10-28-2003
    Location
    Happyville, USA
    MS-Off Ver
    365
    Posts
    294

    Re: VBA to format numbers with commas

    I have attached the file in question. The formatting issue is with column B when the pivot table is created.

    Thanks
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: VBA to format numbers with commas

    I used the code below and was able to successfully reformat the data.

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    10-28-2003
    Location
    Happyville, USA
    MS-Off Ver
    365
    Posts
    294

    Re: VBA to format numbers with commas

    That works. Thanks

  9. #9
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: VBA to format numbers with commas

    No reason to select if you don't need to, just qualify it. It can cause problems.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Sort By Only The Numbers In Front Of The Commas
    By anwaee2 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-02-2014, 02:36 PM
  2. Replies: 5
    Last Post: 01-17-2014, 05:56 PM
  3. [SOLVED] 2 separate columns with numbers separated by commas and single numbers not, need sum
    By Slothbob in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-22-2013, 04:33 PM
  4. VLOOKUP...no columns just commas between numbers
    By Cicada in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-20-2009, 11:40 AM
  5. Pasting Numbers with Commas from Outside Source
    By Lou Giele in forum Excel General
    Replies: 3
    Last Post: 08-15-2006, 02:05 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1