mienz,

Copy the code below, and paste it into your codemodule. Then use it like

=MaxIF(B1:B6, B2, A1:A6)

HTH,
Bernie
MS Excel MVP

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

Dim myCell As Range
Dim i As Integer

i = 0
MaxIF = 0

For Each myCell In criteriaRange
i = i + 1
If myCell.Value = searchValue Then
MaxIF = Application.Max(MaxIF, calcRange(i))
End If
Next myCell
End Function

"mienz" wrote in message
...

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