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.
Check if Integer




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.

Re: Check if Integer
hi Drew,
is it ok if you post yr code for us to see? :thanx:


Re: Check if Integer
Hi Jindon
Try this modification of Barry's code
Code
Display MoreSub 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
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


Re: Check if Integer
Oops! Thanks Jindon, my bad. This will work;

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
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!