Is this Multi-Search Macro Possible?

[Cecil]

Hi All,

If it is not the right place for my question, please point me to the correct
newsgroup.

I have a perplexing problem building a macro in excel that I hope someone
can help me solve as follows;

I have a stream of data values in column AB2:AB14516.
And what I would like to do, is to find lowest value in the last highest
group of 13 consecutive values, rather than the first group using Excel VBA.
Then put that value into cell W3 and the address of that group's range into
cell W4.
In addition, Identify the address of the five cells ahead of that group that
was found and put that address into cell W2.
And finally, identify the address of the 22 cells after the group of 13
consecutive high values and put its address into cell W5.

Any help at this point would be greatly appreciated!
Thanks in advance,
CTown

 

[joel]

Yes it can be done and this is the right place. But I need to see som
data because your instructions can be interpreted in more than 1 way.
Either post some sample data in your posting or go to TheCodeCage.Co
and attach the file to the posting. I'm not sure which website yo
generated you request but there are a few websites that simulataneousl
posts the same requests. The CodeCage allows files to be attached t
the request

 

[Don Guillett]

Modify to suit

Sub findlowestinblock()
MC = 1' col A
On Error Resume Next
For i = Cells(Rows.Count, MC).End(xlUp).Row To 1 Step -1
mr = Cells(i - 3, MC).Resize(4)
If Application.CountA(mr) = 4 Then
'MsgBox i
'MsgBox Application.Min(mr)
Cells(2, MC + 4) = Application.Min(mr)
Exit For
End If
Next i
End Sub

 

[c-town]

Yes it can be done and this is the right place. But I need to see som
data because your instructions can be interpreted in more than 1 way.
Either post some sample data in your posting or go to TheCodeCage.Co
and attach the file to the posting. I'm not sure which website yo
generated you request but there are a few websites that simulataneousl
posts the same requests. The CodeCage allows files to be attached t
the request.

Hi Joel,



Thanks for responding to this request!

The following is a sample of 40 consecutive cells from the column o
data that I described in which I want to capture the cell addresses of


Please note how the groupings that I explained previously, in tota
would comprise a total of 40 consecutive cells of data, while the firs
five cells and the remaining 22 cells are consecutive in relation to th
target grouping of the 13 highest values originally searched on. Th
order of steps below should paint a clearer picture/description of th
task.



For example;

Found some where in

Column AB

Row Count.) Row# Values



Step 2 Identify the address of the five cells ahead of that targe
group that was found in “step 1” and put that address into cell W2
which would be “$AB$3270:$AB$3274”.



1.) 3270 484

2.) 3271 912.4

3.) 3272 2884.8

4.) 3273 2793.2

5.) 3274 4745.6



Step 1 The macro would first locate the following 13 values in colum
AB, simply because they represent the highest consecutive values in th
column list, while “4018” of Row #3286 in this group is found to be th
lowest value in this group of high values.

So the value “4018” would be copied into cell “W3”, and the address o
that group's range “$AB$3275:$AB$3287”, would be stored into cell W4
This first step is critical because it represents a worst case scenari
to be acted on when the macro completes its process.



6.) 3275 5773

7.) 3276 5310

8.) 3277 5137

9.) 3278 4982

10.) 3279 4828

11.) 3280 4635

12.) 3281 4500

13.) 3282 4249

14.) 3283 4307

15.) 3284 4211

16.) 3285 4172

17.) 3286 4018

18.) 3287 4963



Step 3 Identify the address of the 22 cells after the target group o
13 consecutive high values from “Step 1” and put its address into cel
W5, which in this case would be “$AB$3288:$AB$3309.



19.) 3288 2688.15

20.) 3289 2497.3

21.) 3290 415.45

22.) 3291 70.6

23.) 3292 -1021

24.) 3293 -790

25.) 3294 -655

26.) 3295 -539

27.) 3296 -230

28.) 3297 -326

29.) 3298 59

30.) 3299 117

31.) 3300 156

32.) 3301 291

33.) 3302 253

34.) 3303 310

35.) 3304 387

36.) 3305 445

37.) 3306 368

38.) 3307 503

39.) 3308 484

40.) 3309 388



