+ Reply to Thread
Results 1 to 5 of 5

Excel Macro to Convert Number Format into 'Comma Style' accross All Worksheets

  1. #1
    Registered User
    Join Date
    03-01-2017
    Location
    Manila, Philippines
    MS-Off Ver
    MS Office 2007
    Posts
    77

    Excel Macro to Convert Number Format into 'Comma Style' accross All Worksheets

    Hi excelforum Experts,

    I have this excel data exported from the system that contains 'Number Stored as Text". I tried recording a code for these but the code did not work entirely, there are still numbers which have 'green color' on the upper-left of the cell. Could someone help me this out?

    I have attached the sample workbook that I am referring to and I wish to convert all numbers from 'Range B8:H500' to be converted into 'Comma Style' for all worksheets (Sometimes I have about 31 worksheets in a workbook).

    Thank you in advance!


    Warmest regards,
    Arbel

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,207

    Re: Excel Macro to Convert Number Format into 'Comma Style' accross All Worksheets

    No need for VBA for this.

    1. select the 1st sheet
    2. highlight all the columns affected
    3. Groups all the sheets affected by holding Shift and clicking on the last sheet tab
    4. press CTRL H (Find and Replace)
    5. Find , (comma) Replace nothing (leave blank)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    03-01-2017
    Location
    Manila, Philippines
    MS-Off Ver
    MS Office 2007
    Posts
    77

    Re: Excel Macro to Convert Number Format into 'Comma Style' accross All Worksheets

    Hi FDibbins,

    I want my process to be automated because I have 30 workbooks which have about 31 worksheets. It would be much appreciated if you could help me create VBA code to automate this process.

    Thank you in advance!


    Warmest regards,
    Arnel

  4. #4
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    Re: Excel Macro to Convert Number Format into 'Comma Style' accross All Worksheets

    Hi Arnel- Try this. Open the attached file, then open any workbooks you want to "Fix". There's a TEST button you can use to check my approach. If it works, you can then press the "Process ALL..." button. The procedure keeps a log of the workbooks you process. Here's the code for you browsers:
    Please Login or Register  to view this content.
    Hope this helps. If so, please mark the thread as Solved... and maybe click the *Add Reputation Star at left. Thanks! -Lee
    Attached Files Attached Files
    Last edited by leelnich; 04-08-2017 at 08:37 AM.

  5. #5
    Registered User
    Join Date
    03-01-2017
    Location
    Manila, Philippines
    MS-Off Ver
    MS Office 2007
    Posts
    77

    Re: Excel Macro to Convert Number Format into 'Comma Style' accross All Worksheets

    Hi leelnich,

    It works like magic!

    Thanks for providing me with the best VBA codes that exceeded my expectation. This will enable me to save time processing data in excel.

    I appreciate your immediate feedback. Have a great day!


    Warmest regards,
    Arnel

+ 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. Replies: 4
    Last Post: 02-23-2016, 03:29 AM
  2. problem with converting numbers to TEXT format and applying comma style
    By Vogelmann in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-23-2012, 10:43 AM
  3. numbers with `comma style` in text format
    By Vogelmann in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-22-2012, 11:37 AM
  4. Replies: 14
    Last Post: 08-03-2012, 07:07 PM
  5. auto number accross many worksheets
    By jayfive in forum Excel General
    Replies: 2
    Last Post: 01-13-2012, 03:00 AM
  6. Calculating number of entries per person accross multiple worksheets
    By istanley24 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-11-2010, 10:46 AM
  7. Replies: 7
    Last Post: 06-18-2009, 04:41 AM

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