Find smallest/largest values

Tags
Range, Loop
Summary

Loop across some records. Put an If in the loop, comparing the current value with the smallest/largest so far.

Situation

You have some records in a Range.

Needs

You want the smallest and/or largest value(s) for a field in the records.

Provides

Smallest/largest values.

Action

Here's code that gets the minimum and maximum from a Range:

  • sMinimumWeight = 3.4028235E+38
  • sMaximumWeight = -3.4028235E+38
  • ...
  • For sCurrentRecord = 1 to sNumberRecords
  •     sWeight = rGoatDataTable.Cells(sCurrentRecord, FIELD_WEIGHT)
  •     If sWeight < sMinimumWeight Then
  •         sMinimumWeight = sWeight
  •     End If
  •     If sWeight > sMaximumWeight Then
  •         sMaximumWeight = sWeight
  •     End If
  • Next sCurrentRecord

You could get other fields, like a goat name, as well:

  • sMinimumWeight = 3.4028235E+38
  • tMinimumWeightGoatName = ""
  • sMaximumWeight = -3.4028235E+38
  • tMaximumWeightGoatName = ""
  • ...
  • For sCurrentRecord = 1 to sNumberRecords
  •     tName = rGoatDataTable.Cells(sCurrentRecord, FIELD_NAME)
  •     sWeight = rGoatDataTable.Cells(sCurrentRecord, FIELD_WEIGHT)
  •     If sWeight < sMinimumWeight Then
  •         sMinimumWeight = sWeight
  •         tMinimumWeightGoatName = tName
  •     End If
  •     If sWeight > sMaximumWeight Then
  •         sMaximumWeight = sWeight
  •         tMaximumWeightGoatName = tName
  •     End If
  • Next sCurrentRecord
Where referenced
Min and max