Hi Charles
good point!
Though for me the question remains why to use a UDF at all for this
:-)))

--
Regards
Frank Kabel
Frankfurt, Germany

"Charles Williams" schrieb im Newsbeitrag
...
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