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.

Microsoft ExcelSpreadsheets

Last Comment

Alan

8/22/2022 - Mon

StephenJR

5/15/2011

You could do it like this:

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

Alan

5/15/2011

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

5/15/2011

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

Your help has saved me hundreds of hours of internet surfing.

fblack61

StephenJR

5/15/2011

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

5/15/2011

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options