Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max/Min for variable length columns
I need to find the max value in a column of unknown length, (start point at
about row 9). Then place that value in same column in next (blank) row. Then do the same for MIN (but now it is column length -1) This should be easy, but its been beating me all day. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max/Min for variable length columns
Tim,
I assume that you are able to select the first empty cell. Enter this formula =MAX(INDIRECT("A9:A"&ROW()-1)) and then enter =MIN(INDIRECT("A9:A"&ROW()-1)) in the next row. The column length is irrelevant in this case as the new number is the MAX so it will not affect the MIN formula. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Tim Rush" <Tim wrote in message ... I need to find the max value in a column of unknown length, (start point at about row 9). Then place that value in same column in next (blank) row. Then do the same for MIN (but now it is column length -1) This should be easy, but its been beating me all day. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max/Min for variable length columns
Sub maxMinValues()
Dim i As Long, bottomRow As Long Dim maxValue As Double, minValue As Double bottomRow = Cells(65536, Selection.Column).End(xlUp).Row maxValue = Cells(9, Selection.Column).Value For i = 9 To bottomRow If Cells(i, Selection.Column).Value maxValue Then maxValue = Cells(i, Selection.Column).Value End If Next i Cells(i, Selection.Column).Value = maxValue Cells(i, Selection.Column - 1).Value = "Maximum Value" minValue = Cells(9, Selection.Column).Value For i = 9 To bottomRow If Cells(i, Selection.Column).Value < minValue Then minValue = Cells(i, Selection.Column).Value End If Next i Cells(i + 1, 4).Value = minValue Cells(i + 1, 3).Value = "Minimum Value" End Sub Select a cell in the relevant column and then run. -- Gordon Rainsford London UK "Tim Rush" <Tim wrote: I need to find the max value in a column of unknown length, (start point at about row 9). Then place that value in same column in next (blank) row. Then do the same for MIN (but now it is column length -1) This should be easy, but its been beating me all day. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max/Min for variable length columns
Ok, tried that, get a 'Compile error' (Did I mention I was doing this in VBA?
and Office 2002?) Its not recognizing 'Row'. I entered my line as: High = Max(INDIRECT("B9:B" & Row() - 1)) Tim "Bob Phillips" wrote: Tim, I assume that you are able to select the first empty cell. Enter this formula =MAX(INDIRECT("A9:A"&ROW()-1)) and then enter =MIN(INDIRECT("A9:A"&ROW()-1)) in the next row. The column length is irrelevant in this case as the new number is the MAX so it will not affect the MIN formula. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Tim Rush" <Tim wrote in message ... I need to find the max value in a column of unknown length, (start point at about row 9). Then place that value in same column in next (blank) row. Then do the same for MIN (but now it is column length -1) This should be easy, but its been beating me all day. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max/Min for variable length columns
Gordon Rainsford wrote:
Cells(i + 1, 4).Value = minValue Cells(i + 1, 3).Value = "Minimum Value" These final two lines should read: Cells(i + 1, Selection.Column).Value = minValue Cells(i + 1, Selection.Column - 1).Value = "Minimum Value" -- Gordon Rainsford London UK |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max/Min for variable length columns
That did it... It make sense too (go figure), however, I did nodify the last
2 lines to Cells(i + 1, Selection.Column).Value = minValue which keeps it in the same column. "Gordon Rainsford" wrote: Sub maxMinValues() Dim i As Long, bottomRow As Long Dim maxValue As Double, minValue As Double bottomRow = Cells(65536, Selection.Column).End(xlUp).Row maxValue = Cells(9, Selection.Column).Value For i = 9 To bottomRow If Cells(i, Selection.Column).Value maxValue Then maxValue = Cells(i, Selection.Column).Value End If Next i Cells(i, Selection.Column).Value = maxValue Cells(i, Selection.Column - 1).Value = "Maximum Value" minValue = Cells(9, Selection.Column).Value For i = 9 To bottomRow If Cells(i, Selection.Column).Value < minValue Then minValue = Cells(i, Selection.Column).Value End If Next i Cells(i + 1, 4).Value = minValue Cells(i + 1, 3).Value = "Minimum Value" End Sub Select a cell in the relevant column and then run. -- Gordon Rainsford London UK "Tim Rush" <Tim wrote: I need to find the max value in a column of unknown length, (start point at about row 9). Then place that value in same column in next (blank) row. Then do the same for MIN (but now it is column length -1) This should be easy, but its been beating me all day. Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max/Min for variable length columns
Got it, Thanks...
"Gordon Rainsford" wrote: Gordon Rainsford wrote: Cells(i + 1, 4).Value = minValue Cells(i + 1, 3).Value = "Minimum Value" These final two lines should read: Cells(i + 1, Selection.Column).Value = minValue Cells(i + 1, Selection.Column - 1).Value = "Minimum Value" -- Gordon Rainsford London UK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Length Columns | Excel Discussion (Misc queries) | |||
Return a Variable value length | Excel Worksheet Functions | |||
Graphing Variable-length columns | Charts and Charting in Excel | |||
Referencing a Value in variable length columns. | Excel Worksheet Functions | |||
Data from multiple variable length columns to one column | Excel Programming |