Check for more than 2 decimal places

I'm look for a macro that will check the number of decimal places in a range
of values. I would like to have a pop up box alert the user when a cell is
found in that range that contains more than 3 decimal places. For example if
the numbers in a range are 50.35, 25.2, 35.235, and 35, the macro would stop
on that cell and deliver a pop up box showing me that it contains more than 3
decimal places. I can create an excel formula to check the information:
=if(B2<>trunc(b2,2,"error",B2) however I don't know enough VBA to get this
fomula into a macro to check a range.
Any help would be great.

Thanks
Brian

Sub CheckDecimals()
Dim cell As Range

For Each cell In Selection
If cell.Value <> Round(cell.Value, 2) Then
MsgBox cell.Address
End If
Next cell

End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Brian" <Brian @discussions.microsoft.com> wrote in message
news:A828BC9A-D895-434F-8E8A-3B037F827178@microsoft.com...
> I'm look for a macro that will check the number of decimal places in a
range
> of values. I would like to have a pop up box alert the user when a cell
is
> found in that range that contains more than 3 decimal places. For example
if
> the numbers in a range are 50.35, 25.2, 35.235, and 35, the macro would
stop
> on that cell and deliver a pop up box showing me that it contains more
than 3
> decimal places. I can create an excel formula to check the information:
> =if(B2<>trunc(b2,2,"error",B2) however I don't know enough VBA to get this
> fomula into a macro to check a range.
> Any help would be great.
>
> Thanks
> Brian

First enter this macro:

Sub Macro1()
Dim r As Range
Dim v As Variant
For Each r In Selection
v = r.Value
If v <> Round(v, 2) Then
t = r.Address
MsgBox (t)
End If
Next
End Sub

The macro will work over any range that you select.

Say in A1 thru A5 you have entered:

1
2
2.2
2.34
2.345
Select these cells and run the macro and it will stop on the 2.345

If you want to allow 2.345, but stop on 2.3456 then just change the Round to:

Round(v,3)
--
Gary's Student


"Brian" wrote:

> I'm look for a macro that will check the number of decimal places in a range
> of values. I would like to have a pop up box alert the user when a cell is
> found in that range that contains more than 3 decimal places. For example if
> the numbers in a range are 50.35, 25.2, 35.235, and 35, the macro would stop
> on that cell and deliver a pop up box showing me that it contains more than 3
> decimal places. I can create an excel formula to check the information:
> =if(B2<>trunc(b2,2,"error",B2) however I don't know enough VBA to get this
> fomula into a macro to check a range.
> Any help would be great.
>
> Thanks
> Brian

caveat: this assumes that the results were manually entered, and not the
result of calculations (where the vagaries of binary approximation could
lead a result that appears to have only 2 decimal places, yet is not
equal to its rounded value.

Jerry

Bob Phillips wrote:

> Sub CheckDecimals()
> Dim cell As Range
>
> For Each cell In Selection
> If cell.Value <> Round(cell.Value, 2) Then
> MsgBox cell.Address
> End If
> Next cell
>
> End Sub
>
>
>