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

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: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=160004

Microsoft Office Help

joel;578841 Wrote:
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.


Hi Joel,



Thanks for responding to this request!

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


Please note how the groupings that I explained previously, in total
would comprise a total of 40 consecutive cells of data, while the first
five cells and the remaining 22 cells are consecutive in relation to the
target grouping of the 13 highest values originally searched on. The
order of steps below should paint a clearer picture/description of the
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 target
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 column
AB, simply because they represent the highest consecutive values in the
column list, while “4018” of Row #3286 in this group is found to be the
lowest value in this group of high values.

So the value “4018” would be copied into cell “W3”, and the address of
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 scenario
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 of
13 consecutive high values from “Step 1” and put its address into cell
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.



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



And thank you Joel, for your interest in helping me with this. Because
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: 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/sh...d.php?t=160004)

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


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

Microsoft Office Help

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


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

Microsoft Office Help

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

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 last set
of values should be.

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.


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

'where does this go?
TotalAverage = TotalSum / NumberofSums

End Sub


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

Microsoft Office Help

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Cecil" wrote in message
...
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

Don,

I've made the following changes in the code you suggested in order to
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 Sub
--------------------


Please correct me if I am wrong, but is the following line of code
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 the
overall function determines the length of the column data stream and I
think it found the lowest value in the groups that it searched, but it
does not reveal the address of the block to which it belongs. In the
data stream that I tried the macro on, there are 92 occurances. A bit of
in-lightenment may help make better use of this macro. I'll prepare a
worksheet that I can post, the actual worksheet is way too congested to
post. In the meantime, please examine the following explanation to
farther clarify my task.

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


Please note how the groupings that I explained previously, in total
would comprise a total of 40 consecutive cells of data, while the first
five cells and the remaining 22 cells are consecutive in relation to the
target grouping of the 13 highest values originally searched on. The
order of steps below should paint a clearer picture/description of the
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 target
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 column
AB, simply because they represent the highest consecutive values in the
column list, while “4018” in this group is found to be the lowest value
in this group of high values.

So the value “4018” would be copied into cell “W3”, and the address of
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 scenario
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 of
13 consecutive high values from “Step 1” and put its address into cell
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,


Don Guillett;579100 Wrote:
Modify to suit


Code:
--------------------

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


--------------------

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Cecil" wrote in message
...
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







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

Microsoft Office Help