If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
getting the number of the row with the maximum value
i have a a column with values, by using a macro i want to get the number of
the row in which the maximum value of the column is. |
#2
|
|||
|
|||
Are you sure you want to do it by macro?
First you'd use the MAX() function to find the largest value. Now, if you want to do this by macro (presumably to SELECT the cell containing the largest value), you'd use the Find command. I'm assuming your values are in a range named 'rngValues' Dim r As Range Set r = Range("rngValues") r.Find(WorksheetFunction.Max(r)).Select If you don't need a macro, then you'd use the MATCH() function to find the relative position of that MAX value in the list. =MATCH(MAX(rngValues),rngValues) If your data starts in any row but the first, you'll have to convert the relative position to a row #. Do this by adding a value to the MATCH() equal to the number of rows down the sheet your range starts. Thus, if your data starts in row 2, then the data starts one row from the top and the formula needs to be =MATCH(MAX(rngValues),rngValues)+1 Duke "hilbert" wrote: i have a a column with values, by using a macro i want to get the number of the row in which the maximum value of the column is. |
#3
|
|||
|
|||
=ROW(INDEX(A1:A100,MATCH(MAX(A1:A100),A1:A100,0)))
|
#4
|
|||
|
|||
hilbert,
Dim myRng As Range Set myRng = Range("A:A") With Application.WorksheetFunction MsgBox .Match(.Max(myRng), myRng, False) End With or with a formula: =MATCH(MAX(A:A),A:A,FALSE) HTH, Bernie MS Excel MVP "hilbert" wrote in message ... i have a a column with values, by using a macro i want to get the number of the row in which the maximum value of the column is. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Maximum Number of Cell Formats | Jim Allen | General Discussion | 4 | April 19th, 2005 07:07 PM |
Combo Box (1st) Populating Text Box (2nd) Field | AccessRookie | Using Forms | 1 | April 6th, 2005 11:37 PM |
Combo Box & Text Box | AccessRookie | Using Forms | 3 | April 6th, 2005 11:33 PM |
Maximum number of controls | ARG | Using Forms | 7 | January 30th, 2005 10:00 PM |
Maximum Number of E-Mail Addresses | LPS | General Discussion | 1 | January 21st, 2005 05:10 PM |