asked on

Excel VBA - Find max value in worksheet range, with criteria in another column

Hi All,

I need to code a lookup in VBA to find a cell in a worksheet containing the max value, subject to a criteria in another column.

Example (two columns of date in a worksheet):

Apples 10
Apples 20
Bananas 30

I need to return '20' being the largest value for apples.

Trivial using worksheet formulae, but I can't think how to do it in VBA!

Thanks,

Alan.

StephenJR

You could do it like this:

Range("D1").FormulaArray = "=MAX(IF(A1:A3=""apples"",B1:B3))"

Alan

ASKER

Hi Stephen,

Doesn't that put a formula into cell D1?

I guess I *could* do it like that, then read the value of D1, and finally delete the formula, but that's really no different to just doing it in the worksheet?

I was hoping for a way to get the same result within VBA itself. Is that possible?

Please correct me if I am missing something!

Thanks,

Alan.

StephenJR

Alan, OK this will put the formula in and then replace it with the value:

With Range("D1")
.FormulaArray = "=MAX(IF(A1:A3=""apples"",B1:B3))"
.Value = .Value
End With

StephenJR

Sorry, I may be complelely missing the point. Do you just want to assign it to a variable and not put it in the worksheet at all?

ASKER CERTIFIED SOLUTION

StephenJR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Alan

ASKER

Hi Stephen,

Yes - the latter is exactly what I wanted.

I totally forgot about EVALUATE in VBA.

Thanks!

Alan.