Need help combing loops

June 14, 2009 at 07:33:29
Specs: Windows Vista
I have written this code to get familiar with Loops. I am new to this but god I love it. If there is some one that can tell me how to shorten this it would be great. All this does is color cells to make a Letter A.

Sub ColorCells()
For A = 17 To 17
Cells(9, A).Interior.ColorIndex = 3
Next A
For A = 18 To 18
Cells(10, A).Interior.ColorIndex = 3
Next A
For A = 19 To 19
Cells(11, A).Interior.ColorIndex = 3
Next A
For A = 20 To 20
Cells(12, A).Interior.ColorIndex = 3
Next A
For A = 21 To 21
Cells(13, A).Interior.ColorIndex = 3
Next A
For A = 22 To 22
Cells(14, A).Interior.ColorIndex = 3
Next A
For A = 23 To 23
Cells(15, A).Interior.ColorIndex = 3
Next A
For A = 24 To 24
Cells(16, A).Interior.ColorIndex = 3
Next A
For A = 25 To 25
Cells(17, A).Interior.ColorIndex = 3
Next A
For A = 26 To 26
Cells(18, A).Interior.ColorIndex = 3
Next A
For A = 27 To 27
Cells(19, A).Interior.ColorIndex = 3
Next A
For A = 28 To 28
Cells(20, A).Interior.ColorIndex = 3
Next A
For A = 29 To 29
Cells(21, A).Interior.ColorIndex = 3
Next A

For A = 10 To 23
Cells(15, A).Interior.ColorIndex = 3
Next A
For A = 16 To 16
Cells(8, A).Interior.ColorIndex = 3
Next A
For A = 15 To 15
Cells(9, A).Interior.ColorIndex = 3
Next A
For A = 14 To 14
Cells(10, A).Interior.ColorIndex = 3
Next A
For A = 13 To 13
Cells(11, A).Interior.ColorIndex = 3
Next A
For A = 12 To 12
Cells(12, A).Interior.ColorIndex = 3
Next A
For A = 11 To 11
Cells(13, A).Interior.ColorIndex = 3
Next A
For A = 10 To 10
Cells(14, A).Interior.ColorIndex = 3
Next A
For A = 9 To 9
Cells(15, A).Interior.ColorIndex = 3
Next A
For A = 8 To 8
Cells(16, A).Interior.ColorIndex = 3
Next A
For A = 7 To 7
Cells(17, A).Interior.ColorIndex = 3
Next A
For A = 6 To 6
Cells(18, A).Interior.ColorIndex = 3
Next A
For A = 5 To 5
Cells(19, A).Interior.ColorIndex = 3
Next A
For A = 4 To 4
Cells(20, A).Interior.ColorIndex = 3
Next A
For A = 3 To 3
Cells(21, A).Interior.ColorIndex = 3
Next A
End Sub


See More: Need help combing loops

Report •


#1
June 14, 2009 at 15:20:57
I assume you realize that all the snippets that look like this are not really "loops" since they only execute once. "A" would have to span, at a minimum, from 17 to 18 for it to actually "loop".

For A = 17 To 17
 Cells(9, A).Interior.ColorIndex = 3
Next A

You could have saved a lot of typing by simply using this syntax with different Row and Column numbers.

Cells(9, 17).Interior.ColorIndex = 3

The only true loop in your code is this one, since it actually loops through a series of A's:

For A = 10 To 23
 Cells(15, A).Interior.ColorIndex = 3
Next A

Here are two ways to shorten your code. The first macro uses separate loops for the left and right legs. The second one uses 1 loop and then either colors the left or right leg depending on the value of A. They both color the horizontal leg in one line, with no loop required.


Sub MakeA()
'Two loop method
'Right Hand Leg
  For A = 17 To 29
   B = A - 8
   Cells(B, A).Interior.ColorIndex = 3
  Next

'Horizontal leg
  Range(Cells(15, 10), Cells(15, 23)).Interior.ColorIndex = 3

'Left Hand Leg
  B = 7
   For A = 16 To 3 Step -1
    B = B + 1
     Cells(B, A).Interior.ColorIndex = 3
   Next

End Sub

Sub MakeA2()
'One loop method
'Left hand Leg
 B = 22
  For A = 3 To 29
   If A < 17 Then
    B = B - 1
     Cells(B, A).Interior.ColorIndex = 3

'Right Hand Left
   ElseIf A > 16 Then
    B = A - 8
     Cells(B, A).Interior.ColorIndex = 3
   End If
  Next

'Horizontal Leg
  Range(Cells(15, 10), Cells(15, 22)).Interior.ColorIndex = 3
End Sub



Report •

#2
June 15, 2009 at 13:09:23
Believe it or not, this code might be quicker than the other 2. Since it is not coloring a cell each time is goes through a loop, it's probably faster, although you probably wouldn't notice it unless it was running on a very slow system.

The point here is that code can be made more efficient if more can be done within VBA as opposed to updating the spreadsheet multiple times. As your code gets more complex, this concept could make a big difference.

Sub MakeA3()
'Two loop method
'Build Right Hand Leg Range
  For A = 17 To 29
   B = A - 8
    RightRangeTmp = RightRangeTmp & "," & Cells(B, A).Address
  Next
     RightRange = Right(RightRangeTmp, Len(RightRangeTmp) - 1)

'Build Horizontal Leg Range
  HorzRange = Range(Cells(15, 10), Cells(15, 23)).Address

'Build Left Hand Leg Range
  B = 7
   For A = 16 To 3 Step -1
    B = B + 1
     LeftRangeTmp = LeftRangeTmp & "," & Cells(B, A).Address
   Next
     LeftRange = Right(LeftRangeTmp, Len(LeftRangeTmp) - 1)

'Color Entire Range At One Time
     Range(RightRange & "," & HorzRange & "," & LeftRange).Interior.ColorIndex = 3
End Sub


Report •

Related Solutions


Ask Question