Find Maximum Value in Moving Range

Below is a VBA function that finds a maximum value in a continuos range. The measured range represents a portion of the overall range. The measured range length is always equal to a pre-defined value.  It advances with the next cell in the overall range. The function accepts 2 parameters: an overall range (address) and a period measured. If a maximum value is found, the function puts a border around the cell that contains it.

Usage example: FindMaxFromRange "B10:B155", 30

Function FindMaxFromRange(rng As Range, iPeriod As Integer)
        Dim cell As Range
        Dim i As Integer
        Dim val As Double
        Dim max As Double
        Dim imax As Integer
        i = 0
        max = 0
        imax = 0

        For Each cell In rng
            i = i + 1
            val = cell.Value

            If (i - imax) > iPeriod Then
                max = 0
            End If

            If val > max Then
                max = val
                imax = i
                cell.BorderAround xlContinuous, xlThin, xlColorIndexAutomatic
            End If
        Next
    End Function

No comments:

Post a Comment