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

Alan
Alan used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You could do it like this:

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

Author

Commented:
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.
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
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

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?
If the latter. this seems to work:
Dim v

v = Evaluate("=MAX(IF(A1:A3=""apples"",B1:B3))")

Open in new window

AlanConsultant

Author

Commented:
Hi Stephen,

Yes - the latter is exactly what I wanted.

I totally forgot about EVALUATE in VBA.

Thanks!

Alan.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial