Using active cells and jumping around in a For Next loop

This example uses the weeklysales worksheet of the SALESMAN.XLS workbook, to check to see if a salesperson is worthy of promotion. The criteria for promotion in the organisation are currently that the sales person's monthly sales should exceed 1000 units. The macro is given in Listing 7.3.

Active cells are used to implement this program. It is a slightly different approach to what we have seen so far because instead of using a For. Next loop on the cell range, we will use a loop on the range of sales staff. To do this, we start with the first active cell in the range then use the Offset method

1 Sub checkPromotion()

2 Application.ScreenUpdating = False

3 Const Promotion As Long = 1000

4 Const MaxSalesman As Integer = 9

5 Dim Count As Integer

6 Worksheets("Weeklysales").Select

7 Range("End_month_sales").Select

8 For Count = 1 To MaxSalesman

9 If ActiveCell.Value > Promotion Then

10 ActiveCell.Interior.ColorIndex = 5

11 End If

12 ActiveCell.Offset(1,0).Select

13 Next

14 End Sub to go to the next cell in the loop. When using active cells, the screen can sometimes jump around when you run the program. To circumvent this, you can freeze the screen while the macro runs by using the code in Line 2, i.e.

Application.ScreenUpdating = False

This line will ensure that the screen remains frozen until you either assign the property of the value to True, or when your macro finishes execution. You will not have to restore it to true, unless you want to display screen changes while the macro is still running.

After Line 2, some variables are declared, as well as constants set for the MaxSalesman, currently 9, and the amount of units sold which would qualify for promotion, currently 1000. Remember that the purpose of the procedure is to check if a salesperson's sales are high enough to be considered for promotion.

Line 5 defines count as an integer variable, and line 6 selects the worksheet called weeklysales.

Line 7 selects the range of cells called end_month_sales. The first cell in this range will then become active.

Line 8 uses a For... Next to go through the loop 9 times (i.e. the value of MaxSalesman).

Lines 9-11 check for the promotion condition.

Line 12 uses the Offset method applied to the active cell (i.e. the new active cell becomes the next cell in the range).

The output is shown in Figure 7.4.

E3 Microsoft Excel - SALESMAN

File Edit View Insert Format Tools Data Window Help

§ I Times New Roman

Type a question For help

File Edit View Insert Format Tools Data Window Help

§ I Times New Roman

Type a question For help

A

B

a

D

E F

u

H

1

21

i j^I oi Moi.'Ji Sales:

Each cell value in this is c

alculated by adding the Sales To Date to the Monthly Total

.22

Monthly Bonus :

Each cell value in this is c

alculated by multiplying Monthly Total by the Bonus Rate

23

Sales To Date:

Each cell in this range is earned over from the End of Month Sales column for the beginning of each new r

24

Month No:

This cell value is incremented at the start of each new month

25

26

Sales Data:

27

28

Eairas Raie:

s

t

29

Monti i No:

1

30

Weekly Sales

31

Rep Najijc

Sales To Date

1

2

3 I 4

Monthly Total

Monthly Bonns

End of Month Sales

32

Andy

135

5

6 12

77

f. 383 .DO

212

33

Bindi

439

■43

136

£ 680.00

393

34

Katie

337

150 166

372

1,860.00

729

35

Misba

540

77

ISO

£ 900.00

720

36

At

489

137

£ 783.00

646

37

Amy

333 ■

.39 78

193

£ 963.00

326

38

Pete

336

2T

9 -7

1-23

£ 613.00

439

33

Rhlarmon

433

54

402 68

337

£ :ä,"785:oo

40

Gareth

330

33-I 18

122

£. 610.00

4321

_

41

TOTALS

3432

286

i66

71i>! 446

1917

9SS5

5349i

42

ijfi

j «it

M i

► > \ Sheets / Sheet 1 \weeklysaSes / week sales test data / Summ | ^ J

Ready

Figure 7.4 Screenshot for Listing 7.3

0 0

Post a comment