+ Reply to Thread
Results 1 to 5 of 5

VBA Custom Number Formatting

  1. #1
    Registered User
    Join Date
    10-22-2014
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2011
    Posts
    44

    VBA Custom Number Formatting

    Hello,

    I have created a macro that basically cleans up a spread sheet into 2 columns. I need help formatting my second column. Here is my code

    Please Login or Register  to view this content.
    Here is what the format result

    Please Login or Register  to view this content.
    I am looking for the correct code to format Column B so that the number is * by 100 and rounded to two decimal places

    For example A = 1.10 and B= .90

    Thank You for your help

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: VBA Custom Number Formatting

    Available number format codes: https://support.office.com/en-au/art...2-09fab54be7f4 I would recommend becoming more familiar with the available number format codes.

    I am not aware of a number format code that will do exactly what you describe. The closest I can think of is to use a per cent format ("#.00%"), but that will include the per cent symbol in the display, which your examples do not indicate. If the display of the per cent symbol is not a problem, then this seems like the easiest "number formatting only" approach.

    If you really do not want the per cent symbol, then I think the easiest approach will be to actually multiply the values by 100, then format as fixed to two decimal places ("#.00"). It will be important to recognize that the actual values in these cells will be 100x the original value if you need to use them in subsequent calculations. This approach is a combination of math operation plus number formatting to get what you want
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    10-22-2014
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2011
    Posts
    44

    Re: VBA Custom Number Formatting

    Hey MrShorty,

    Thanks for your help. I can't have it as a %, so I ended up *100 prior to formatting. It works great .

  4. #4
    Valued Forum Contributor
    Join Date
    03-28-2014
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: VBA Custom Number Formatting

    Hi btone,


    Please add this to your code

    If this not satisfied, please attach your workbook my clear understanding & simplify your code
    Please Login or Register  to view this content.

    Please add reputation to me if the code helped you
    Last edited by Faridwahidi; 08-24-2015 at 11:47 AM.
    1. Thank those who have helped you by clicking the Star * below the post.
    2. Please mark your post [SOLVED] if it has been answered satisfactorily.

    Sincerely,
    Farid

  5. #5
    Valued Forum Contributor
    Join Date
    03-28-2014
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: VBA Custom Number Formatting

    sorry for duplicate.
    Last edited by Faridwahidi; 08-24-2015 at 11:48 AM.

+ 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. Custom Number Formatting
    By springboardjg in forum Excel General
    Replies: 1
    Last Post: 07-01-2012, 01:23 PM
  2. Custom Number Formatting
    By huston1679 in forum Excel General
    Replies: 13
    Last Post: 08-07-2011, 04:44 AM
  3. custom number formatting.
    By Rgaherty in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-24-2008, 11:08 AM
  4. Custom Number Formatting
    By ferrari_99 in forum Excel General
    Replies: 1
    Last Post: 08-08-2007, 09:34 PM
  5. Custom number formatting
    By Don in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-25-2006, 09:40 AM
  6. [SOLVED] Number custom formatting
    By Don in forum Excel General
    Replies: 1
    Last Post: 06-24-2006, 10:40 PM
  7. Custom Number Formatting
    By LDanix in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-16-2005, 10:06 AM
  8. Custom Number formatting
    By gizmo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-07-2005, 10:06 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