Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

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:

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:

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:

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:

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:

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:

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:

Enter some film details and click the button.

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

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:

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:

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:

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

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

Add a second condition using the And operator.

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

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 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:

The details will appear at the end of the list.

To practise using the And operator:

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

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

Add the details of a great film to Sheet1:

Add details for any film.

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

The results should appear in the Great worksheet.

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

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

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

Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

The Example Workbook

Using the And Operator

Using the And Operator with ElseIf Conditions

Head office

London

Manchester