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