Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the number from given conditon?
Does anyone have any suggestions on how to determine the number under
following conditions? There is a list of number under column A & B, and I would like to determine the last values based on following conditions. If any number under column A is smaller than the previous one from bottom to top direction, then this is the starting point, such as row 5 is the starting point, because in cell A6 is 6 and in cell A5 is 2. The other starting point will be in cell A10 and cell A15. Once the starting point is determine, then I would like to determine the last value from starting point to the top as long as the number in column A is larger than previous one, such as in cell A10, the last number column B from row 10 to row 6 is 114.74 in cell C10. The reason to select the number up to row 6 is that the number in cell A6 is larger than the number in cell A5, therefore, this define as the ending point. Therefore, I would like to determine the last number within the range from starting point to ending point. Another example will be from row 15 to row 11, the last number within this range is 114.01 in cell C15, and another example will be from row 5 to row 1, the last number within this range is 110.9 in cell C5. row 1 6 110.9 row 2 5 112.66 row 3 4 113.29 row 4 3 114.51 row 5 2 114.54 row 6 6 114.74 row 7 5 114.87 row 8 4 115.27 row 9 3 114.72 row 10 2 114.69 row 11 6 114.01 row 12 5 114.09 row 13 4 113.94 row 14 3 114.65 row 15 2 114.07 row 16 6 115.15 Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the number from given conditon?
Looks like your thoughts could be simply put as every time the number
switches to one greater, take the corresponding value in column b, is this not correct? Try this, got the same results you gave Sub main() Dim resultNum As Double Dim lastRow As Long lastRow = Sheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row resultNum = Sheets("sheet1").Cells(1, 2) MsgBox resultNum For i = 1 To lastRow If Sheets("sheet1").Cells(i + 1, 1) < "" And _ Sheets("sheet1").Cells(i, 1) < Sheets("sheet1").Cells(i + 1, 1) Then resultNum = Sheets("sheet1").Cells(i + 1, 2) MsgBox resultNum End If Next End Sub -- -John Please rate when your question is answered to help us and others know what is helpful. "Eric" wrote: Does anyone have any suggestions on how to determine the number under following conditions? There is a list of number under column A & B, and I would like to determine the last values based on following conditions. If any number under column A is smaller than the previous one from bottom to top direction, then this is the starting point, such as row 5 is the starting point, because in cell A6 is 6 and in cell A5 is 2. The other starting point will be in cell A10 and cell A15. Once the starting point is determine, then I would like to determine the last value from starting point to the top as long as the number in column A is larger than previous one, such as in cell A10, the last number column B from row 10 to row 6 is 114.74 in cell C10. The reason to select the number up to row 6 is that the number in cell A6 is larger than the number in cell A5, therefore, this define as the ending point. Therefore, I would like to determine the last number within the range from starting point to ending point. Another example will be from row 15 to row 11, the last number within this range is 114.01 in cell C15, and another example will be from row 5 to row 1, the last number within this range is 110.9 in cell C5. row 1 6 110.9 row 2 5 112.66 row 3 4 113.29 row 4 3 114.51 row 5 2 114.54 row 6 6 114.74 row 7 5 114.87 row 8 4 115.27 row 9 3 114.72 row 10 2 114.69 row 11 6 114.01 row 12 5 114.09 row 13 4 113.94 row 14 3 114.65 row 15 2 114.07 row 16 6 115.15 Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to determine the number from given condition? | Excel Discussion (Misc queries) | |||
Determine Row number | Excel Discussion (Misc queries) | |||
How to determine the cell contain number only? | Excel Discussion (Misc queries) | |||
How to determine the number in excel? | Excel Discussion (Misc queries) | |||
How to determine the number of units? | Excel Discussion (Misc queries) |