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?

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" wrote in message
...
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?

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" wrote in message
...
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" wrote in message
...
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?

"zxcv" wrote:
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.

Try:

If Round(inval,2) = inval Then

Normally, I would opt for WorksheetFunction.Round or even
Evaluate("round(...)") instead of the VB Round function. There are
functional differences. In this case, I do not think it makes a difference.
Nevertheless, you might want to use one of those alternatives instead, just
to be sure.


----- original message -----

"zxcv" wrote in message
...
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?

Hi Rick, I ran a test and it did not error out. But it does not hurt to be
safe.


"Rick Rothstein" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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?

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" wrote in message
...
Hi Rick, I ran a test and it did not error out. But it does not hurt to
be safe.


"Rick Rothstein" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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?

On Mar 24, 12:04*pm, "Rick Rothstein"
wrote:
Your code will fail if the number is a whole number without a decimal point.

On Mar 24, 12:31*pm, "Joe User" <joeu2004 wrote:
"zxcv" wrote:
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.

Try:

If Round(inval,2) = inval Then

Normally, I would opt for WorksheetFunction.Round or even
Evaluate("round(...)") instead of the VB Round function. *There are
functional differences. *In this case, I do not think it makes a difference.
Nevertheless, you might want to use one of those alternatives instead, just
to be sure.

----- original message -----

"zxcv" wrote in message

...

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?

Thanks for the input but I need to do this in VBA as I have no control
over the input and cannot put any formulas in the sheet. Someone else
enters the data and then another person hits a button that I created.

Embellishment....

"zxcv" wrote:
I tried rounding the numbers to do a test like this:
If Int(inval * 100) < inval * 100 Then

but this gets a rounding error with certain values like 2.22.

The reason that does not work is because most numbers with decimal fractions
cannot be represented exactly. Instead, they are represented by a sum of 53
consecutive powers of two (bits), for example 2*2^1 + 0*2^0 + 0*2^-1 +
0*2^-2 + 1*2^-3 + etc.

Consequently, 2.22 is represented by exactly
2.22000000000000,019539925233402755111455917358398 4375. Int(2.22*100) is
exactly 222. But 2.22*100 is
222.000000000000,028421709430404007434844970703125 , preserving the
additional bits used to approximate 0.22 in this context.

In contrast, Round(inval,2) results in inval exactly as it would be
represented internally if it were entered with 2 decimal places. So if
inval is 2.22, Round(inval,2) results in
2.22000000000000,019539925233402755111455917358398 4375. But if inval were
2.22+2^-51 (the smallest value larger than 2.22), it would be represented
internally as 2.22000000000000,063948846218409016728401184082031 25, and
Round(inval,2) does not equal inval.

Note: You cannot enter the
2.220000000000000639488462184090167284011840820312 5 as a constant in Excel;
however, it can be the result of a calculation. Also, you can enter that
constant in VBA, including as input to an InputBox. Caveat: If you write
that constant in a VBA statement, the VBA editor might change it later when
you edit the line. It would be more reliable to write
Cdbl("2.220000000000000639488462184090167284011840 8203125").


----- original message -----

"Joe User" <joeu2004 wrote in message
...
"zxcv" wrote:
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.

Try:

If Round(inval,2) = inval Then

Normally, I would opt for WorksheetFunction.Round or even
Evaluate("round(...)") instead of the VB Round function. There are
functional differences. In this case, I do not think it makes a
difference. Nevertheless, you might want to use one of those alternatives
instead, just to be sure.


----- original message -----

"zxcv" wrote in message
...
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?

"zxcv" wrote:
On Mar 24, 12:31 pm, "Joe User" <joeu2004 wrote:
Try:
If Round(inval,2) = inval Then
[....]
Thanks for the input but I need to do this in VBA
as I have no control over the input and cannot put
any formulas in the sheet. Someone else enters the
data and then another person hits a button that I
created.

I don't understand your comment. What I wrote is for VBA, and it is
intended to deal with exactly the situation that you describe. I think you
misunderstand my comments. Perhaps you should just give it a try.

PS: Sorry, I wrote "=" where you wanted "<". That's a simple change, heh?


----- original message -----

"zxcv" wrote in message
...
On Mar 24, 12:31 pm, "Joe User" <joeu2004 wrote:
"zxcv" wrote:
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.

Try:

If Round(inval,2) = inval Then

