Hi Kyle

I am sorry for the delay getting back to you. It is no longer the
weekend and we have the auditors (ehem proctologists) in here at work
and time has been at a premium.

OK there are some discrepancies between what you have as Peaks./
Troughs which are addressed below.

You say row 61 is the first trough but Row 51 has a variance of
-0.0067 between the SP and the ARL. This is the first time the
variance breaks -.0033 so this is where the process starts. With this
in mind row 53 and 54 are both Troughs with -0.0071 and -0.0072
variance from the SP. There is not a Peak per se till row 73 where
the Peak – Trough is 0.0098.

There is a trough on Row 78 as you suggested there was. Only one more
mention but I could go on. Why is Row 180 a Trough when the ARL of
row 179 contains exactly the same number as Row 180?

I could not see the references with Red and Yellow cells you
mentioned. Must have got lost in the WWW.


The following is a rework and starts with a bit of a fudge. VBA gets
told that the Peak is the Opening High and the Trough is the Opening
Low.

The code then runs a merry course till Row 51 where the first instance
of above or below .0033 RELATIVE TO THE SP is reached. Then the real
process starts.

This seems to capture the highs and lows but they do not match your
version of events. Have a look at it and get back to me. I have put
data in Cols 7,8, and 9 to assist in showing what is going on in the
back ground. Have a look at this variance column (Col 9) to see what
I am talking about.


Take care

Marcus

Option Explicit

Sub AnewProcess1()

Dim InRow As Long
Dim ws As Worksheet
Dim EndRow As Long
Dim ARH As Long
Dim ARL As Long
Dim i As Long
Dim j As Long
Dim sp As Double
Dim Peak As Double
Dim Trough As Double
Set ws = ActiveSheet
EndRow = Range("A65536").End(xlUp).Row
InRow = 2 'First Row of data
ARH = 4
ARL = 5
i = 2
With ws
sp = .Cells(2, 3).Value
Peak = .Cells(2, 4).Value
Trough = .Cells(2, 5).Value

Do
i = i + 1
Loop Until .Cells(i, ARL) - sp <= -0.0033 Or .Cells(i, ARH) -
sp = 0.0033
'Catch the Peak or Trough before moving forward
For i = i To i
If .Cells(i, ARH) - sp = 0.0033 Then
Peak = .Cells(i, ARH)

ElseIf .Cells(i, ARL) - sp <= -0.0033 Then
Trough = .Cells(i, ARL)
End If
Next

For j = i To EndRow

If .Cells(j, ARH) Peak Then
Peak = .Cells(j, ARH)
.Cells(j, 7) = "Peak"
.Cells(j, 8) = Peak - Trough
End If

If .Cells(j, ARL) < Trough Then
Trough = .Cells(j, ARL)
.Cells(j, 7) = "Trough"
.Cells(j, 8) = Trough - Peak

End If

If Peak - Trough = 0.0033 Then
.Cells(j, 9) = Peak - Trough
End If

If Trough - Peak <= -0.0033 Then
.Cells(j, 9) = Trough - Peak
End If

Next

End With
End Sub