CODEWITHSUNDEEP
excelvbamax
MGraugaard
MGraugaard

Reputation: 27

Return the max value within a range where the values evaluated fulfills a criteria in VBA

I want to use this function from excel in my VBA code

MAX((F2:F20=CellRef)*G2:G20)

The function returns the largest value of values in a range which falls within a certain condition.(CellRef is refering to a specific cell in the range F2:F20)

The function works perfectly fine in excel, but I can't seem to implement it correctly in my VBA code.

This is what I wrote in VBA:

WorksheetFunction.Max((Worksheets("Ark1").Range("F2:F35000") = CellRef2) * Worksheets("Ark1").Range("G2:G35000"))

The purpose is to then see if this output (which is a date) is equal to another date, if so then Something. (CellRef2 is refering to a specific cell in the range F2:F35000)

Thanks in advance

Using the function in excel:

MAX date cell 1 represent the largest date for that serial_Number "uefh"

enter image description here

Upvotes: 1

Views: 616

Answers (1)

JohnnieL
JohnnieL

Reputation: 1231

You can do this using the MAXIFS function https://support.microsoft.com/en-gb/office/maxifs-function-dfd611e6-da2c-488a-919b-9b6376b28883

so if your above data starts with serial_number in A1, the formula in C2 would be

=MAXIFS($B$2:$B$15, $A$2:$A$15, A2)

Upvotes: 1

Related Questions