Declaring a range from a Variable

[RTR97]

I have an initial range, for that range I have code that will find the maximum value and return it. The problem I am having is that I would like to use this max value to start a new range. Since the max value is returned via a variable how do I use that variable when declaring the new range?

The code below works and finds the Maximum number in the range, however once the Maximum value is located, I want to use that number to start another range.

Please Login or Register  to view this content.

This is the function that returns the MAX values cell address, I need this to basically check to see if the above Max function is returning the correct cell.

Please Login or Register  to view this content.

This is my attempt to retrieve the next Maximum value using "new" range with the variable included, however I am failing miserably at it.
This is where I go wrong

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Bob Phillips]

Please Login or Register  to view this content.

[RTR97]

Bob,

The above code provided, essentially provides me with the same information as :

Please Login or Register  to view this content.

Which is great and more efficient than my code, however once this returns the cell address of the Maximum number in the range, I then want use that Maximum value's cell address that is returned as my new starting point for another range, would you happen to know how to do this? Thank you for your response.

Thomas

[Bob Phillips]

Thomas, if you look closely at my code you will see it actually returns a range variable not a string variable. I just took the Address property of that variable so as to show something. So I have already done what you want.

[RTR97]

Understood, please forgive me I am a novice.

Ok, with that said, can I now take "MaxAdr" and insert that into a statement to declare that as the first cell in my new range? For example:

Please Login or Register  to view this content.

What I am trying to accomplish is first to find the maximum value(which we have done), then use that as a starting point for another range to find the minimum value in the new range. Thanks in advance.

Thomas

[Bob Phillips]

Using my MaxCell Function, you would use

Please Login or Register  to view this content.

[RTR97]

PERFECT! Thanks Bob for all of your help, I noticed that I had the right idea, however my understanding is a bit more narrow than I would like, but your code works perfectly. Again thanks!

Thomas

[Bob Phillips]

BTW< to be safe you should best qualify all ranges

Please Login or Register  to view this content.

[RTR97]

Brilliant, I assume you would qualify the range as to not get it confused with the same range in a different sheet?

Also, along the same lines as what you have been helping me with, how could I modify your MaxCell function to find the minimum value in the newRange that is greater than zero? Below I modified the MaxCell function without the "greater than zero" portion. This gives me the minimum value however I am working with negative values in the range, and I would like to find the minimum value greater than zero, should I enter and "If" statement in there?

Please Login or Register  to view this content.

Thomas

[Bob Phillips]

Brilliant, I assume you would qualify the range as to not get it confused with the same range in a different sheet?

Sort of. It is to cover the code should the activesheet change from the sheet that you were looking at, or to avoid using activesheet, as an unqualified range automatically assumes the activesheet.

Also, along the same lines as what you have been helping me with, how could I modify your MaxCell function to find the minimum value in the newRange that is greater than zero? Below I modified the MaxCell function without the "greater than zero" portion. This gives me the minimum value however I am working with negative values in the range, and I would like to find the minimum value greater than zero, should I enter and "If" statement in there?

Please Login or Register  to view this content.

If you want a conditional MIN/MAX, that is a bit trickier as you need to process a range within an IF within the MIN/MAX function, which would be an array formula in Excel itself. But this can be evaluated in VBA, like so

Please Login or Register  to view this content.