Step 4 Finally, copy the values in the three groups of addresse
beginning with the starting address from cell W3, which would b
“$AB$3270” and the ending address from cell W5, which in this case woul
be $AB$3309”, so the full range of 40 cell values can be copied int
Range(“$F$4:$F$43”), completing the macro process.



If it would be more helpful to have a sample workbook with an actua
data stream to practice with, let me know and I'll have to post it as a
attachment on “TheCodeCage.Com” as you stated?



And thank you Joel, for your interest in helping me with this. Becaus
I don't mind saying it has been one major hair puller by the hand full
for me.



Cecil,

----- Original Message -----
From: joel
Newsgroups: microsoft.public.excel.programming
Sent: Sunday, December 06, 2009 6:35 AM
Subject: Re: Is this Multi-Search Macro Possible?



Yes it can be done and this is the right place. But I need to see
some
data because your instructions can be interpreted in more than 1 way.
Either post some sample data in your posting or go to TheCodeCage.Com
and attach the file to the posting. I'm not sure which website you
generated you request but there are a few websites that
simulataneously
posts the same requests. The CodeCage allows files to be attached to
the request.


--
joel
------------------------------------------------------------------------
joel's Profile: 'The Code Cage Forums - View Profile: joel'
(http://www.thecodecage.com/forumz/member.php?userid=229)
View this thread: 'Is this Multi-Search Macro Possible? - The Code Cage
Forums' (http://www.thecodecage.com/forumz/showthread.php?t=160004)

'Microsoft Office Help' ("http://www.thecodecage.com&quot

 

[c-town]

Don,

I've made the following changes in the code you suggested in order t
better understand it.


Code
-------------------
Sub findlowestinblock()
'MC = 1 ' col A
MC = 28 ' col AB
Worksheets("Sheet1").Activate
On Error Resume Next
For i = Cells(Rows.Count, MC).End(xlUp).Row To 1 Step -1
mr = Cells(i - 3, MC).Resize(4)
If Application.CountA(mr) = 4 Then
MsgBox "Row Count = " & i
Range("T4").value = i
MsgBox "Highest Group Minimum Value = " & Application.Min(mr)
Range("T2").value = mr
Cells(2, MC + 4) = Application.Min(mr)
Exit For
End If
Next i
End Su
-------------------


Please correct me if I am wrong, but is the following line of cod
seeking only a group of four values?


Code
-------------------
mr = Cells(i - 3, MC).Resize(4
-------------------


I was able to figure out with the use of the message boxes that th
overall function determines the length of the column data stream and
think it found the lowest value in the groups that it searched, but i
does not reveal the address of the block to which it belongs. In th
data stream that I tried the macro on, there are 92 occurances. A bit o
in-lightenment may help make better use of this macro. I'll prepare
worksheet that I can post, the actual worksheet is way too congested t
post. In the meantime, please examine the following explanation t
farther clarify my task.

The following is a sample of 40 consecutive cells from the column o
data that I described in which I want to capture the cell addresses of


Please note how the groupings that I explained previously, in tota
would comprise a total of 40 consecutive cells of data, while the firs
five cells and the remaining 22 cells are consecutive in relation to th
target grouping of the 13 highest values originally searched on. Th
order of steps below should paint a clearer picture/description of th
task.



For example;

Found some where in

Column AB

Row Count.) Row# Values



Step 2 Identify the address of the five cells ahead of that targe
group that was found in “step 1” and put that address into cell W2
which would be “$AB$3270:$AB$3274”.



1.) 3270 484

2.) 3271 912.4

3.) 3272 2884.8

4.) 3273 2793.2

5.) 3274 4745.6



Step 1 The macro would first locate the following 13 values in colum
AB, simply because they represent the highest consecutive values in th
column list, while “4018” in this group is found to be the lowest valu
in this group of high values.

So the value “4018” would be copied into cell “W3”, and the address o
that group's range “$AB$3275:$AB$3287”, would be stored into cell W4
This first step is critical because it represents a worst case scenari
to be acted on when the macro completes its process.



6.) 3275 5773

7.) 3276 5310

8.) 3277 5137

9.) 3278 4982

10.) 3279 4828

11.) 3280 4635

12.) 3281 4500

13.) 3282 4249

14.) 3283 4307

15.) 3284 4211

16.) 3285 4172

17.) 3286 4018

18.) 3287 4963



