Variable decimal places using VBA

  • I've got a worksheet with specification for some of my products. And when the products are tested the results should be rounded to the nearest number with the same amount of decimal places as given in the specifications.


    Example:
    Specifications: 2.03 - 2.48 --> Result is 2.2456 this should be rounded to 2.25
    Specifications: 13500 - 16500 --> Result is 15680.1234 this result should be rounded to 15680


    The VBA code should look for the amount of decimal places in the specifications and use that value to round the result value. Is there a way to do this?


    Any suggestions?

  • Re: Variable decimal places using VBA


    Please try the below, let me know if it helps !


    Code
    Sub M()For i = 1 To 100 ' Change as per requirement
    Range("B" & i) = Format(Range("A" & i), "." & String(Len(Range("A" & i)) - InStr(Range("A" & i), "."), "0")) ' Column B is Output and Column A is input change as pere requirement
    Next
    End Sub
  • Re: Variable decimal places using VBA


    Sorry please see below !


    Code
    Sub M()
    For i = 1 To 100 ' Change as per requirement
    If InStr(Range("A" & i), ".") = 0 Then ' CHeck in Column A for Decimal Value
    intdec = 0
    Else
    intdec = Len(Range("A" & i)) - InStr(Range("A" & i), ".")
    End If
    Range("G" & i) = Round(Range("C" & i), intdec) ' Updates Column G with the Value in Column C..Change as per requirement
    Next
    End Sub
  • Re: Variable decimal places using VBA


    Thanks for the Reply!
    For some reason the code messes up decimal separator and the 1000 separator.
    The numbers in Column B are calculated with formulas. The results of these formulas should have the same amount of decimals as the numbers given in Column A.

  • Re: Variable decimal places using VBA


    Hard to give a working example without sample data, but essentially you could use something like:
    (assuming specification is in A1 and variable "result" contains the full result value)


    Code
    x = Application.DecimalSeparator '// separator changes in different cultures.
    
    
    Range("B1").Value = Round(result, Len(Split(Range("A1").Value & x, x)(1)))


    alternatively, you could wrap your existing formula with something like:


    =ROUND([existing formula here],LEN(A1)-LEN(LEFT(A1,FIND(".",A1))))

  • Re: Variable decimal places using VBA


    =ROUND([existing formula here],LEN(A1)-LEN(LEFT(A1,FIND(".",A1))))


    This could work, only the formula skips zero's at the end. (e.g., 2.10 rounds to 1 decimal)

  • Re: Variable decimal places using VBA


    Thanks! However that formula doesn't do anything to my numbers :wow:
    However, if I format the numbers as "Number", 2.212 ends up in 2.200 if the specification is 2.2.


    I guess one of my settings in excel is not right.

  • Re: Variable decimal places using VBA


    Quote

    the formula skips zero's at the end


    It will do, because Excel treats it as a number, and true numbers don't have leading zeros. If you want to display in this format then you will need to treat the value as text and use something like this:


    =TEXT(IF(OR(G2="",$J$2=""),"",G2*H2/$J$2*1000),"0"&IF(ISERROR(FIND(",",A2)),"","."&REPT("0",LEN(A2)-LEN(LEFT(A2,FIND(",",A2&","))))))


    Note that the cell format is just "General" in my tests

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!