JLGWhiz
3/24/2010 5:46:00 PM
Yes, I did not account for the 3 digit whole number. The modified code
below would also eliminate that possibility and restrict the items tested to
only those with decimal values.
Sub decDig()
For Each c In Range("A2:A5")
If InStr(c, ".") > 0 Then
If Len(c) - InStr(c, ".") > 2 Then
MsgBox c.Address & " OK"
End If
End If
Next
End Sub
"Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message
news:%23xBbqS3yKHA.6140@TK2MSFTNGP05.phx.gbl...
> By fail I meant it will return the wrong result, not error out. I assumed
> from the OP's posting that whole numbers as well as floating point numbers
> with one or two decimal places were okay... your original If...Then
> statement reported one and two digits after the decimal point as being
> okay, but listed whole numbers as having more than two decimal places (if
> the whole number had more than two digits in it).
>
> --
> Rick (MVP - Excel)
>
>
>
> "JLGWhiz" <JLGWhiz@cfl.rr.com> wrote in message
> news:u5$pIN3yKHA.1796@TK2MSFTNGP02.phx.gbl...
>> Hi Rick, I ran a test and it did not error out. But it does not hurt to
>> be safe.
>>
>>
>> "Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message
>> news:OZHMFv2yKHA.4752@TK2MSFTNGP04.phx.gbl...
>>> Your code will fail if the number is a whole number without a decimal
>>> point. If you change your If..Then statement to the following, then your
>>> code will work correctly...
>>>
>>> If Len(c) - InStr(c & ".", ".") > 2 Then
>>>
>>> --
>>> Rick (MVP - Excel)
>>>
>>>
>>>
>>> "JLGWhiz" <JLGWhiz@cfl.rr.com> wrote in message
>>> news:#sweMa2yKHA.4752@TK2MSFTNGP04.phx.gbl...
>>>> This worked pretty good. You can adapt it to your needs.
>>>>
>>>> Sub dk()
>>>>
>>>> For Each c In Range("A2:A6")
>>>> If Len(c) - InStr(c, ".") > 2 Then
>>>> MsgBox c.Address & " More than 2 decimal places"
>>>> End If
>>>> Next
>>>> End Sub
>>>>
>>>>
>>>>
>>>> "zxcv" <zxcvnosend@yahoo.com> wrote in message
>>>> news:3466ddcc-9050-4873-9377-bcaee5b6517e@z3g2000yqz.googlegroups.com...
>>>>>I need to do some input validation so to check if a value has no more
>>>>> than 2 decimal digits. So 14.12 is valid but 14.123 is not.
>>>>>
>>>>> I have tried doing a check like this:
>>>>>
>>>>> If Int(inval * 100) <> inval * 100 Then
>>>>>
>>>>> but this gets a rounding error with certain values like 2.22. If I
>>>>> subtract one side above from the other I get a difference on the order
>>>>> of 10^-21.
>>>>>
>>>>> So I tried rounding the numbers to do a test like this:
>>>>>
>>>>> If Round(Int(inval * 100), 10) <> Round(inval * 100, 10) Then
>>>>>
>>>>> and this does something weird like rounding Round(Int(0.29 * 100), 10)
>>>>> to 28.
>>>>>
>>>>> Is there some simpler way to check that a number does not have too
>>>>> many decimal digits?
>>>>
>>>>
>>
>>