+ Reply to Thread
Results 1 to 4 of 4

Round Percentages in all cells

Hybrid View

  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,080

    Re: Round Percentages in all cells

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

    Public Sub PercentsToDecimal()
    Dim rCell As Range
    For Each rCell In ActiveSheet.UsedRange.Cells
    With rCell
    If .NumberFormat Like "*%*" Then
    .Value = Application.Round(.Value, 2)
    End If
    End With
    Next rCell
    End Sub
    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
    MS365 Apps for enterprise
    Posts
    5,973

    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. [SOLVED] 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