Hi, I want to validate data to m 1

[wuwang]

Hi,

I want to validate data to make sure it is NOT either decimal or negative number.
I used "If Not IsNumeric(vData2) Then ....."
but it didn't work well.

Is there any other function I can use in EXCEL 97?


Thank you in advance

 

[SkipVought]

Hi,
Do you mean that you only want positive integers?

If so...

=IF(a=ABS(INT(a)),"YES","NO&quot

Hope this helps. Skip,
metzgsk@voughtaircraft.com

 

[ETID]

Another way....

Highlite the column(s) in question,
then
from the menu...Data>Validation>settings

choose allow whole number, then Greater than 0



.........built in tools, a wonderful thing!

 

[JVFriederick]

ETID - that suggestion is perfect for <0
but how did you get validation to allow whole number??
I'm confused about that part.

 

[ETID]

The first drop down in the &quot;settings&quot; tab, Validation criteria...

under the heading &quot;Allow:&quot;....then select &quot;whole number&quot;

then it will ask you for a min and/or a max...set the min to 0
------------
FYI, I'm using Excel 2000

 

[JVFriederick]

Oh, little slow here.

For some reason, I was thinking wuwang wanted to evaluate AFTER data entry, but not so.

Your idea will certainly work well!

 

[wuwang]

Thank you a lot for your great idea.
I used the code
If InStr(1, vData2, &quot;.&quot <> 0 Or Abs(vData2) > vData2 Then
.................

and it works well.

Thank you again.

 

[SkipVought]

Well....
wuwang opted for a VBA solution, and since this is the Microsoft: Office Forum, we were all looking for a built-in SPREADSHEET solution.

O L X-) Skip,
metzgsk@voughtaircraft.com