This page will go through Looping. An essential part of writing excel macros as that is what causes the repetitive behavior in your scripts. Testing criteria, cell value's colors or texts. This, in combination with IF statements is a powerful combination and works as a great founding block to any programmer regardless of the programming language.
Loops can be divided in to two types of loops: For Loops, or While Loops. For Loops are definitely the ones I use the most. Whilst creating While loops can be a good thing sometimes it can also get your code into an annoying endless circle as it is basically just a for loop to infinity (or laptop crashing).
There are also two popular versions of the For Loop:
1. For i where 'i' is a counter in number series or...
2. For Each Cell where is the 'Cell' is a part of a greater Range.
For i= 1 to 10
'Do something.
Next i
Alternatively we can use the For each syntax below.
For Each Cell in Range("A1:A10")
'Do something.
Next Cell.
For Loops examples
Looping to 10 step 1
Sub Loop1 ()
'Clears any previous content in the sheet.
Cells.Clear
'Steps 1 cell each time and prints it in column A. (1,2,3...10)
For i = 1 To 10
Cells(i, 1) = i
'Range("A"&i), this the same thing.
Next i
End Sub
Looping to 10 and steps 2 cells in each loop
Sub Loop2()
Cells.Clear
'Steps 2 each time and prints it in column A.(1,3,5...9)
For i = 1 To 10 Step 2
Cells(i, 1) = i
Next i
End Sub
Reverse Looping
Sub LoopReverse()
Cells.Clear
'Steps -1 each time and prints it in column A.
'The result is exactly the same as our first loop.
'However, this is printed in the reverse order starting from A10.
For i = 10 To 1 Step -1
'This will print in reverse order.
Cells(i, 1) = i
Next i
End Sub
For Each Cell Loop
Sub LoopForEach()
'We are defining a counter variable here.
i = 1
For Each Cell In Range("A1:A10")
'Prints out the current "i" value. and adds +1
for each loop.
Cell.Value = i
i = i + 1
Next Cell
End Sub
Double Looping (a loop in a loop.. I know, Inception right!!)
Sub LoopDouble()
'Double loop
'For every loop 'i' value the inner loop 'j' will complete its loop to 10.
'Steps into the first outer loop with variable i.
For i = 1 To 10
'Steps into the second inner loop with variable j.
For j = 1 To 10
Cells(i, j) = i * j
Next j
Next i
'Exiting the inner loop 'j' first and then 'i'
End Sub
While Loops examples
Looping to 10 step 1 (same result as above but now with 'While')
Sub LoopWhile()
i = 1 'Need to set a value for i in order to start on cell A1.
'Steps 1 each time and prints it in column A. (1,2,3...10)
Do While i <= 10
Cells(i, 1) = i
'Don't forget this part. If you do, it will be an endless loop.
'Of course you can add any value you wish to variable "i".
i = i + 1
Loop
End Sub
Loop with IF statement
Sub LoopIF()
'Steps 2 each time and prints it in column A.(1,3,5...9)
For i = 1 To 10
'prints out our number series.
Cells(i, 1) = i
'This will color all even cellnumbers to yellow.
'Mod 2 is checking the remainder of a division by 2.
'since even numbers are evenly divided by 2 there is no remainder.
If i Mod 2 = 0 Then
'colors blue for even numbers.
Cells(i, 1).Interior.Color = RGB(100, 100, 255)
End If
Next i
End Sub
Loop with IF ELSE statement
Sub Loop_IF_Else()
'Steps 2 each time and prints it in column A.(1,3,5...9)
For i = 1 To 10
'prints out our number series.
Cells(i, 1) = i
'This will color all even cellnumbers to yellow.
'Mod 2 is checking the remainder of a division by 2.
'since even numbers are evenly divided by 2 there is no remainder.
If i Mod 2 = 0 Then
'colors blue for even numbers.
Cells(i, 1).Interior.Color = RGB(100, 100, 255)
Else
'colors yellow for uneven numbers.
Cells(i, 1).Interior.Color = RGB(255, 255, 0)
End If
Next i
End Sub
Double Loop with IF, ELSEIF, ELSE statement
Sub LoopDouble_Ifs()
'Clears any previous cells
Cells.Clear
'Double loop
'Steps into
For i = 1 To 10
For j = 1 To 10
'Printing out the number
Cells(i, j) = i * j
'Let's add some colors
'i*j<10
If i * j < 10 Then
'Red colors up to value 9.
Cells(i, j).Interior.Color = RGB(255, Rnd * 255, 0)
'10 <= i*j <= 20
ElseIf i * j >= 10 And i * j <= 20 Then
'Blue colors between values 10 and 20.
Cells(i, j).Interior.Color = RGB(0, Rnd * 255, 255)
'i*j = 49 or i*j = 100
ElseIf i * j = 49 Or i * j = 100 Then
'Green color for value 49 and 100.
Cells(i, j).Interior.Color = RGB(150, 200, 100)
'i*j = Any number except value 72.
ElseIf Not i * j = 72 Then
'colors the rest yellow, except values 72.
Cells(i, j).Interior.Color = RGB(255, 255, 0)
End If
Next j
Next i
End Sub
EPTU Machine ETPU Moulding…
EPTU Machine ETPU Moulding…
EPTU Machine ETPU Moulding…
EPTU Machine ETPU Moulding…
EPTU Machine ETPU Moulding…
EPS Machine EPS Block…
EPS Machine EPS Block…
EPS Machine EPS Block…
AEON MINING AEON MINING
AEON MINING AEON MINING
KSD Miner KSD Miner
KSD Miner KSD Miner
BCH Miner BCH Miner
BCH Miner BCH Miner