# 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 AFor A = 18 To 18 Cells(10, A).Interior.ColorIndex = 3 Next AFor A = 19 To 19 Cells(11, A).Interior.ColorIndex = 3 Next A For A = 20 To 20 Cells(12, A).Interior.ColorIndex = 3 Next AFor A = 21 To 21 Cells(13, A).Interior.ColorIndex = 3 Next AFor A = 22 To 22 Cells(14, A).Interior.ColorIndex = 3 Next A For A = 23 To 23 Cells(15, A).Interior.ColorIndex = 3 Next AFor A = 24 To 24 Cells(16, A).Interior.ColorIndex = 3 Next AFor A = 25 To 25 Cells(17, A).Interior.ColorIndex = 3 Next A For A = 26 To 26 Cells(18, A).Interior.ColorIndex = 3 Next AFor A = 27 To 27 Cells(19, A).Interior.ColorIndex = 3 Next AFor 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 AFor A = 14 To 14 Cells(10, A).Interior.ColorIndex = 3 Next AFor A = 13 To 13 Cells(11, A).Interior.ColorIndex = 3 Next A For A = 12 To 12 Cells(12, A).Interior.ColorIndex = 3 Next AFor A = 11 To 11 Cells(13, A).Interior.ColorIndex = 3 Next AFor A = 10 To 10 Cells(14, A).Interior.ColorIndex = 3 Next A For A = 9 To 9 Cells(15, A).Interior.ColorIndex = 3 Next AFor A = 8 To 8 Cells(16, A).Interior.ColorIndex = 3 Next AFor A = 7 To 7 Cells(17, A).Interior.ColorIndex = 3 Next A For A = 6 To 6 Cells(18, A).Interior.ColorIndex = 3 Next AFor A = 5 To 5 Cells(19, A).Interior.ColorIndex = 3 Next AFor A = 4 To 4 Cells(20, A).Interior.ColorIndex = 3 Next A For A = 3 To 3 Cells(21, A).Interior.ColorIndex = 3 Next AEnd Sub

See More: Need help combing loops

#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