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