Miscellaneous Exercise of conditional statements and Loop
We have the already worked with syntax and examples of conditional statements and loops in the previous tutorials. In this tutorial, we will learn how to work with both together. We will explain how to run the loops based on objectives and will try to develop logic as per the specified criteria. In VBA or any other programming language, the whole game is based on logic. There are many ways to solve or encounter any task. The smarter you thought and built the logic, the shorter the code becomes, and hence, increasing the productivity of the code.
In the below, we have given some examples, try to solve them first without looking at the solutions. Your code may be lengthy at first, but slowly you will gain pace and learn how to code to the point and become a VBA developer.
NOTE: Always try to use fewer variables while programming.
Example 1
Objective: Categorize the student’s basis on the criterion given below
- Less Than 100 - Fail
- Between 100 & 150 - Pass
- More than 150 Excellent
Sub Miscellaneous_Example1() Dim rng As Range Dim cell As Range Set rng = Range("E2:E11") 'rng is a Range collection and For-Each is moving into each cell of rng collection For Each cell In rng ‘nested-if to check for the various conditions If cell < 100 Then Cells(cell.Row, 6) = "Fail" ElseIf cell >= 100 And cell <= 150 Then Cells(cell.Row, 6) = "Pass" ElseIf cell > 150 Then Cells(cell.Row, 6) = "Excellent" End If Next End Sub
Output: Press F5 for the output (for sone laptops press function+f5). You will get the following output
Name | Physics | English | Math | Total | Result |
Itrat Zaidi | 12 | 57 | 11 | 80 | Fail |
Thomas A. Edison | 41 | 38 | 11 | 90 | Fail |
Charles Wait | 74 | 45 | 14 | 133 | Pass |
Benazir Mohamad | 95 | 65 | 67 | 227 | Excellent |
Thomas R. Butkus | 67 | 38 | 28 | 133 | Pass |
Illas Booda | 88 | 19 | 95 | 202 | Excellent |
Craig E. Dahl | 41 | 63 | 74 | -178 | Fail |
Robert D. Gecht | 35 | 44 | 10 | 89 | Fail |
Waandy Riitar | 90 | 62 | 91 | 243 | Excellent |
Randy Newman | 51 | 74 | 32 | 157 | Excellent |
Example 2 (Mostly Asked Interview Question).
Objective: Print Pyramid Pattern and write a macro that creates pyramid using VBA:
Sub Miscellaneous_Example2() Dim r As Byte Dim col As Byte ‘will put off the gridlines from the excel sheet. ActiveWindow.DisplayGridlines = False For r = 1 To 5 ‘step 1: r=1, col 5 to 5: code will run once, hence printing * once at cell (1,5) position. ‘step 2: r=2, col 4 to 6: code will run thrice, hence printing * three times at cell (2, 4), cell (2,5), cell (2,6) ‘step 3: r=3, col 3 to 7: code will run five times, hence printing * five times at cell (3,3), cell (3,4), cell (3,5), cell (3,6), cell (3,7) ‘step 4: : r=4, col 2 to 8: code will run seven times, hence printing * seven times at cell (4,2), cell (4,3), cell (4,4), cell (4,5), cell (4,6), cell (4,7), cell (4,8) ‘step 5: : r=5, col 1 to 9: code will run five times, hence printing * five times at cell (5,1), cell (5,2), cell (5,3), cell (5,4), cell (5,5), cell (5,6), cell (5,7), cell (5,8) position. For col = 5 - r + 1 To 4 + r Cells(r, col).Value = "*" Next Next End Sub
Output: Press F5 for the output (for sone laptops press function+f5). You will get the following output
* * * * * * * * * * * * * * * * * * * * * * * * *
Example 3
Objective: Write a loop procedure that Fills the table as shown in the image on the right:
Column "S.No" will go from 1 to 27 and Column "Name" will have "Name+SlNo (i.e. Name1, Name2)
> If the "Sl.No" is greater than 20, highlight the cells of column B in Blue,
> If the "SI.No" is greater than 15, highlight the cells of column A in Green
Sub Miscellaneous_Example3() Dim rw As Byte For rw = 1 To 25 'Loop runs 10 times as the upperbound is set to 10 Cells(rw, 1) = rw Cells(rw, 2) = "Name" & rw If rw > 15 Then Range("A" & rw).Interior.Color = vbBlue If rw > 20 Then Range("B" & rw).Interior.Color = vbGreen End If End If Next End Sub
Output: Press F5 for the output (for sone laptops press function+f5). You will get the following output
1 | Name1 |
2 | Name2 |
3 | Name3 |
4 | Name4 |
5 | Name5 |
6 | Name6 |
7 | Name7 |
8 | Name8 |
9 | Name9 |
10 | Name10 |
11 | Name11 |
12 | Name12 |
13 | Name13 |
14 | Name14 |
15 | Name15 |
16 | Name16 |
17 | Name17 |
18 | Name18 |
19 | Name19 |
20 | Name20 |
21 | Name21 |
22 | Name22 |
23 | Name23 |
24 | Name24 |
25 | Name25 |
26 | Name26 |
27 | Name27 |