Selective Summation in VBA

Today we shall touch on VBA again. I have created a VBA that can do selective summation according to a set of conditions. For example, you might just want to sum up those figures that have achieved sales target and vice versa. The VBA program I am showing you can sum up marks that are below 50 (which considered as failed) as well as those marks which are above 50 (which considered as passed). Here is the program

　

Private Sub CommandButton1_Click()
Dim rng As Range, i As Integer
Dim mark, sumFail, sumPass As Single
sumFail = 0
sumPass = 0
Set rng = Range("A1:A10")
For i = 1 To 10
mark = rng.Cells(i).Value
Select Case mark
Case Is < 50
sumFail = sumFail + mark
Case Is >= 50
sumPass = sumPass + mark
End Select
Next i
MsgBox "The sum of Failed marks is" & Str(sumFail) & vbCrLf & "The sum of the Passed marks is" & Str(sumPass)

End Sub
　

Explantion

　
rng is declared as range and we can set it to include certain range of cells, here the range is from A1 to A10.
Then I used the For .......Next loop to scan through the selected range
rng.Cells(i).Value read the value in cells(i) and then passed it to the variable mark.
To do selective addition, I used the statement Select Case....End Select
Finally, the results are shown in a message box
　
　

Selective Summation using VBA