Step 3 Identify the address of the 22 cells after the target group o
13 consecutive high values from “Step 1” and put its address into cel
W5, which in this case would be “$AB$3288:$AB$3309.



19.) 3288 2688.15

20.) 3289 2497.3

21.) 3290 415.45

22.) 3291 70.6

23.) 3292 -1021

24.) 3293 -790

25.) 3294 -655

26.) 3295 -539

27.) 3296 -230

28.) 3297 -326

29.) 3298 59

30.) 3299 117

31.) 3300 156

32.) 3301 291

33.) 3302 253

34.) 3303 310

35.) 3304 387

36.) 3305 445

37.) 3306 368

38.) 3307 503

39.) 3308 484

40.) 3309 388



Step 4 Finally, copy the values in the three groups of addresses
beginning with the starting address from cell W3, which would be
“$AB$3270” and the ending address from cell W5, which in this case would
be $AB$3309”, so the full range of 40 cell values can be copied into
Range(“$F$4:$F$43”), completing the macro process.



And thank you for your interest in helping me with this.

CTown,

 

[joel]

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 th
values
For RowCount = 2 To (LastRow - GroupSize + 1)
Total = Evaluate("Sum(AB" & RowCount & ":AB" & (RowCount + GroupSiz
- 1) & ")")
If Total > MaxCount Then
FirstRow = RowCount
MaxCount = Total
End If
Next RowCount

