Programmatically set Rows to Repeat At Top using VBA
http://www.vbtutor.net/VBA/vba_chp9.htm
http://www.codeforexcelandoutlook.com/
Chapter 9: Creating a Counter in Excel VBA
Private Sub CommandButton1_Click()
Dim i, counter As Integer
For i = 1 To 20

If Cells(i, 2).Value > 50 Then
counter = counter + 1
Cells(i, 2).Font.ColorIndex = 5
Else
'do nothing
Cells(i, 2).Font.ColorIndex = 3

End If
Next i
Cells(21, 2).Value = counter
Cells(22, 2).Value = 20 - counter

End Sub



Explanation:
This program combines For..Next and If ...Then...Else statements to control the program flow. If the value in that cell is more than 50, the value of counter is increased by 1 and the font color is changed to blue (the colorIndex is 5) , otherwise there is no increment in the counter and the font color is changed to red (ColorIndex=3)
********************************************************************************************
Chapter 12 : Creating a Quadratic Equation Solver



Private Sub CommandButton1_Click()

Dim a, b, c, det, root1, root2 As Single

a = Cells(2, 2)
b = Cells(3, 2)
c = Cells(4, 2)
det = (b ^ 2) - (4 * a * c)
If det > 0 Then
root1 = (-b + Sqr(det)) / (2 * a)
root2 = (-b - Sqr(det)) / (2 * a)
Cells(5, 2) = Round(root1, 2)
Cells(6, 2) = Round(root2, 2)

ElseIf det = 0 Then
root1 = (-b) / 2 * a
Cells(5, 2) = root1
Cells(6, 2) = root1
Else
Cells(5, 2) = "No root"
End If
End Sub
Explanation:
The format of the quadratic equation is as below:

ax2+bx+c , where a,b,c are constants.

The number of roots depends on the determinant of b2-4ac

If b2-4ac>0 then there are two roots

If b2-4ac=0 then there is only one root

If b2-4ac<0 then there is no root.

By making use the above conditions and employ the use of If....Then...Else statements, the program is able to solve the quadratic equation

*********************************************************************************************
Chapter 13 : Creating a BMI Calculator
Body Mass Index (BMI) is so popular today that it has become a standard measure for our health status. If your BMI is too high, it means you are overweight and would likely face a host of potential health problems associated with high BMI, such as hypertension, heart disease, diabetics and many others. I have programmed a BMI calculator using VB6 professional, but now I will show you how to create a VBA BMI calculator in MS Excel.

　

Private Sub CommandButton1_Click()
Dim weight, height, bmi, x As Single

weight = Cells(2, 2)
height = Cells(3, 2)
bmi = (weight) / height ^ 2
Cells(4, 2) = Round(bmi, 1)
If bmi <= 15 Then
Cells(5, 2) = "Under weight"
ElseIf bmi > 15 And bmi <= 25 Then
Cells(5, 2) = "Optimum weight"
Else
Cells(5, 2) = "Over weight"
End If
End Sub



　
Explanation:

The formula for calculating BMI is

BMI=wieght(/(height2)

The function Round is to round the value to a certain decimal places. It takes the format Round(x,n), where n is the number to be rounded and n is the number of decimal places.

The second part of the program usees the If...Then..Else statement to evaluate the weight level.
*********************************************************************************************
Chapter 11: Range Selection and Manipulation
We can program a VBA that can select certain range of cells and at the same time perform certain tasks according to a set of conditions. In this example, I program the VBA such that it can accept range input from the user and then change the mark to blue if it is more than or equal to 50 and change it to red if the mark is less than 50.
Private Sub CommandButton1_Click()
Dim rng, cell As Range, selectedRng As String
selectedRng = InputBox("Enter your range")
Set rng = Range(selectedRng)
For Each cell In rng
If cell.Value >= 50 Then
cell.Font.ColorIndex = 5
Else
cell.Font.ColorIndex = 3
End If
Next cell
End Sub



Explanation:

The InputBox function is used to accept value from the users.

rng and cell are declared as a Range variable using the Dim statement while selectedRng is declared as a string that receive input from the user.

Once the input is obtained from the user, it is stored using the Set method and the Range function.

For Each cell In rng ......Net cell is a loop that can iterate through the selected range, one cell at a time.

The If...Then...Else statements are to specify the color of the font according to the range of values determined by the conditions.