Struggling with VBA...

[Alex]

I am new to VBA so knowledge is low.

I have a list of 10 numbers in range A1:A10. I want to
write VBA code so that if I select that range I get a
value returned (say in cell B1) that tells me the number
of rows beteeen the highest and lowest value. Thats it!

You can use Application.WorksheetFunction.Max(Selection)
to get the max value and do the same for the minimum
value.

From here I struggle. I can get VBA to recognise the max
and min values ( I used Debug.Print to see that I got the
correct values). But I need VBA to use the cell
references of the max and min to compute the row
difference. I can't see how to do this.

I have just spent 1.5 hours trying to work this out (on
top of 2.5 hours yesterday) so I think I now need help.

Many thanks to those who can offer help....

 

[Dick Kusleika]

Alex

Try this formula

=ROW(INDEX(A1:A7,MATCH(MAX(A1:A7),A1:A7,0)))-ROW(INDEX(A1:A7,MATCH(MIN(A1:A7
),A1:A7,0)))

Where A1:A7 is the range in question. See also here

http://www.dicks-blog.com/excel/2004/05/address_of_matc.html

 

[LarryP]

Unclear. If A1:A10 contains sorted discrete values, the
rows between is always going to be 10 (or 9, or 8,
depending on what you mean by "rows between," but you get
the idea).

If A1:A10 is NOT sorted, however, "rows between" could be
anything between 0 and 10.

And if A1:A10 is a dynamic range that could shrink or
grow, another layer of complexity is added.

And if there could be multiple occurrences of your Max
and/or Min values, another complication.

Want to restate your problem? As it is, I expect you'll
get lots of answers that may or may not meet your needs.