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