Set up a count loop
Closed
roj
-
Apr 7, 2015 at 08:14 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Apr 7, 2015 at 12:07 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Apr 7, 2015 at 12:07 PM
Related:
- Vba counter loop
- Counter strike 1.6 cheats - Guide
- Counter strike 2 download - Download - Shooters
- Steam fps counter not showing - Guide
- Vba case like - Guide
- Number to words in excel without vba - Guide
2 responses
MaxStart
Posts
339
Registration date
Tuesday March 3, 2015
Status
Moderator
Last seen
July 3, 2015
69
Apr 7, 2015 at 10:12 AM
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 trueDim 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).
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Apr 7, 2015 at 12:07 PM
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:
Best regards,
Trowa
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
