Nesting One Loop Inside Another Loop

It is okay to run a loop inside another loop. Perhaps the first loop is running through all the rows in your recordset. The second loop then might run through all the columns in your recordset. Consider the dataset in Figure 5.10:

Figure 5.10

By nesting one loop inside the other,VBA can loop through each row and then each column.

1 Wen_^ ,1-ut'j hrtiuarir MiiCh Hi/ JunH Juty Augu,1 aplflmbiii O^ühör NmBrntbn U'

2 hSShweTSI™ l 572,63? i LhJj i .3*5 £98 i,i7i£ffl i 6*4,396 1,538336 i;ü/wj ,1'J,' 156 rPtms ,533 7sa -j ürflwHi* ¡dium.T zaf/ie 196£39 210,636 16372F isu=j 140.720 iff ,3« 1^4,972 mm U3ß59 137283 1HJLur t i^KB^mi 473,433 -W-" ,/\.V, £54 < 1A Jfl1 30*£32 360.3*5 474033 SB7.H7 374 y- -

5 'CaM<ifG™ds5dJ 1,064,951 tHO,^27 365,370 942 33 740.136 DO*,3&I 645,115 1096,*75 63 436 925214 643^50

6 E.püniü 3SBJ 33131 BrtP Mt.313 269141 334 631 626 SC »7.797 06 39/31 326603 306 7467 SUäpK 160006t 1^3 ODO 160000 190X60 160096 lSOflOO 150000 150000 16O 03 I5COCO 160 0» 9 F?j 12500ft 13536 12500ft 13630 12500ft 13430 125000 126000 125000 135X66 125000 13536"

1 Loop through each row and column

1 Add a checkerboard format

FinalRow = Cells(65536, 1).End(xlUp).Row

FinalCol = Cells(1, 255).End(xlToLeft).Column

For I = 2 to FinalRow

1 For even numbered rows, start in column 1 ' For odd numbered rows, start in column 2 if I mod 2 = 1 then StartCol = 1

Else

StartCol = 2 End if

For J = StartCol to FinalCol Step 2 Cells(I, J).Interior.ColorIndex = 35 Next J Next I

In this code, the outer loop is using the I counter variable to loop through all the rows in the dataset. The inner loop is using the J counter variable to loop through all the columns in that row. Because Figure 5.10 has 7 data rows, the code runs through the I loop 7 times. Each time through the I loop, the code runs through the J loop 6 or 7 times. This means that the line of code that is inside the J loop ends up being executed several times for each pass through the I loop. The result is shown in Figure 5.11.

Aiiguil Siplembei Odober NOrtrrftisr December

2 Hardwir* Kmnua 1^?2j63? 1655,321 1J56.234 1,531060 1.3*5,699 J.1?2j6eö 1JSU.2!» 1,538;936 1,975,409 1,19056 1,1 «¿OS 1*33«?»

3 ' Seftvrtii liirtnu* 236/16 198639 210626 183727 161,4ä4 140,720 197,116 1&I672 237049 143659 13733 184JW

4 Seme* Rewrws 473,433 397,277 421,256 367,454 322,966 261,440 394632 359,345 474,096 267.317 274,566 369X05

5 Cow Goods ScM 1,064,951 910,427 966,379 042/363 740,135 644,967 904,364 346,415 1,066;475 656.436 629,214 84355»

6 Selling Enponao 391,527 331 £64 361047 306,212 260,140 234,534 326,860 307,787 396,082 239.431 228805 306.746.

7 G&A EipunirV 150000 150£00 150000 150£00 150000 150000 150,000 150,000 150000 150,000 150,000 150 .000 6 R&O 126000 125,000 126000 125,000 126000 125,000 125000 125,000 125000 125,000 126000 135 CCD

0 0

Post a comment