+ Reply to Thread
Results 1 to 8 of 8

How do I use VBA to change cell format to a percentage?

  1. #1
    Registered User
    Join Date
    01-02-2014
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    5

    How do I use VBA to change cell format to a percentage?

    Good Afternoon,

    I am in the middle of creating a custom function that calculates the percentage of the variance on a work order. Below is what I have so far. Everything works until you get to the last 2 lines. I have tried everything I can think of, but I can't change the cell format to percentage. Can you offer any assistance? I am still new with visual basic and I learned the code by recording macros.

    Please Login or Register  to view this content.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How do I use VBA to change cell format to a percentage?

    Hi and welcome to the forum

    How about you set the format in the cell itself, instead of in the macro?
    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
    01-02-2014
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: How do I use VBA to change cell format to a percentage?

    Hello FDibbins,

    Thank you for the welcome.

    I would prefer that it be done in the formula, so that you can use it in any worksheet without having to go back after to change the format. What do you think, doable?

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: How do I use VBA to change cell format to a percentage?

    your problem is this

    VariancePercentage = "Less than 5%"

    you cant "round" text

    also dont know how column T fits into the picture

    Please Login or Register  to view this content.
    would something like this work?
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  5. #5
    Registered User
    Join Date
    01-02-2014
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: How do I use VBA to change cell format to a percentage?

    Sorry, the Column T was just me trying to force the format change. I entered in the code exactly how it is written and the cell format still doesn't change to percent. Could there be another issue? If you run the code does it work for you? I attached a sample.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: How do I use VBA to change cell format to a percentage?

    slight change to code

    Please Login or Register  to view this content.
    also could not find the code in the file so i included it on mine

    not sure if you actually wanted the "actual" rounding to 2 decimal places in there so the example does not include it

    if you do...put this line in instead
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by humdingaling; 01-02-2014 at 11:10 PM.

  7. #7
    Registered User
    Join Date
    01-02-2014
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: How do I use VBA to change cell format to a percentage?

    That worked beautifully. Thanks for your help humdingaling.

  8. #8
    Registered User
    Join Date
    01-02-2014
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    5

    [Solved] Re: How do I use VBA to change cell format to a percentage?

    Huge Help!!!

+ 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. Percentage error from change in value of a cell
    By danjaf in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-05-2013, 10:42 AM
  2. Replies: 3
    Last Post: 01-31-2013, 09:38 PM
  3. [SOLVED] format as percentage from cell to email
    By fabrecass in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-25-2013, 12:26 PM
  4. Replies: 2
    Last Post: 02-01-2006, 12:50 PM
  5. [SOLVED] FORMAT PERCENTAGE IN INDEX CELL?
    By Ladybug726 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 11-20-2005, 01:15 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