Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for Decimal
In a section of code I am using the MOD function to test for cases where a
given number is a multiple of 51. If Range("A1") MOD 51 = 0 then ....more code In cell A1 I have 203.52. The function is returning true because the 203.52 is rounded up to 204 as part of the MOD test. I don't want to apply code to A1l and need to test for the decimal before I use the MOD function, I think. If Range("A1") < "decimal" then If Range("A1") MOD 51 = 0 then ...more code Is this the logic I need and how do I test for the decimal? Thanks. -- Ken Hudson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for Decimal
You can test your value like this...
If InStr(Range("A1") .Value, ".") = 0 Then If Range("A1") MOD 51 = 0 then ...more code Rick "Ken Hudson" wrote in message ... In a section of code I am using the MOD function to test for cases where a given number is a multiple of 51. If Range("A1") MOD 51 = 0 then ....more code In cell A1 I have 203.52. The function is returning true because the 203.52 is rounded up to 204 as part of the MOD test. I don't want to apply code to A1l and need to test for the decimal before I use the MOD function, I think. If Range("A1") < "decimal" then If Range("A1") MOD 51 = 0 then ...more code Is this the logic I need and how do I test for the decimal? Thanks. -- Ken Hudson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for Decimal
I would take the integer value subtract from the original.
With Range("A1") If .Value - Int(.Value) = 0 And .Value Mod 51 = 0 Then 'more code End If End With -- Regards, Nigel "Ken Hudson" wrote in message ... In a section of code I am using the MOD function to test for cases where a given number is a multiple of 51. If Range("A1") MOD 51 = 0 then ....more code In cell A1 I have 203.52. The function is returning true because the 203.52 is rounded up to 204 as part of the MOD test. I don't want to apply code to A1l and need to test for the decimal before I use the MOD function, I think. If Range("A1") < "decimal" then If Range("A1") MOD 51 = 0 then ...more code Is this the logic I need and how do I test for the decimal? Thanks. -- Ken Hudson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for Decimal
Here is two methods. either search for a decimal point in the number or use
Mod and test if the remainder equals 0. If InStr(Range("J21").Text, ".") = 0 Then End If If (Range("J21") Mod 10) = 0 Then End If "Ken Hudson" wrote: In a section of code I am using the MOD function to test for cases where a given number is a multiple of 51. If Range("A1") MOD 51 = 0 then ....more code In cell A1 I have 203.52. The function is returning true because the 203.52 is rounded up to 204 as part of the MOD test. I don't want to apply code to A1l and need to test for the decimal before I use the MOD function, I think. If Range("A1") < "decimal" then If Range("A1") MOD 51 = 0 then ...more code Is this the logic I need and how do I test for the decimal? Thanks. -- Ken Hudson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for Decimal
Thanks, Nigel.
I believe the correct expression for your side of the pond is "that works a treat." -- Ken Hudson "Nigel" wrote: I would take the integer value subtract from the original. With Range("A1") If .Value - Int(.Value) = 0 And .Value Mod 51 = 0 Then 'more code End If End With -- Regards, Nigel "Ken Hudson" wrote in message ... In a section of code I am using the MOD function to test for cases where a given number is a multiple of 51. If Range("A1") MOD 51 = 0 then ....more code In cell A1 I have 203.52. The function is returning true because the 203.52 is rounded up to 204 as part of the MOD test. I don't want to apply code to A1l and need to test for the decimal before I use the MOD function, I think. If Range("A1") < "decimal" then If Range("A1") MOD 51 = 0 then ...more code Is this the logic I need and how do I test for the decimal? Thanks. -- Ken Hudson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for Decimal
While it is probably not the case with the OP, just thought I would point
out that your Int function call will fail if the contents of A1 is a non-number. Also, you could save a couple of characters by testing the ..Value=Int(.Value) directly instead of subtracting them and seeing if that difference was zero. Rick "Nigel" wrote in message ... I would take the integer value subtract from the original. With Range("A1") If .Value - Int(.Value) = 0 And .Value Mod 51 = 0 Then 'more code End If End With -- Regards, Nigel "Ken Hudson" wrote in message ... In a section of code I am using the MOD function to test for cases where a given number is a multiple of 51. If Range("A1") MOD 51 = 0 then ....more code In cell A1 I have 203.52. The function is returning true because the 203.52 is rounded up to 204 as part of the MOD test. I don't want to apply code to A1l and need to test for the decimal before I use the MOD function, I think. If Range("A1") < "decimal" then If Range("A1") MOD 51 = 0 then ...more code Is this the logic I need and how do I test for the decimal? Thanks. -- Ken Hudson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Logical test IF a decimal was typed in a cell | Excel Worksheet Functions | |||
Subtracting two 2-decimal place numbers gives result 13-decimal places? | Excel Worksheet Functions | |||
Batch converting CSV files from comma-decimal to period-decimal | Excel Discussion (Misc queries) | |||
Converting 2-place decimal value to floating point decimal number with leading zero | Excel Discussion (Misc queries) | |||
FIXED 2 DECIMAL PLACES, MUST ENTER ALL ZEROES AFTER DECIMAL POINT. | Excel Discussion (Misc queries) |