A More Efficient Method Than the Evaluate Method?

I'm looking to gain efficiency in some script I've written. I use the inefficient "Evaluate" method to return the minimum and maximum values in a range that meet a certain criteria:

Please Login or Register  to view this content.

The variable dblCohort is the criteria by which the function defines the conditional value to return. Basically, I'm looking for a function in VBA that returns the minimum and maximum values in a range that uses conditional arguments much in the same way SUMIFS, AVERAGEIFS or COUNTIFS do.

I suspect there's an easy solution, but I either don't know the solution or I'm blanking on it. I've searched through this forum and the on the internet with no luck. Any suggestions?

Tony

Originally Posted by romperstomper

There isn't one. Why do you think Evaluate is so inefficient?

Hey romperstomper.

Maybe "inefficient" is the wrong term. But when I comment those two lines out, my tool runs 44% faster. The code uses SUMIFS and COUNTIFS quite a bit, which means that these two functions don't seem to have the same effect operating on the same range that the Evaluate method does.

And since the tool I built loops through at least 12 different workbooks, 44% is quite a considerable difference in time.

Do you think there's a better way to go about this?

Tony

Originally Posted by romperstomper

Start by restricting the range you operate on, and also use the Evaluate method of the parent worksheet - supposed to be faster (though that may be moot if it's the active sheet)

Thanks for the suggestion. Since the tool loops through many workbooks,the ranges the Evaluate method operates on are of different dimensions -- one range may have 24 rows, another may have 2400. No matter,though: it's easy enough to create variables that capture each range's dimension.

And if I'm understanding your second statement correctly, since I'm setting and activating the worksheet (I need to do this because I'm applying a filter to it), I believe I'm using the Evaluate method of the parent worksheet. Though I can't be sure. Would you mind explaining the difference between the parent worksheet and any other type of worksheet?

Tony

Originally Posted by JBeaucaire

Perhaps efficiency would increase if you adjusted your Evaluate formulas to only the range of data instead of the entire column?

Thanks for the suggestion JBeaucaire. I agree with you and romperstomper, restricting the ranges will increase efficiency, even if only marginally. (Every bit helps.)

Originally Posted by shg

That's true, though I don't see why you need to select a sheet to filter it.

Sorry, I meant sorting, not filtering. I need to activate the worksheet to apply a sort to the table.

Originally Posted by shg

You don't need to select the sheet to sort, either.

Thanks for the input shg. I must be doing something wrong because when I comment out .Activate, I get this error:

Run-time error '1004':

The sort reference is not valid. Make sure that it's written within the data you want to sort, and the first Sort By box isn't the same or blank.

It breaks at the last line shown here:

Please Login or Register  to view this content.

Am I coding it incorrectly?

Originally Posted by shg

Unqualified, a range reference is to the active worksheet, so ...

Please Login or Register  to view this content.

Thanks. I clearly missed that little bit.

I'll update shortly on the progress.

Efficiency gained! Thanks to the help on everyone who chimed in today, the tool runs 4.25 faster.

Thanks again.