Some examples using the Range object

The following macro is used to create a named range variable called RandomRange in Excel. A For. Next loop is then used to generate a set of random numbers in each cell in RandomRange. After completion of the For.Next loop, the cell values are then sorted using the Sort method. A screenshot of the output is shown in Figure 4.6.

Sub cellProperty ()

Dim RandomRange As Range Set RandomRange = Range("b1:b10") For Each cell In RandomRange cell.Value = Int(Rnd() * 20 + 1) Next

RandomRange.Sort key1:=RandomRange End Sub

Micrusoft Excel - lisl in.j tb

■<

IfQ File Edit

SäH |

View Insert Format Tools Data Window | ßj » Arial - 10 -

Help. - 0 B / u m m

»

■A.

B

a

D

E

F

M.

T

1

2

"T3 : : i r

2

6

3

6

"4

B

"is"

10

6

11

J_

12-

0

1-3

S

13

■10

IE

12

13

J!

16

fl

/Sheet3

j <lj s I

Ready

J

Figure 4.6 Screenshot of the output

This next example will format the worksheet named Summary from the SALESMAN workbook. The screenshot displayed in Figure 4.7 is taken before the macro in Listing 4.4 is run. You will see from the screenshot that Column B displays each rep name and columns C and D contain numbers which are supposed to represent the highest and lowest sales respectively for the rep during the last six month period. The macro will insert two new rows above the first rep name. It should insert appropriate headings in the rows immediately above the columns shown. These should be "Name", "Highest Sales" and "Lowest Sales" respectively. There should also be data in column E that contains a formula that calculates the difference between the highest and lowest sales. The VBA code is shown in Listing 4.4.

Listing 4.4

Sub formatSummarySheet()

Worksheets ("Summary").Select

Range ("A1").Select

With Selection

.EntireRow.Insert

.EntireRow.Insert

End With

Range ("B2").Value = "Rep Name" Range ("C2").Value = "Highest Sales" Range ("D2").Value = "Lowest Sales" Range ("E2").Value = "Difference" Range ("E3:E11").Formula = "=C3-D3" End Sub

B Microsoft Excel - SALESMAN

Figure 4.7 Summary Worksheet Data

B Microsoft Excel - SALESMAN

Figure 4.7 Summary Worksheet Data

The VBA procedure is named formatSummarySheet and the first statement selects the required Summary worksheet. The next statement selects the range A1 ready for the insertion of the new rows using the EntireRow.Insert object reference. Range ("B2").Value = "Rep Name" sets the value in cell B2 to "Rep Name", as do the three other statements set the other headings. The statement Range ("E3:E11").Formula = "=C3-D3" assigns a relative formula to each cell in the range E3:E11, to the difference between the highest and lowest sales figure for each rep name. When the macro is run, the worksheet Summary is shown in Figure 4.8.

E3 Microsoft Excel - SALESMAN

»

File Edit Wew Insert Format I00I5 Data Window. Help - B □ ££ S If 5} | A™l ' 10 * B J" U ff « m

B

C D | E F

m

H

±1

1

2

Rep Name

Highest Sales Lowest Sales Difference

3

Andy

58 ' ® 42

4

Bindi

64 44 201

5

Katie

84 77 7

S

Misba

45 32 13

7

Ali

431 421 6

S

Amy

78: 75j 3|

3

Pete

SB 23 7

■10

Rhiannon

■eo ri si 14

11

Garefh

' 82'| 65 \ 17

12

13

k

ft < ► M week sales test.data \s«mmary £ Sheet | ] H|

Ready

Figure 4.8 Screenshot of Listing 4.4 output

Figure 4.8 Screenshot of Listing 4.4 output

0 0

Post a comment