Selective Summation in VBA

In this lesson, we have created a VBA that can perform 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 Passed marks is" & Str(sumPass)

End Sub

Explanation:

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　

Chapter 18: Selective Summation using VBA