Hello,

Please help!!

I have a worksheet with multiple result rows with varying rows within each result.
The result row is also above the detail, not below.

What my code is doing is finding the ranges between the two result rows and caculating the median using only the two ranges after the first result row and before the last result row. I need it to perform the median on the complete range, i.e. (rng1:rng2) and not (rng1, rng2). Also, I need the result row above to be updated with the value, and not the result row below. Code and example follows.

Many Thanks
Michelle


e.g.

Column E Column H
Result 20 5
Row 1 4
Row 2 6
Row 3 5
Row 4 5
Result 10 2
Row 1 7
Row 2 2
Row 3 1
Result 40 6
Row 1 2
Row 2 10
Row 3 20
Row 4 6
Row 5 2



Sub calc_median()

Dim rng1 As Range, rng2 As Range, total As Integer, i As Integer

total = 0#


While Cells(ActiveCell.Row, "B").Interior.ColorIndex <> xlNone
If Cells(ActiveCell.Row, "B").Value = "Result" Then
Cells(ActiveCell.Row, "H").Value = total
total = 0#
Set rng1 = Cells(ActiveCell.Row + 1, "E")
Cells(ActiveCell.Row + 1, "B").Select
End If
While Cells(ActiveCell.Row, "B").Value <> "Result"
Cells(ActiveCell.Row + 1, "B").Select
Wend
If Cells(ActiveCell.Row, "B").Value = "Result" Then
Set rng2 = Cells(ActiveCell.Row - 1, "E")
total = Application.Median(rng1, rng2)
total = total
End If
Wend
End Sub