For Each Next

Use this structure when it is required to process all objects in a collection, such as loop around a range of cells in a worksheet. The loop will execute for as many times as there are elements in a specified group, e.g. each worksheet in a workbook, or each cell in a range. The For Each ... Next loop has the basic syntax:

For Each element In group

Statements Next

For Each cell In Range(range_name)

Statements Next

In this example the examination mark data shown on the worksheet in Figure 7.1 is analysed so that the number of distinctions (70 or more), credits (50 or more but less than 70) and failures (less than 50) is displayed for each gender in the positions shown. That is, the numbers in the ranges E9:G9 and E16:G16. The procedure, checkGenderProportions, has been designed by using a For ... Next loop which checks each cell in the range C6:C19 - the ones that contain examination marks. During each repetition of the For loop, the cell is checked to see which category it falls into. You can see that the first If condition checks to see if the cell value is both female and a distinction, if it is, then the integer variable female_distinction_count is incremented. The same testing process is applied to each of the other categories. After the For. Next loop has been completed, the categories are output.

ElseIf (cell.Value = "f") And (cell.0ffset(0, -1).Value >= 50) Then female_credit_count = female_credit_count + 1 ElseIf (cell.Value = "f") And (cell.0ffset(0, -1).Value < 50) Then female_fail_count = female_fail_count + 1 End If

If (cell.Value = "m") And (cell.0ffset(0, -1).Value >= 70) Then male_distinction_count = male_distinction_count + 1 ElseIf (cell.Value = "m") And (cell.0ffset(0, -1).Value >= 50) Then male_credit_count = male_credit_count + 1 ElseIf (cell.Value = "m") And (cell.0ffset(0, -1).Value < 50) Then male_fail_count = male_fail_count + 1 End If Next

Range("e9").Value = female_distinction_count Range("f9").Value = female_credit_count Range("g9").Value = female_fail_count Range("e16").Value = male_distinction_count Range("f16").Value = male_credit_count Range("g16").Value = male_fail_count End Sub

0 Microsoft Excel - EHam_mark_analysis

^iniM

0 Microsoft Excel - EHam_mark_analysis

^iniM

File Edit

□ S H 1

Vrew Insert Format Tools Data Window Help - 3 X; | (?t H Arial . 10 . B / I f I i "

A

B mrj\ m

E

F

Hf I

i

I

—'

2

Examination Mill Woiksheet

3

4

5

Mark

Gendei

6

□6

f

7

47

r

Summary of Female Categories

3

63

m

Distinctions

Credits

Fail

9

76

f

2

■'3-

'2

10

66

■f

11

34

m

12

45

m

13

73

f

14

□5

m

Summaiy of Male Gate

jories

15

47

m

Distinctions

Credits

Fail

16

71

m

1

■3

■3"

17

65

f

13

45

■f

19

53

m

2G

-rl

M

► ftstai »Kfflswi

hl

I

Ready yy

Figure 7.1 The output format for gender categorisation of marks

Figure 7.1 The output format for gender categorisation of marks

0 0

Post a comment