Kris,

A bit unclear on what you want to do, by maybe the code below will get you
going in the right direction....

HTH,
Bernie
MS Excel MVP

Sub AvgGT15Ver2()
Dim AOI As Range
Dim StoreResult As Range
Dim c As Range

Set StoreResult = [M1]
Set AOI = [D1:D52] '111]
[M1:M52] '111].ClearContents

For Each c In AOI
If c.Value 150 Then
StoreResult.Value = Application.Average( _
c.Offset(1, 0).Resize(12, 1))
Set StoreResult = StoreResult.Offset(1, 0)
End If
Next c
End Sub


wrote in message
ups.com...
Hi All!

I know this should be a simple change, however it is currently eluding
me for some reason.

The code below finds groupings of values over 150 and stops after the
values that go below 150. It then lists all of the averaged groupings.


What I now need to do is when the value is over 150, it will average
the next 12 cells below in the column and then keep doing that for the
entire range. So basically, instead of averaging the various groupings
over 150, it needs to average the groupings of 12 cells that begin with
a value over 150. I hope that I explained that thoroughly enough!

The following is the code that I currently have:

Option Explicit
Sub AvgGT15()
Dim AOI As Range
Dim StoreResult As Range
Dim c As Range
Dim i As Long
Dim Result()

Set StoreResult = [M1]
Set AOI = [D1:D52111]
[M1:M52111].ClearContents

i = 0
ReDim Preserve Result(i)

For Each c In AOI
If c.Value < 150 Then
If Result(0) = 150 Then
StoreResult.Value =
Application.WorksheetFunction.Average(Result())
Set StoreResult = StoreResult.Offset(1, 0)
End If
i = 0
Result(0) = 0
Else
ReDim Preserve Result(i)
Result(i) = c.Value
i = i + 1
End If
Next c

If Result(0) = 150 Then StoreResult.Value = _
Application.WorksheetFunction.Average(Result())

End Sub


That's it!!! Please post suggestions and modifications!

Thanks in advance,

Kris Taylor
www.QuestOfAges.org Administrator