if more than 2 decimal places
 

How can I determine programmatically within VBA whether or not the contents
of a cell have more than 2 decimal places? Anyone able to help?

if more than 2 decimal places
 

Do you mean two places displayed in the cell, or two places in the
underlying value?

Cliff Edwards

if more than 2 decimal places
 

One way:

If Int(Cells(1, 1) * 100) / 100 = Cells(1, 1) Then
MsgBox "Less"
Else
MsgBox "More"
End If

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"gbink" wrote in message
...
How can I determine programmatically within VBA whether or not the
contents
of a cell have more than 2 decimal places? Anyone able to help?

if more than 2 decimal places
 

Thanks for responding Cliff

The cell is set to display as 2 dp but the underlying value may have more
decimal places. I want to check if the underlying value has more than 2 dp.

I have trying to code something like

if cell.value < int(cell.value *100) / 100

and this does work on most occasions but sometimes I get a unexpected result.

g

"ward376" wrote:

Do you mean two places displayed in the cell, or two places in the
underlying value?

Cliff Edwards

if more than 2 decimal places
 

Thanks Sandy.

Your code is virtually identical to mine however I am finding the sometimes
the INT function is not returning expected results. e.g the cell contains
4.77 yet when I run the code I get a 'Less than 2dp' result.

when i put a watch in the code and examine INT(cell.value * 100) / 100 I get
4.76

G

"Sandy Mann" wrote:

One way:

If Int(Cells(1, 1) * 100) / 100 = Cells(1, 1) Then
MsgBox "Less"
Else
MsgBox "More"
End If

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"gbink" wrote in message
...
How can I determine programmatically within VBA whether or not the
contents
of a cell have more than 2 decimal places? Anyone able to help?

if more than 2 decimal places
 

Rounding errors due to decimal-binary-decimal conversions may create
differences like 0.000000000012, so instead of equal, check the size of the
difference and allow a little slack.

Also, check what INT does to negative values.

HTH. Best wishes Harald

"gbink" wrote in message
...
Thanks Sandy.

Your code is virtually identical to mine however I am finding the
sometimes
the INT function is not returning expected results. e.g the cell contains
4.77 yet when I run the code I get a 'Less than 2dp' result.

when i put a watch in the code and examine INT(cell.value * 100) / 100 I
get
4.76

G

"Sandy Mann" wrote:

One way:

If Int(Cells(1, 1) * 100) / 100 = Cells(1, 1) Then
MsgBox "Less"
Else
MsgBox "More"
End If

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"gbink" wrote in message
...
How can I determine programmatically within VBA whether or not the
contents
of a cell have more than 2 decimal places? Anyone able to help?

if more than 2 decimal places
 

Give this a try...

If Len(CStr(Cells(1, 1).Value)) - 2 _
InStr(CStr(Cells(1, 1).Value), ".") Then
MsgBox "More"
Else
MsgBox "Equal or Less"
End If

Rick


"gbink" wrote in message
...
How can I determine programmatically within VBA whether or not the
contents
of a cell have more than 2 decimal places? Anyone able to help?

if more than 2 decimal places
 

Thanks Rick that did teh trick

"Rick Rothstein (MVP - VB)" wrote:

Give this a try...

If Len(CStr(Cells(1, 1).Value)) - 2 _
InStr(CStr(Cells(1, 1).Value), ".") Then
MsgBox "More"
Else
MsgBox "Equal or Less"
End If

Rick


"gbink" wrote in message
...
How can I determine programmatically within VBA whether or not the
contents
of a cell have more than 2 decimal places? Anyone able to help?