Complex adjacent row value finder - need help

[kevinkr]

Hi,

I'm having difficulty finding the right solution for my problem, please
help if you can.

I have two adjacent columns of data such as:

0.0017 POS
0.0016 NEG
0.0015 NEG
0.0015 POS
0.0016 POS
0.0017 POS
0.0017 NEG
0.0016 NEG
0.0016 NEG
0.0016 NEG
0.0016 POS
0.0013 NEG
0.0011 NEG
0.0010 NEG
0.0009 NEG
0.0009 NEG
0.0008 NEG
0.0007 NEG
0.0007 POS

The second column is basically and indicator of the rate of change of
the value s of the first colum. Within each column of same values, I
want to select the the highest or lowest value in that group of cells.
These are ultimately derived from date-based data, so the calculations
happen sequentially as one progress down the data.

So, for example, I have this data:
A1: 0.0017 POS
A2: 0.0016 NEG
A3: 0.0015 NEG
A4: 0.0015 POS
A5: 0.0016 POS
A6: 0.0017 POS
A7: 0.0015 (I look at the previous rate of change value - in this case,
POS - then I want to find the range of previous adjacent cells with the
same value. When I have the range, I want to select the highest or
lowest value from the adjacent column within that range. In this
example, A4-A6 would be selected and the highest value would be 0.0017.

My challenge is how to dynamically look in adjacent rows that only have
the same value. I need to do this all programmatically, not manually
with selections by hand.

Any thoughts on direction to take? Thanks in advance for your think
time on this.

-Kevin

 

[Tom Ogilvy]

Sub WriteMin_Max()
Dim lasrow as Long, lngFirst as Long, i as Long
lastrow = cells(rows.count,1).End(xlup).row
lngFirst = 1
for i = 3 to lastrow
if cells(i-1,2) = cells(i-2,2) then
set rng = Range(cells(lngFirst,1),Cells(i-1,1))
cells(i,3).Value = Application.Max(rng)
cells(i,4).Value = application.Min(rng)
else
lngFirst = i-1
end if
Next
End Sub