worksheetfunction max with multiple criteria in excel VBA

[rarmn]

I would like to use worksheetfunction max with multiple criteria in excel VBA. I have the following data:

Col A	Col B
a	1
a	2
a	3
b	4
b	5

<colgroup><col width="44"><col width="44"></colgroup> <tbody>
</tbody>

In excel i was able to use the formula =MAX(($A$2:$A$6=$A2)*($B$2:$B$6)). I would like to be able to do the same in VBA using the worksheetfunction.max. I was trying something like

worksheetfunction.MAX(($A$2:$A$6=$A2)*($B$2:$B$6)) and it didnt work. Any suggestions on these line on how to proceed?

Thanks.

<colgroup><col width="44"><col width="44"></colgroup> <tbody> </tbody>

<colgroup><col width="86"><col width="86"></colgroup> <tbody>
</tbody>

 

[rarmn]

Dim i as integer
Dim rowmax as integer

rowmax = 10 'enter whatever value or expression here that you want

For i = 2 to rowmax

Range("C" & i).FormulaArray = "=MAX(($A$2:$A$6=$A$" & i & ")*($B$2:$B$6))"

Next i

Try the above, but make sure you define your rowmax variable however you want it.

This code works great and it will help me proceed further with my other requirements. Thanks

 

[rarmn]

Or you could just use the initial code, and fill down

range("C2").formulaarray = "=MAX(($A$2:$A$6=$A2)*($B$2:$B$6))"

range("C2:C10").FillDown

Thanks. This works great too.