Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
if more than 2 decimal places
Do you mean two places displayed in the cell, or two places in the
underlying value? Cliff Edwards |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Decimal Places | Excel Worksheet Functions | |||
Subtracting two 2-decimal place numbers gives result 13-decimal places? | Excel Worksheet Functions | |||
Decimal Places | Excel Discussion (Misc queries) | |||
Decimal Places | Excel Programming | |||
FIXED 2 DECIMAL PLACES, MUST ENTER ALL ZEROES AFTER DECIMAL POINT. | Excel Discussion (Misc queries) |