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>

 

[ZAX]

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

<tbody> </tbody>

<tbody>
</tbody>

You should be using the Range property and double quotes in VBA..
i.e worksheetfunction.MAX((Range("$A$2:$A$6") = Range("$A2"))*(Range("$B$2:$B$6")))
ZAX

 

[rarmn]

Thanks ZAX. I tried the following:

range("C2").value = WorksheetFunction.Max((Range("$A$2:$A$6") = Range("$A2")) * (Range("$B$2:$B$6")))


range("C2").value=Application.WorksheetFunction.Max((Range(Cells(2, "A"), Cells(6, "A")) = Cells(2, "A")) * (Range(Cells(2, "B"), Cells(6, "B"))))

I am getting error for either of these statements.

Thanks.

 

[ZAX]

Thanks ZAX. I tried the following:

range("C2").value = WorksheetFunction.Max((Range("$A$2:$A$6") = Range("$A2")) * (Range("$B$2:$B$6")))


range("C2").value=Application.WorksheetFunction.Max((Range(Cells(2, "A"), Cells(6, "A")) = Cells(2, "A")) * (Range(Cells(2, "B"), Cells(6, "B"))))

I am getting error for either of these statements.

Thanks.

Plz,Would you tell me what do you want the formula to do??
ZAX

 

[nuked]

Try:

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

 

[rarmn]

I have an column of names with duplicates and column of numbers against each name. I want to find the max number for each set of name. Basically i want to find max number based on multiple criteria for each set of name. I also would like to use worksheetfunction with range(cells) to control the rows and columns.

Thanks for the help.

 

[rarmn]

Thanks nuked, this works for me. i want to put the formula in a for loop so i need to be able to control the row i am passing into the formula. i tried the same formula with

for row 2 to rowmax

range(cells(row, "C")).formulaarray = "=MAX((Range(Cells(2,"A"),Cells(6,"A))=Cells(row,"A"))*(Range(Cells(2,"B"),Cells(6,"B"))))".

next

any suggestions on how to implement in this way.

Thanks for all the help.
</pre>

 

[nuked]

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.

 

[nuked]

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

 

[rarmn]

Thank you very much. Works Great. Thanks a lot for all the help.