Set FivePreviousRows = Range("AB" & (FirstRow - 5) & ":AB" & (FirstRo
- 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 Su

 

[c-town]

Hey Joel,

You are the Man!!! That macro accomplished exactly what I've bee
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 performe
exactly as intended.
But it also revealed something that I had overlooked before now that i
missing.

With the current capabilities that this macro provides, I can no
target that worst case scenario, but what I would like to know is woul
you mind modifying the macro to capture the last possible consecutiv
values that meet the same criteria, except the 13 high values are a
average of all the high values?

This way I'll have a norm to compare with the worst case scenario in m
chart graph. I noticed how direct your code addressed each step a
requested, so I'll bet changing the macro to capture the average i
something simple that is still beyond my VBA abilities. This is my fina
request on this task if you don't mind helping me with it. I would gues
that both processes could be combined, but I would prefer to run the
separately as needed!

If you are willing to help me with this, the cells to store th
addresses of the second macro for the average high consecutive value
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 i
"AK2:AK41".

As a long shot guess, would changing the following line of your code b
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]

I made some changes to the code below. I don't know where you want t
put the average of all the totals. I also don't know what the last se
of values should be.

From your last instructions you said you ran the macro on differen
data streams. I think you want to keep a worse case of the differen
data streams but not sure. You could also means that the min and ma
are refering to a single data stream.


Sub GetHigh()

Const GroupSize = 13

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 th
values
For RowCount = 2 To (LastRow - GroupSize + 1)
Total = Evaluate("Sum(AB" & RowCount & ":AB" & (RowCount + GroupSiz
- 1) & ")")
If Total > MaxCount Then
FirstRow = RowCount
MaxCount = Total
End If

TotalSum = TotalSum + Total
NumberofSums = NumberofSums + 1

Average = Evaluate("Avg(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" & (FirstRo
- 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

'where does this go?
TotalAverage = TotalSum / NumberofSums

End Su

 

[c-town]

I made some changes to the code below. I don't know where you want t
put the average of all the totals. I also don't know what the last se
of values should be.

From your last instructions you said you ran the macro on differen
data streams. I think you want to keep a worse case of the differen
data streams but not sure. You could also means that the min and ma
are refering to a single data stream.

The evaluate method refers to the worksheet function names the AV
should be Average.


Sub GetHigh()

Const GroupSize = 13

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 th
values
For RowCount = 2 To (LastRow - GroupSize + 1)
Total = Evaluate("Sum(AB" & RowCount & ":AB" & (RowCount + GroupSiz
- 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" & (FirstRo
- 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

'where does this go?
TotalAverage = TotalSum / NumberofSums

End Sub

Joel,

You stated;

I made some changes to the code below. I don't know where you want to
put the average of all the totals. I also don't know what the las
set
of values should be.

For a bit more clarity I will briefly say this, that the total 40 cell
of values that I used in my previous example represent a pressure curv
of 40 data points per second and when displayed in a line chart, revea
a pressure rise (the first 5 data points, a peak (the 13 highest dat
points), a decay (of 3 data points, and finally a low between eac
pressure curve (of 19 data points), hence the decay and low value
comprised the 22 consecutive values that followed the targeted 13 hig
values. I hope I haven’t muddy the water, but I’ll bet that you get th
picture a little clearer now.

So, in answer to your question of > “where to put the average of all the totals?” the search results value would be save into the cell location a
follows;

The cells to store the addresses of the second macro for the averag
high consecutive values;

Preceding 5 cell Group Range Address in T7
Lowest 1 of the Average Highest 13 Value in T8
Average Highest 13 group Range Address in T9
Trailing 22 Cell Group Range Address in T10

And the range to copy the full consecutive 40 cell values into i
"AK2:AK41".

In answer to your next question of > “I also don't know what the last set

of values should be.”.

That is understandable, because I worded that part of my message to yo
pretty poorly; as I was think aloud to myself when I wrote it. Pleas
excuse!
Just understand that when the data stream is generated, the targeted 1
high values appear to always be generated earlier in the column data an
then begins to plateau down to a lower set of high values. This is why I
need an average set of high values to compare. So please disregard my
remark about seeking the last set, seeking the average of high values is
a far better approach to achieving my goal during the corrective action
calculations that are ready and waiting for these search results.

You Asked;

From your last instructions you said you ran the macro on different
data streams. I think you want to keep a worse case of the different
data streams but not sure. You could also means that the min and max
are refering to a single data stream.

Yes, I loaded previously saved data streams one at a time into the same
source data column beginning at ”AB2” to examine the performance of the
macro search results. When the worst case is determined, another macro
attached to a button on the worksheet is run to save that data to a
different location to calculate corrective actions. So yes, in that
respect the reference is always to a single data stream intended for the
search task of both macros. Note, that the search result from each macro
will be saved to separate locations as described. In addition, both
macros must remain as separate utilities.

The main purpose of having a second macro to locate the average 13 high
values is to better establish a low end boundary in comparison to the
previous maximum 13 high values during the corrective action
calculation, but the first macro may not be needed in every examination.
My thinking is that the second macro we're working on can target the
average 13 high values group, relative to all of the groups of high/peak
values and capture that set of 40 values. The completion of this second
macro will finally resolve this problem I’ve been working on for months.
:

I am attaching a text file to this message of an actual data stream you
can use to help in testing your macro.

So thank you again Joel for sticking with me on this!

Cecil,


+-------------------------------------------------------------------+
|Filename: DataStream.txt |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=382|
+-------------------------------------------------------------------+

 

[joel]

1) I have a BSEE and a MSCS with over 30 year off on-job experience.
2) My son is a Chemical Engineer
3) My roomate at school was also a chemical Engineer and his favorit
expression was "Thermal dynamics Sucks!".


You have Sinusoidal data and I'm not sure what is the importan
parameters are that you are looking for.

a) You can take the absolute avarage of all the points or the RM
value of the points
If you save a true sine wave thenb the RMS is .707 of the peak and th
average is approximately .63 of peak. You can get harmonic distortio
which is how close the signal (pressure measurement) is to a true sin
wave.
b) You can get the Peak-to-Peak vaue of the data
c) You can get the minimum and maximum



I looked at the data closely and see the the data eventually ramps u
and becomes a true sinusoidal data. I think you want to get th
peak-to-peak value by calculating the average. I would look at the dat
backwards instead of foward to find the point where the average valu
drops to something like 90% to find the transition point. If yo
average the last 100 to 200 points and use this average as a referenc
then move backwards until the average drops to 90% of the reference yo
can find where the data transitions

 

[c-town]

1) I have a BSEE and a MSCS with over 30 year off on-job experience.
2) My son is a Chemical Engineer
3) My roomate at school was also a chemical Engineer and his favorite
expression was "Thermal dynamics Sucks!".


You have Sinusoidal data and I'm not sure what is the important
parameters are that you are looking for.

a) You can take the absolute avarage of all the points or the RMS
value of the points
If you save a true sine wave thenb the RMS is .707 of the peak and the
average is approximately .63 of peak. You can get harmonic distortion
which is how close the signal (pressure measurement) is to a true sine
wave.
b) You can get the Peak-to-Peak vaue of the data
c) You can get the minimum and maximum



