Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex adjacent row value finder - need help
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 -- Regards, Tom Ogilvy "kevinkr" wrote in message oups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is the range finder and how do you use it? | Excel Discussion (Misc queries) | |||
When data match, copy adjacent value to adjacent column | Excel Worksheet Functions | |||
Number Combination finder | Excel Worksheet Functions | |||
Totally Lost - Dates in huge table, Range finder | Excel Discussion (Misc queries) | |||
Rnage Finder | Excel Worksheet Functions |