Hey Joel,
You are the Man!!! That macro accomplished exactly what I've been
trying to do.
I guess you probably get this response many time throughout the day,
but I have to say it anyway, “Thank you so much”!!!
When I ran the macro over many different data streams, it performed
exactly as intended.
But it also revealed something that I had overlooked before now that is
missing.
With the current capabilities that this macro provides, I can now
target that worst case scenario, but what I would like to know is would
you mind modifying the macro to capture the last possible consecutive
values that meet the same criteria, except the 13 high values are an
average of all the high values?
This way I'll have a norm to compare with the worst case scenario in my
chart graph. I noticed how direct your code addressed each step as
requested, so I'll bet changing the macro to capture the average is
something simple that is still beyond my VBA abilities. This is my final
request on this task if you don't mind helping me with it. I would guess
that both processes could be combined, but I would prefer to run them
separately as needed!
If you are willing to help me with this, the cells to store the
addresses of the second macro for the average high consecutive values
would be as follows;
Preceding 5 cell Group Range = T7
Lowest 1 of Highest 13 Value = T8
Highest 13 group Range = T9
Trailing 22 Cell Group Range = T10
And the range to copy the full consecutive 40 cell values into is
"AK2:AK41".
As a long shot guess, would changing the following line of your code be
moving in the right direction?
Changing this,
Total = Evaluate("Sum(AB" & RowCount &
":AB" & (RowCount + GroupSize - 1) & ")")
To this?
Total = Evaluate("Avg(AB" & RowCount &
":AB" & (RowCount + GroupSize - 1) & ")")
And thank you again Joel for the help you have already given me.
You guys are really the greatest!
Cecil,
joel;579495 Wrote:
This is pretty simple
Sub GetHigh()
Const GroupSize = 13
LastRow = Range("AB" & Rows.Count).End(xlUp).Row
MaxCount = 0
FirstRow = 0
'find the hisghest consecuitive 13 numbers by getting the sum of the
values
For RowCount = 2 To (LastRow - GroupSize + 1)
Total = Evaluate("Sum(AB" & RowCount & ":AB" & (RowCount + GroupSize
- 1) & ")")
If Total MaxCount Then
FirstRow = RowCount
MaxCount = Total
End If
Next RowCount
Set FivePreviousRows = Range("AB" & (FirstRow - 5) & ":AB" & (FirstRow
- 1))
Range("W2") = FivePreviousRows.Address
Set DataRange = Range("AB" & FirstRow & ":AB" & (FirstRow + GroupSize -
1))
Min = WorksheetFunction.Min(DataRange)
Range("W3") = Min
Range("W4") = DataRange.Address
StartRow = FirstRow + GroupSize
EndRow = StartRow + 21
'Don't exceed the length of data
If EndRow LastRow Then
EndRow = LastRow
End If
Set TwentyTwoNextRows = Range("AB" & StartRow & ":AB" & (EndRow))
Range("W5") = TwentyTwoNextRows.Address
'copy data
Range("AB" & (FirstRow - 5) & ":AB" & EndRow).Copy _
Destination:=Range("F4")
End Sub
--
c-town
------------------------------------------------------------------------
c-town's Profile: 695
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=160004
Microsoft Office Help