Set up a count loop

Closed
-
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
-
Hello,

How do i set up a count loop

2 replies

Posts
341
Registration date
Tuesday March 3, 2015
Status
Moderator
Last seen
July 3, 2015
69

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).
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
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