Summary
Loop over a data table. For each record, add a field to a total, count, or whatevs.
Situation
You have a data set, and you want to total up or count the values.
Needs
A data set.
Provides
A variable with a total or count.
Action
An example, totaling goat weight in an array:
- sTotalWeight = 0
- For sCurrentRecordNumber = 1 To sNumberOfRecords
- sTotalWeight = sTotalWeight + rGoatData.Cells(sCurrentRecordNumber, 1)
- Next sCurrentRecordNumber
- MsgBox "Total weight: " & sTotalWeight
Notice that sTotalWeight is initialized in line 1.
Often used with a filter, like this one to count light goats:
- sCountLightGoats = 0
- For sCurrentRecordNumber = 1 To sNumberOfRecords
- If rGoatData.Cells(sCurrentRecordNumber, 1) < 20 Then
- sCountLightGoats = sCountLightGoats + 1
- EndIf
- Next sCurrentRecordNumber
- MsgBox "Number of light goats: " & sCountLightGoats
The If statement in line 3 accesses the data in asGoatWeight() directly. If there are several fields in each record, you might put the field values in their own variables. That's what I do.
- sCountLightGoats = 0
- sCountTallGoats = 0
- sCountCuteGoats = 0
- sCountHappyGoats = 0
- For sCurrentRecordNumber = 1 To sNumberOfRecords
- ' Get fields of current record.
- sCurrentGoatWeight = rGoatData.Cells(sCurrentRecordNumber, 1)
- sCurrentGoatHeight = rGoatData.Cells(sCurrentRecordNumber, 2)
- sCurrentGoatCuteness = rGoatData.Cells(sCurrentRecordNumber, 3)
- sCurrentGoatHappiness = rGoatData.Cells(sCurrentRecordNumber, 4)
- ' Process fields for current record.
- If sCurrentGoatWeight < 20 Then
- sCountLightGoats = sCountLightGoats + 1
- EndIf
- If sCurrentGoatHeight > 40 Then
- sCountTallGoats= sCountTallGoats+ 1
- EndIf
- ...
- Next sCurrentRecordNumber
Where referenced