+ Reply to Thread
Results 1 to 4 of 4

Round Percentages in all cells

  1. #1
    Registered User
    Join Date
    05-17-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2010
    Posts
    16

    Round Percentages in all cells

    I am trying to round any percentage in the table up or down to the nearest whole number. For example, 2.56% to 3% and 2.42% to 2%.
    I found the following macro (originally meant to change percentages to decimals). It takes the number from 2.56% to 2.56. I changed the "2" to "0" and get 3.00. But when I try to change the number format to get 3% I have trouble. Is there a way to fix it or is there another macro I should try?

    Public Sub PercentsToDecimal()
    Dim rCell As Range
    For Each rCell In ActiveSheet.UsedRange.Cells
    With rCell
    If .NumberFormat Like "*%*" Then
    .Value = Application.Round(.Value * 100, 2)
    .NumberFormat = "0.00"
    End If
    End With
    Next rCell
    End Sub

    Thanks for any help.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: Round Percentages in all cells

    Not sure why you're multiplying by 100, or changing the format.
    This seems to work

    Please Login or Register  to view this content.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,883

    Re: Round Percentages in all cells

    There are two things here. What should actually be stored in cell?
    0.03 = 3%

    Or should it store text value of 3%?

    Typically I don't recommend changing underlying value using VBA (since what operations are performed on value is not readily traceable by another user).

    If you just want to display 3% instead of 2.56%. Why not just change Percentage format's decimal display? I.E. 0% instead of 0.00% format.

    If you want value updated to 0.03 instead of 0.0256 then remove the *100 from code and set Number format to "0%"
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  4. #4
    Registered User
    Join Date
    05-17-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Round Percentages in all cells

    Yes, I just wanted to display the text value 3%. Thanks, this worked. The key was leaving the 2. I had taken out the *100, replaced the 2 with 0, and tried changing the "0.00" with "0%" but it didn't work. Guess it's some sort of double removal? Not sure, but thanks, this works now!

+ 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. Help with macro to round percentages
    By mridul127 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-09-2016, 03:51 PM
  2. round percentages
    By ragin_cajuns in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-20-2013, 03:31 PM
  3. calculating percentages of filled cells with 1 over total cells for each ptID
    By myjebay1 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-29-2012, 06:11 PM
  4. Counting the non-null cells in a range with noncontinguous cells in a round-robin
    By StudentTeacher in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-08-2011, 07:28 AM
  5. Round Percentages (Up and Down) by Multiples of 5%
    By Brandy in forum Excel General
    Replies: 4
    Last Post: 09-29-2009, 11:22 AM
  6. multiply a number by percentages and round so that they add upto the total %
    By dittotharappel in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-19-2009, 01:26 PM
  7. Percentages between two cells
    By chedd via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-08-2006, 09:20 AM

Tags for this Thread

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