# 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

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

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

Both options don't work for me, I can't figure out why.

The grey areas are filled in by the user. The result is then calculated with a simple formula. The result should have the same decimal numbers as the number in column A (or C).

## Files

• 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

This works for me in D2, copied down:

=ROUND(IF(OR(G2="",\$J\$2=""),"",G2*H2/\$J\$2*1000),LEN(A2)-LEN(LEFT(A2,FIND(",",A2&","))))

• 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

• Re: Variable decimal places using VBA

Wow thank you so much! that does the trick!

• Re: Variable decimal places using VBA

Thanks! that will do the trick!

## Participate now!

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