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