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

--
mien
-----------------------------------------------------------------------
mienz's Profile: http://www.excelforum.com/member.php...fo&userid=1630
View this thread: http://www.excelforum.com/showthread.php?threadid=27697

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

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

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

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

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.
--
Regards,
Tom Ogilvy

"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