You probably need to change Frank's UDF a bit to allow for some quirks
(treats all references not qualified by sheet names as being on whatever is
currently the active sheet) in application.evaluate when handling ranges on
different sheets:

Public Function MaxIF(criteriaRange As Range, searchValue As
Variant,calcRange As Range)

MaxIF = Evaluate("=SumProduct(Max((" &
criteriaRange.address(External:=true) & "=" &
searchvalue & ")*(" & calcRange.address(External:=true) & ")))")

End Function


Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

"Frank Kabel" wrote in message
...
Hi
and you want to run this function from the spreadsheet. If yes, no way to
do
it this way as a function invoked from the spreadsheet can't change cells.
It
can only return values. Not tested but you may try:


Public Function MaxIF(criteriaRange As Range, searchValue As Variant,
calcRange As Range)

MaxIF = Evaluate("=SumProduct(Max((" & criteriaRange.address & "=" &
searchvalue & ")*(" & calcRange.address & ")))")

End Function




"mienz" wrote:


Hi
I want to write a reuseable maxif function in vba. The following code
is working when entered directly into the cell:

=SUMPRODUCT(MAX((B1:B6=B2) * (A1:A6)))

Now I want to pack this function into vba code to make it commonly
available. I tried it with the following code but it doesn't work. I
assume there is a problem with the passing of the range parameters. I
also tried it with String and Variant as parameter types - without
success. Can anybody help me?

Public Function MaxIF(criteriaRange As Range, searchValue As Variant,
calcRange As Range)

AciveCell.Formula = "=SumProduct(Max((criteriaRange = searchValue)
* (calcRange)))"

End Function


--
mienz
------------------------------------------------------------------------
mienz's Profile:
http://www.excelforum.com/member.php...o&userid=16304
View this thread:
http://www.excelforum.com/showthread...hreadid=276970