Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro Modification
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Modification Help | Excel Discussion (Misc queries) | |||
Macro Modification | Excel Worksheet Functions | |||
Macro Modification Help | Excel Worksheet Functions | |||
Macro Modification Help | Excel Programming | |||
Need Macro Modification | Excel Programming |