I have a range that should only contain numbers. Blanks are OK, but I need to determine if anyone “deleted” a cell by typing a space or entered text in any other way. This worksheet formula returns TRUE if there is text in the range:
COUNTA counts all the non-blank cells while
COUNT counts only numeric cells. If I were trying to prevent improper data entry, I would probably use Data Validation to ensure that only numbers are entered. Data Validation has an option to only allow decimals (or whole numbers). But you can’t just leave it at that. You have to define a range of decimals to allow. To allow any number, you can set the criterion to less than or equal to fifteen 9s. Excel can hold numbers larger than that, but it’s unlikely you program allows them.
If you need to allow really large numbers, you can use custom validation with a formula to check for text.
I’m not try to prevent data entry, though. I don’t have control over the worksheet, I just need to check for the error condition in code. So I used:
Err.Raise 9999, , “Range contains text”