Set up a count loop

Closed
roj - Apr 7, 2015 at 08:14 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Apr 7, 2015 at 12:07 PM
Hello,

How do i set up a count loop

2 replies

MaxStart Posts 340 Registration date Tuesday March 3, 2015 Status Moderator Last seen July 3, 2015 69
Apr 7, 2015 at 10:12 AM

Single Loop

You can use a single loop to loop through a one-dimensional range of cells.
Dim i As Integer

For i = 1 To 6
    Cells(i, 1).Value = 100
Next i

The code will be executed six times. For i = 1, Excel VBA enters the value 100 into the cell at the intersection of row 1 and column 1. When Excel VBA reaches Next i, it increases i with 1 and jumps back to the For statement. For i = 2, Excel VBA enters the value 100 into the cell at the intersection of row 2 and column 1, etc.

Double Loop

You can use a double loop to loop through a two-dimensional range of cells.
Dim i As Integer, j As Integer

For i = 1 To 6
    For j = 1 To 2
        Cells(i, j).Value = 100
    Next j
Next i

For i = 1 and j = 1, Excel VBA enters the value 100 into the cell at the intersection of row 1 and column 1. When Excel VBA reaches Next j, it increases j with 1 and jumps back to the For j statement. For i = 1 and j = 2, Excel VBA enters the value 100 into the cell at the intersection of row 1 and column 2. Next, Excel VBA ignores Next j because j only runs from 1 to 2. When Excel VBA reaches Next i, it increases i with 1 and jumps back to the For i statement. For i = 2 and j = 1, Excel VBA enters the value 100 into the cell at the intersection of row 2 and column 1, etc.

Do While Loop

Besides the For Next loop, there are other loops in Excel VBA. For example, the Do While Loop. Code placed between Do While and Loop will be repeated as long as the part after Do While is true
Dim i As Integer
i = 1

Do While i < 6
    Cells(i, 1).Value = 20
    i = i + 1
Loop

as long as i is lower than 6, Excel VBA enters the value 20 into the cell at the intersection of row i and column 1 and increments i by 1. In Excel VBA (and in other programming languages), the symbol '=' means becomes. It does not mean equal. So i = i + 1 means i becomes i + 1. In other words: take the present value of i and add 1 to it. For example, if i = 1, i becomes 1 + 1 = 2. As a result, the value 20 will be placed into column A five times (not six because Excel VBA stops when i equals 6).
1
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Apr 7, 2015 at 12:07 PM
That is a great and extensive explanation MaxStart!

Not sure though if it's an answer to roj's query.

My question to roj would be: What do you want to count?

Using a loop is not the most efficient way to count, better make use of worksheet functions.

The following example will count AAA in range A1:A10:
Sub RunMe()
Dim CountedValue As Integer
CountedValue = Application.WorksheetFunction.CountIf(Range("A1:A10"), "AAA")
Range("B1") = CountedValue
End Sub


Best regards,
Trowa
0