VBA Code to check values and allow only 2 decimals

[a984sce]

All,
Please can I request your help/assistance on the following.

I have a workbook with 9 different worksheets which are named. Example Summary, FT Employee, PT Employee etc.
In each of the worksheet the resulting calculated column should contain values with only 2 decimal places. For example 15.12 is correct, but 15.123 is dis-allowed and any rounding Up or Down to 2 decimals is not allowed.
The columns with resulting values would be different column no., so we need to define this column no in the VBA Code.

Please can you share the VBA Code which will perform the check and correct the values with decimals.

Thank you.

regards
venkat

 

[CalcSux78]

Welcome to the forum...

The worksheet function TRUNC should accomplish what you're looking for.

Excel 2010
	A	B
1	Number	Truncated #
2	15.123	15.12
3	15.299	15.29
4	15.749	15.74
Sheet2

Cell Formulas
Range	Formula
B2	=TRUNC(A2,2)

A VBA approach:

Sub Test()

Dim rng As Range
Dim cel As Range


Set rng = Range([A2], Cells(Rows.Count, 1).End(xlUp))
For Each cel In rng.Cells
    cel = TruncTo(cel.Value, 2)
Next cel
End Sub


Public Function TruncTo(dblValue As Double, lngPlaces As Long) As Double
TruncTo = Int(dblValue * 10 ^ lngPlaces) / 10 ^ lngPlaces
End Function