Check if Integer

DrewBe · Nov 30th 2004

I need to check if a certain cell value is an integer. I was thinking along the lines of using a mod by 1 and if the result is 0, then its an integer, else its not. Can anyone help me with the mod command or another easy way to do this. thanx in advance.

xlite · Nov 30th 2004

Re: Check if Integer

hi Drew,

i supposed you wanna check via VBA?

if yes then try:

Code

Sub checkInt()
If IsNumeric(Range("A1")) Then
    MsgBox "OK"
Else
    MsgBox "Not Integer"
End If


End Sub

HTH

DrewBe · Nov 30th 2004

Re: Check if Integer

does the function IsNumeric check if a value is an integer or if its just numeric values as opposed to text. When i put the line in my code, it still did not work correctly. It could be my error if the command checks to see if the value is an integer.

xlite · Nov 30th 2004

Re: Check if Integer

hi Drew,

is it ok if you post yr code for us to see? :thanx:

jindon · Nov 30th 2004

Re: Check if Integer

Hi

try

Code

Sub test()
If IsNumeric(ActiveCell) Then
    If ActiveCell.Value - Int(ActiveCell.Value) <> 0 Then
        MsgBox "no"
    Else
        MsgBox "yes"
    End If
End If
End Sub

hope this helps

jindon

Dave Hawley · Nov 30th 2004

Re: Check if Integer

Hi Jindon

Try this modification of Barry's code

Code

Sub checkInt()
    If IsNumeric(Range("A1")) And Not IsEmpty(Range("A1")) Then
        If Range("A1") Mod 1 = Range("A1") Then
             MsgBox "Integer: " & Range("A1")
        Else
             MsgBox "Not Integer: " & Range("A1")
        End If
    Else
            MsgBox "Not numeric or empty"
    End If
End Sub

Display More

Not sure of your reasons but, the code will tell you if the Integer is outside of Excel's Variable storage value limits: -32,768 to 32,767

jindon · Nov 30th 2004

Re: Check if Integer

Hi Dave

Although this is not my thread, I don't understand the code.

Code

If Range("A1") Mod 1 = Range("A1") Then 
            MsgBox "Integer: " & Range("A1")

Range("A1") Mod 1 is always 0, therefore it always tell us Not Integer unless A1 value is 0

best regards,

jindon

Dave Hawley · Nov 30th 2004

Re: Check if Integer

Oops! Thanks Jindon, my bad. This will work;

Code

Sub checkInt()
    If IsNumeric(Range("A1")) And Not IsEmpty(Range("A1")) Then


        If Round(Range("A1"), 0) / 1 = Range("A1") Then
            MsgBox "Integer: " & Range("A1")
        Else
            MsgBox "Not Integer: " & Range("A1")
        End If
    Else
        MsgBox "Not numeric or empty"
    End If
End Sub

Display More

DrewBe · Nov 30th 2004

Re: Check if Integer

Thanx all for your help. I realize i didnt explain too well what i was trying to do, but for those interested, heres the problem i was working on.

5 guys get stranded on an island with a monkey. They spend an entire day collecting coconuts and decide to divide them equally in the morning. One guy awakes during the middle of the night and is afraid he wont get his fair share, so he divides the coconuts into 5 equal piles, and there is one remaining coconut which he gives to the moneky, and then hides his pile and goes back to sleep. A 2nd guy wakes up and divides the remaining coconuts into five piles and take and hides his, again giving an extra to the monkey. Continues until all 5 men have divided and stashed, always giving one to the monkey. Question is how many coconuts were there originally.

It was a fun problem. We had solved it mathematically and then decided to do it thru code. I had aworksheet doing all the calculations, and when a certain cell reached a positive whole number (As opposed to decimal) then that was the number of coconuts originally in the pile. I was trying to use this as the driving force for a loop which would increase an initial guess of coconuts in each pile after the 5th guy divided (working backwards) until the whole number solution was found. The problem is solved now

Thanks again for your help
DrewBe

Check if Integer

Participate now!

Share