I looked at the data closely and see the the data eventually ramps up
and becomes a true sinusoidal data. I think you want to get the
peak-to-peak value by calculating the average. I would look at the data
backwards instead of foward to find the point where the average value
drops to something like 90% to find the transition point. If you
average the last 100 to 200 points and use this average as a reference
then move backwards until the average drops to 90% of the reference you
can find where the data transitions.
--- AUTOMERGED CONSECUTIVE POST BEFORE RESPONSE ---
I was think some more and think you really want the average peak value.
You should get the average of all the points once the data ramps up.
Then compute the the absolute peak by using the formula

X is all the points after the data ramp up
x(i) is each individual data point

Offset = Average(X) after data ramps up

Average Peak = Average(abs(x(i)-Offset)) after data ramps up

Joel,

Thanks for sharing a bit of your background. This is great news and I
like your roommate already.
Heaven must be smiling upon us both, because your suggestion > “b) You can get the Peak-to-Peak vaue of the data” as you stated;

“I would look at the data backwards instead of foward to find the point
where the average value drops to something like 90% to find the
transition point. If you average the last 100 to 200 points and use
this average as a reference then move backwards until the average drops
to 90% of the reference you can find where the data transitions.”

Yes, this is precisely the direction I’d like the macro to take. And
because one complete pressure curve is 40 points of data, I would like
to expand the average of the last data points to maybe 400.

In your "Automerged Post" you also said;

You should get the average of all the points once the data ramps up.
Then compute the the absolute peak by using the formula

X is all the points after the data ramp up
x(i) is each individual data point

Offset = Average(X) after data ramps up

Average Peak = Average(abs(x(i)-Offset)) after data ramps up

If I understand your suggestion correctly, this would allow the macro
to focus on only the average peaks after the data has ramped up! That is
ultimately exactly what is needed. Many of the other data streams I've
examined have very low values prior to the ramp up, some times negative
values and often for 400 or more data points before the ramp even
begins.

So I wanted to figure away in VBA to disregard as much of that as
possible.
Unfortunately, my experience in VBA is too limited, so as you can
imagine I learn a tremendous amount about the power of VBA and how it
can automate Excel, every time I seek help with problems such as this
one. So Joel, please know that your time, experience and generosity are
greatly appreciated, I hope to be in your position someday soon, helping
others this way!

Thank you so much for your help and insight, and I look forward to
trying out the macro you suggest so that others can get the meaningful
assistance you’ve shared with me!

Kind Regards,
Cecil

 

[joel]

I have 3 macros below. I removed some of the newer code from the 1s
macro. The I create two macros to find the threshhold. Modify a
necessary. The first uses just the average to get the trip point. Th
2nd uses the average peak to determine the trip point.

My son got laid off from Honeywell yesterday. Do you know of an
contacts for an eager your chemical engineer?


Sub GetHigh()

Const GroupSize = 13

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 th
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" & (FirstRo
- 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

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" & (TripRo
+ 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

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" & (RowCoun
+ 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

 

[c-town]

Joel,

Although “Option Explicit” is not in the module you sent,
the third macro “GetStartFromAveragePeak “ is raising errors such a
“Type mismatch (13)” at the following line of code;

“TripPoint = ReferenceAveragePeak * ThreshHold” = <type mismatch>

and

“ReferenceAveragePeak” from the previous line;

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

is getting “ReferenceAveragePeak = Error 2015”, can you help me hea
this off and other possible errors with the “Option Explicit” statemen
in place for the whole moldule? This is a big weakness in my VB
abilities is knowing what variable or object is the correct intent.

You also stated;

My son got laid off from Honeywell yesterday. Do you know of any
contacts for an eager your chemical engineer?

I will have to make some inquiries when I return to work next week.
I appologize for the lengthy delays in communication with you. I wor
evening shift on very demanding projects and I tend to bring my wor
home with me. Not a good habit, I know!

If I believe I have a suggestion that may be fruitful for him, it woul
not be advisable to post it in a public forum such as this becaus
competition for such positions these days are very high. Therefore,
will request a more private means of communication from you at tha
point.
HTH

Thank you for help any suggestions you may have.

Cecil,

 

[joel]

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