Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

excel vba checking

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel VBA - Checking cell value against specific criteria

Excel VBA - Checking cell value against specific criteria

ResolvedVersion 2003

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

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

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.

 

Training courses

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel tip:

Use Paint Brush to Format More than one Cell

To copy a format to many cells or ranges

1. Select the cell with the format that you want to copy

2. Double-click on the paint brush

When you move to the cell where you want to paste the format you will notice that a paint brush follows the cursor.

Paste the format to all the cells or ranges you need to format.

When finished go back to the paint brush on the tool bar and single click on it to deactivate the function.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.11 secs.