Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel VBA - Checking cell value against specific criteria
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel VBA - Checking cell value against specific criteria
Resolved · Urgent Priority · Version 2003
Reply Fri 3 Oct 2008, 09:29Delegate Michael said...
Michael has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Access VBA course
Excel VBA - Checking cell value against specific criteria
I need to check that cell values fall within a specific range of criteria, including the number of characters. My existing code, provided below, checks the cell value but does restrict the check to three characters. Subsequently entries like a single 'Z' are accepted when it shouldn't be. a single 'A' is correctly rejected. This code will undertake four seperate types of validation, which are currently being performed seperately, if I can get it to work and greatly speed up my macro. Many thanks for any guidance.
For R = 1 to 10
If Cells(R,1).Value>="000" And Cells(R,1).Value<="ZZZ" Then
End If
If Cells(R,1).Value<"000" Or Cells(R,1).Value>"ZZZ" Then
Cells(R,1).Interior.ColorIndex=3
End If
Next R
For upcoming training course dates see: Pricing & availability
RE: Excel VBA - Checking cell value against specific criteria
Hi Michael
Thank you for your question
In order to help you, I need to know the precise nature of the validation criteria. What combinations of characters and numbers are permissable, and which are not.
One immediate observation is that your first if statment appears to do nothing. There is no code between the IF and the ENDIF
Regards
Stephen
Reply Tue 7 Oct 2008, 08:06Delegate Michael said...
RE: Excel VBA - Checking cell value against specific criteria
The first line just highlights what is allowed and I suppose is unnecessary. The idea is to ensure that a) the cell contents is no more or less than three characters b) the cell contents falls in a range from '000' (zeroes) to 'ZZZ' (upper case Z). I previously had two loops, one to check the cell length and another to ensure any text was forced to uppercase. (The contents can be numerial or text, or a mix of). This would have probably achieved the same output but I was informed that the same could be achieved with one loop referring to a 'range' (for lack of a better word) of criteria. I almost got the code to work but it ignored if any of the checked cells was more than three characters. It appeared to check the first three, accept it and move on.
Unfortunately I am checking significantly more than 10 rows otherwise I would not try to seek more efficient code.
Does this explain sufficiently ?
Thanks.
|