worksheetfunction max with multiple criteria in excel VBA

rarmn

New Member
Joined
Apr 30, 2013
Messages
20
I would like to use worksheetfunction max with multiple criteria in excel VBA. I have the following data:

Col ACol B
a1
a2
a3
b4
b5

<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>
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

ZAX

Well-known Member
Joined
Jul 5, 2012
Messages
715
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

New Member
Joined
Apr 30, 2013
Messages
20
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

Well-known Member
Joined
Jul 5, 2012
Messages
715
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

Well-known Member
Joined
Mar 20, 2013
Messages
883

ADVERTISEMENT

Try:

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

rarmn

New Member
Joined
Apr 30, 2013
Messages
20
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

New Member
Joined
Apr 30, 2013
Messages
20

ADVERTISEMENT

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

Well-known Member
Joined
Mar 20, 2013
Messages
883
Code:
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

Well-known Member
Joined
Mar 20, 2013
Messages
883
Or you could just use the initial code, and fill down

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

range("C2:C10").FillDown
 

rarmn

New Member
Joined
Apr 30, 2013
Messages
20
Thank you very much. Works Great. Thanks a lot for all the help.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,916
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top