if more than 2 decimal places

[gbink]

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?

 

[ward376]

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

Cliff Edwards

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[gbink]

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

 

[gbink]

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

 

[Harald Staff]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

Thanks Rick that did teh trick

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