Check for typing errors in cells

[KyleG]

I am trying to find a way (macro) to ensure that the data in cells on a worksheet are correct.

The only data that will be entered are:
negative numbers eg. -1.40
Positive numbers eg. 1.40
Cells with the text n/a
Blank Cells.

We have had a few instances where people have accidently entered incorrect characters eg.
1..40
1.40.
n

All of these cause errors in formulas that are running on another worksheet. Is there a way that a have a macro check that only correct values are entered and gives the user a warning if they incorrectly type something?

Thanks

 

[erik.van.geit]

Hi,

My first idea would be to use data validation.
Choose "custom" and try this formula (while A1 is active): =OR(ISNUMBER(A1),A1="n/a")

You will only be able to fill in numerics or "n/a".
If you fill in the alertmessage, the user will be warned why the entry is not accepted. No code needed.

kind regards,
Erik

 

[Joe Was]

Select that cell E9 here:

Excel Toolbar: Data - Validation..
Settings [Tab]

Allow:
Custom

Formula Is:

=Or(E9="n/a",E9="N/A",E9="",IsNumber(E9))

 

[erik.van.geit]

Hi, Joe,

It works for me without repeating the string Lower & Upper Case.
Data validation is not casesensitive.

best regards,
Erik

 

[KyleG]

Thanks heaps thats exactly what i needed and no code required very cool.