software

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(B2lt;gt;trunc(b2,2,quot;errorquot;,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 lt;gt; 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)

quot;Brianquot; lt;Brian @discussions.microsoft.comgt; wrote in message
...
gt; I'm look for a macro that will check the number of decimal places in a
range
gt; of values. I would like to have a pop up box alert the user when a cell
is
gt; found in that range that contains more than 3 decimal places. For example
if
gt; the numbers in a range are 50.35, 25.2, 35.235, and 35, the macro would
stop
gt; on that cell and deliver a pop up box showing me that it contains more
than 3
gt; decimal places. I can create an excel formula to check the information:
gt; =if(B2lt;gt;trunc(b2,2,quot;errorquot;,B2) however I don't know enough VBA to get this
gt; fomula into a macro to check a range.
gt; Any help would be great.
gt;
gt; Thanks
gt; 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 lt;gt; 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 Studentquot;Brianquot; wrote:

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

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