Normally, I would opt for WorksheetFunction.Round or even
Evaluate("round(...)") instead of the VB Round function. There are
functional differences. In this case, I do not think it makes a
difference.
Nevertheless, you might want to use one of those alternatives instead,
just
to be sure.

----- original message -----

"zxcv" wrote in message

...

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?

Thanks for the input but I need to do this in VBA as I have no control
over the input and cannot put any formulas in the sheet. Someone else
enters the data and then another person hits a button that I created.

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" wrote in message
...
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" wrote in message
...
Hi Rick, I ran a test and it did not error out. But it does not hurt to
be safe.


"Rick Rothstein" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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?

On Mar 24, 1:35*pm, "Joe User" <joeu2004 wrote:
"zxcv" wrote:

On Mar 24, 12:31 pm, "Joe User" <joeu2004 wrote:

Try:
If Round(inval,2) = inval Then
[....]
Thanks for the input but I need to do this in VBA
as I have no control over the input and cannot put
any formulas in the sheet. *Someone else enters the
data and then another person hits a button that I
created.

I don't understand your comment. *What I wrote is for VBA, and it is
intended to deal with exactly the situation that you describe. *I think you
misunderstand my comments. *Perhaps you should just give it a try.

PS: *Sorry, I wrote "=" where you wanted "<". *That's a simple change, heh?

----- original message -----

"zxcv" wrote in message

...
On Mar 24, 12:31 pm, "Joe User" <joeu2004 wrote:



"zxcv" wrote:
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.

Try:

If Round(inval,2) = inval Then

Normally, I would opt for WorksheetFunction.Round or even
Evaluate("round(...)") instead of the VB Round function. There are
functional differences. In this case, I do not think it makes a
difference.
Nevertheless, you might want to use one of those alternatives instead,
just
to be sure.

----- original message -----

"zxcv" wrote in message

...

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?

Thanks for the input but I need to do this in VBA as I have no control
over the input and cannot put any formulas in the sheet. *Someone else
enters the data and then another person hits a button that I created.

I see! I did not know that the WorksheetFunction object existed. I
will make use of this more in the future.

Thank you.

On Mar 24, 1:30*pm, "Joe User" <joeu2004 wrote:
Embellishment....

"zxcv" wrote:
I tried rounding the numbers to do a test like this:
If Int(inval * 100) < inval * 100 Then

but this gets a rounding error with certain values like 2.22.

The reason that does not work is because most numbers with decimal fractions
cannot be represented exactly. *Instead, they are represented by a sum of 53
consecutive powers of two (bits), for example 2*2^1 + 0*2^0 + 0*2^-1 +
0*2^-2 + 1*2^-3 + etc.

Consequently, 2.22 is represented by exactly
2.22000000000000,019539925233402755111455917358398 4375. *Int(2.22*100) is
exactly 222. *But 2.22*100 is
222.000000000000,028421709430404007434844970703125 , preserving the
additional bits used to approximate 0.22 in this context.

In contrast, Round(inval,2) results in inval exactly as it would be
represented internally if it were entered with 2 decimal places. *So if
inval is 2.22, Round(inval,2) results in
2.22000000000000,019539925233402755111455917358398 4375. *But if inval were
2.22+2^-51 (the smallest value larger than 2.22), it would be represented
internally as 2.22000000000000,063948846218409016728401184082031 25, and
Round(inval,2) does not equal inval.

Note: *You cannot enter the
2.220000000000000639488462184090167284011840820312 5 as a constant in Excel;
however, it can be the result of a calculation. *Also, you can enter that
constant in VBA, including as input to an InputBox. *Caveat: *If you write
that constant in a VBA statement, the VBA editor might change it later when
you edit the line. *It would be more reliable to write
Cdbl("2.220000000000000639488462184090167284011840 8203125").

----- original message -----

"Joe User" <joeu2004 wrote in message

...

"zxcv" wrote:
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.

Try:

If Round(inval,2) = inval Then

Normally, I would opt for WorksheetFunction.Round or even
Evaluate("round(...)") instead of the VB Round function. *There are
functional differences. *In this case, I do not think it makes a
difference. Nevertheless, you might want to use one of those alternatives
instead, just to be sure.

----- original message -----

"zxcv" wrote in message
...
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?

Thanks. That is a great bit of information. I never knew why the
rounding errors happened.