Excel VBA Large and Small Functions

[Melpp]

I was wondering if there was any way for the Large and Small functions to also return the row number in which it found the largest and smallest values?

 

[Andrew Poulsom]

Like this?

=MATCH(MAX(A1:A10),A1:A10,FALSE)

 

[Jonmo1]

Not by itself..

You would use Max/Min in combination with MATCH to find the row number..

In a cell formula, it would be like this

=MATCH(MAX(A:A),A:A,0)


Important to note, that match technically is not returning the row #.
It's returning the position # within the array A:A.
When the array is A:A, or at least begins in row 1 like A1:A10, then yes it happens to be the same as the row #.
But if the array is say A15:A30, and the Max value is in say A20, then the Match returns 6 (A20 is the 6th position in A15:A30)

Hope that helps.

 

[Melpp]

I am using the following but I am getting an error. Anyone know how to fix this?

start_row_mod = WorksheetFunction.Match(Start, Range(Cells(Z, 5), Cells(j, 5)), 1)

 

[Melpp]

I also tried the following with no luck:

start_row_mod = WorksheetFunction.Match(start_row, Range("E" & Z & ":E" & j), 1)

 

[Jonmo1]

What error do you get?

And can you post all the relevant code?
The code that assignes values to each variable (Start, Z and j)