They say networking is the best method of getting a job. I told my son
to call twop good friend from college that we still keep in touch with.
I also went to my company website which has job all over the country.
There are some jobs that get listed internally before they go on the
public site. I on;y found one chemical enginerring job that was already
filled.

Option explicit only adds a requirement to declare all variables. I
added the variable declaration to the code below.

The 2015 error is caused when the evaluate function returns a bad
value. It is equivalent to the worksheet giving an error like N/A. I'm
not getting the error.

the coide I posted has an unline (line continuation character) which
you didn't post. It could be missing.

LocalAveragePeak = Evaluate("Average(abs(AB" & Rowcount & _
":AB" & (Rowcount + ComparePoints - 1) & "-" & LocalAverage & "))")


Since the code is almost exactly like the previous macro. This means
either The "ABS" isn't working or you are using different data that the
data I tested with. First try the data you posted to see if it gives
the same error. Next put on the worksheet the formula =abs(-5) to see
if the ABS is working. I would also change the VBA menu option Tools -
Option - General - Error Trapping - Break on All Errors. Another
possibility is the add-in on the worksheet in the menu Tools - Addins
check the following two items

Analysis Toolpak
Analysis Toolpak VBA

I'm working with 2003 and wouildn't expect any of this code to fail in
2007 especially since the only different between the 2nd and 3rd macro
is the ABS function.




Option Explicit
Sub GetHigh()

Const GroupSize = 13
Dim Average As Single
Dim DataRange As Range
Dim EndRow As Long
Dim FirstRow As Long
Dim FivePreviousRows As Range
Dim HighestAverage As Single
Dim LastRow As Long
Dim LowestAverage As Single
Dim MaxCount As Single
Dim Min As Single
Dim NumberofSums As Single
Dim Rowcount As Long
Dim StartRow As Long
Dim Total As Long
Dim TotalSum As Single
Dim TwentyTwoNextRows As Range


LastRow = Range("AB" & Rows.Count).End(xlUp).Row

MaxCount = 0
FirstRow = 0
LowestAverage = 0
HighestAverage = 0
TotalSum = 0
NumberofSums = 1
'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

TotalSum = TotalSum + Total
NumberofSums = NumberofSums + 1

Average = Evaluate("Average(AB" & Rowcount & ":AB" & (Rowcount +
GroupSize - 1) & ")")
If LowestAverage = 0 Then
LowestAverage = Average
Else
If Average < LowestAverage Then
LowestAverage = Average
End If
If Average HighestAverage Then
HighestAverage = Average
End If

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")

Range("T8") = LowestAverage
Range("T9") = HighestAverage



End Sub



Sub GetStartFromAverage()
Const ReferencePoints = 400
Const ComparePoints = 400
Const ThreshHold = 0.9 ' 90% of reference

Dim FivePreviousRows As Range
Dim LastRow As Long
Dim LocalReference As Single
Dim Reference As Single
Dim Rowcount As Long
Dim TripPoint As Single
Dim TripRange As Range
Dim TripRow As Long
Dim TwentyTwoNextRows As Range

LastRow = Range("AB" & Rows.Count).End(xlUp).Row

Reference = Evaluate("Average(AB" & (LastRow - ReferencePoints + 1) &
":AB" & LastRow & ")")

TripPoint = Reference * ThreshHold
'get ramp point
For Rowcount = (LastRow - ReferencePoints + 1) To 2 Step -1
LocalReference = Evaluate("Average(AB" & Rowcount & ":AB" & (Rowcount +
ComparePoints - 1) & ")")
If LocalReference <= TripPoint Then
TripRow = Rowcount
Exit For
End If

Next Rowcount

If TripRow = 0 Then
MsgBox ("Did not find Trip Point")
Exit Sub
End If

Set FivePreviousRows = Range("AB" & (TripRow - 5) & ":AB" & (TripRow -
1))

Range("T7") = FivePreviousRows.Address
Range("T8") = LocalReference

Set TripRange = Range("AB" & TripRow & ":AB" & (TripRow + 12))
Range("T9") = TripRange.Address


Set TwentyTwoNextRows = Range("AB" & (TripRow + 13) & ":AB" & (TripRow
+ 13 + 21))
Range("T10") = TwentyTwoNextRows.Address


'copy data
Range("AB" & (TripRow - 5) & ":AB" & (TripRow + 13 + 21)).Copy _
Destination:=Range("AK2")


End Sub



Sub GetStartFromAveragePeak()
Const ReferencePoints = 400
Const ComparePoints = 400
Const ThreshHold = 0.9 ' 90% of reference

Dim FivePreviousRows As Range
Dim LastRow As Long
Dim LocalAverage As Single
Dim LocalAveragePeak As Single
Dim LocalReference As Single
Dim Reference As Single
Dim ReferenceAveragePeak As Single
Dim Rowcount As Long
Dim TripPoint As Single
Dim TripRange As Range
Dim TripRow As Long
Dim TwentyTwoNextRows As Range

LastRow = Range("AB" & Rows.Count).End(xlUp).Row

Reference = Evaluate("Average(AB" & (LastRow - ReferencePoints + 1) &
":AB" & LastRow & ")")

ReferenceAveragePeak = Evaluate("Average(abs(AB" & (LastRow -
ReferencePoints + 1) & _
":AB" & LastRow & "-" & Reference & "))")


TripPoint = ReferenceAveragePeak * ThreshHold
'get ramp point
For Rowcount = (LastRow - ReferencePoints + 1) To 2 Step -1
LocalAverage = Evaluate("Average(AB" & Rowcount & ":AB" & (Rowcount +
ComparePoints - 1) & ")")

LocalAveragePeak = Evaluate("Average(abs(AB" & Rowcount & _
":AB" & (Rowcount + ComparePoints - 1) & "-" & LocalAverage & "))")

If LocalAveragePeak <= TripPoint Then
TripRow = Rowcount
Exit For
End If

Next Rowcount

If TripRow = 0 Then
MsgBox ("Did not find Trip Point")
Exit Sub
End If

Set FivePreviousRows = Range("AB" & (TripRow - 5) & ":AB" & (TripRow -
1))

Range("T7") = FivePreviousRows.Address
Range("T8") = LocalAveragePeak

Set TripRange = Range("AB" & TripRow & ":AB" & (TripRow + 12))
Range("T9") = TripRange.Address


Set TwentyTwoNextRows = Range("AB" & (TripRow + 13) & ":AB" & (TripRow
+ 13 + 21))
Range("T10") = TwentyTwoNextRows.Address


'copy data
Range("AB" & (TripRow - 5) & ":AB" & (TripRow + 13 + 21)).Copy _
Destination:=Range("AK2")


End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=160004

Microsoft Office Help