Module 3 - Conditions and Loops
Lesson 3.2 - Logical Tests
Topic 3.2.2 - Combining Conditions - And

In the previous part of this lesson you saw how to use the Or operator to evaluate two conditions and return True when either condition was met.  In this part of the lesson you'll see how to use the And operator to return True only when both conditions are met.

Files Needed

You can click here to download the file for this page.

Completed Code

You can click here to download a file containing the sample code.

The Example Workbook

Extract and open the workbook linked to in the Files Needed section above.  You'll find a modified version of an example you've seen previously:

Example

You can enter the details of a film and then click the button to copy the values you have entered.

 

After clicking the button, we'd like to copy the film's details into one of three different worksheets according the score awarded:

Score Worksheet
0 - 4 Rubbish
5 - 8 OK
9 - 10 Great

Each of these worksheets contains the column headings under which the data should be pasted:

other table

The Great, OK and Rubbish worksheets contain the same column headings in the same cells.

 

You can find the subroutine which is triggered by clicking the button in the VBE:

Copy

So far, all the subroutine does is copy the details from the relevant cells.

 

Using the And Operator

To demonstrate how the And operator works, we'll write an If statement to move rubbish films to the Rubbish worksheet.  In order for a film to be classed as rubbish, its score must be both greater than or equal to 0 and less than or equal to 4.  Start by writing an If statement to test the first of these conditions:

First condition

Write the first condition as shown here but don't add Then to the end of the line yet.

 

You can add the second condition after adding the And operator:

Second condition

Write the second condition and add Then to the end of the line.

If both conditions have been met, the entire logical test will return True.  We'll use this to select the Rubbish worksheet:

Select sheet

Add a line to select the Rubbish worksheet and then end the If statement.

You can now test that the code works by entering a film with a score between 0 and 4:

Click the button

Enter some film details and click the button.

 

After clicking the button you'll be taken to the Rubbish worksheet:

Rubbish

This is where you'll end up.

 

At this point you could add the code which pastes the copied data to the bottom of the list.  Do this below the If - End If block:

Paste

Add this line below the End If.

Try clicking the button again and make sure that the details appear in the correct place on the Rubbish worksheet:

Copied

The new details will appear at the bottom of the table.

 

Using the And Operator with ElseIf Conditions

You can, of course use the And operator with ElseIf conditions.  Let's add a set of conditions to deal with the OK films:

ElseIf

Add an ElseIf condition within the existing If - End If block.

Use the And operator to add a second condition to the ElseIf:

Second condtion

Add a second condition using the And operator.

Add an instruction to select the OK worksheet if both conditions are met:

Select sheet

Select the worksheet as shown here.

You can test that this part of the code works by entering the details for an average film:

Enter details

Enter some film details and make sure the score is between 5 and 8.

 

Click the button and make sure that the film's details appear on the OK worksheet:

New details

The details will appear at the end of the list.

 

To practise using the And operator:

  1. Using the same subroutine, add a new ElseIf condition to check that the film's score is between 9 and 10:

If Range("B4").Value >= 0 And Range("B4").Value <= 4 Then

 

Worksheets("Rubbish").Select

 

ElseIf Range("B4").Value >= 5 And Range("B4").Value <= 8 Then

 

Worksheets("OK").Select

 

ElseIf Range("B4").Value >= 9 And Range("B4").Value <= 10 Then

 

End If

  1. Add an instruction which selects the Great worksheet if both conditions have been met:

If Range("B4").Value >= 0 And Range("B4").Value <= 4 Then

 

Worksheets("Rubbish").Select

 

ElseIf Range("B4").Value >= 5 And Range("B4").Value <= 8 Then

 

Worksheets("OK").Select

 

ElseIf Range("B4").Value >= 9 And Range("B4").Value <= 10 Then

 

Worksheets("Great").Select

 

End If

  1. Add the details of a great film to Sheet1:
Add details

Add details for any film.

 
  1. Click the button and check that the details appear in the correct place:
Results

The results should appear in the Great worksheet.

 
  1. Add an Else clause to the code to make sure that films with an invalid score aren't added to the list:

If Range("B4").Value >= 0 And Range("B4").Value <= 4 Then

 

Worksheets("Rubbish").Select

 

ElseIf Range("B4").Value >= 5 And Range("B4").Value <= 8 Then

 

Worksheets("OK").Select

 

ElseIf Range("B4").Value >= 9 And Range("B4").Value <= 10 Then

 

Worksheets("Great").Select

 

Else

 

Range("A5").Value = "Invalid Score!"

Exit Sub

 

End If

  1. Test that your code works by entering an invalid score for any film:
Invalid

Perhaps we should have an extra condition which allows a negative score for any Twilight film.

 
  1. Save and close the workbook.
This page has 0 threads Add post