+ Reply to Thread
Results 1 to 2 of 2

Set decimals and round up cells with vba

  1. #1
    Registered User
    Join Date
    02-02-2015
    Location
    Antwerp (Belgium)
    MS-Off Ver
    Office 2010
    Posts
    1

    Set decimals and round up cells with vba

    For an internal order form I’d like to get two things done:

    - In the column “unit price” I want the numbers of decimals to be set to 4 digits (e.g. 3.4509), without any Rounding
    - In the columns “total price excl. VAT” and “total price incl. VAT” I want the numbers of decimals to be limited to 2 digits (e.g. 3.45) AND these columns need to be ROUNDED UP to those 2 digits. For instance: 3.4509 must become 3.45 but 3.4589 must become 3.46

    At this point, I’ve managed to get these things done, but from within the cells.

    But to prevent my colleagues from erasing of tampering with the formulas, I’d like to move these instructions to the VBA-section of the worksheet.
    When all the coding is in place, I'll finalize the form by blocking cells from being used.

    Thanks for any input.
    W.
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Set decimals and round up cells with vba

    I think you messed-up with ROUNDED UP
    Rounding up (2 digits) will round both 3.4509 and 3.4589 to 3.46, so you probably need standard rounding.

    so formulas rather like:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    or equivalent:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    As for code - try:
    Please Login or Register  to view this content.
    Last edited by Kaper; 02-13-2015 at 07:56 AM. Reason: added formulas for proper rounding
    Best Regards,

    Kaper

+ 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. [SOLVED] MAX(Round to no decimals
    By dmad81 in forum Excel General
    Replies: 10
    Last Post: 06-27-2012, 05:53 PM
  2. Round Decimals
    By Ocean Zhang in forum Excel General
    Replies: 2
    Last Post: 01-16-2011, 09:32 PM
  3. Round Down to two Decimals
    By santanuKD in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-16-2009, 07:21 AM
  4. Excel 2007 : Round Up Decimals
    By librarycustodian in forum Excel General
    Replies: 2
    Last Post: 05-30-2009, 03:01 AM
  5. [SOLVED] How do I ROUND() round off decimals of a column dataset?
    By Højrup in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-12-2005, 07:06 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