maxif function in vba

[mienz]

Hi
I want to write a reuseable maxif function in vba. The following cod
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 commonl
available. I tried it with the following code but it doesn't work.
assume there is a problem with the passing of the range parameters.
also tried it with String and Variant as parameter types - withou
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 Functio

 

[Guest]

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

 

[Bernie Deitrick]

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

 

[Tom Ogilvy]

if you are trying to do

=MaxIf()
in a cell, then the only thing you function can do is return a value to that
cell. It can't put a formula in the cell or any other cell. In you
function, you need to write the code to calculate the value.

 

[Charles Williams]

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]

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