SP UDF for EXCEL
 

i wish this quest for new year...hope santa will give me this..

happy holidays?
dribler2

"dribler2" wrote:

Hello,

Bob Philips just fed me this amazing UDfunction

here is the code...
'---------
Function mySPS(test, dec As Long, ParamArray rng())
Dim sConditions As String
Dim sRanges As String
Dim i As Long

sConditions = "--(" & rng(0).Address(, , , True)
sRanges = "ROUND(" & rng(0).Address(, , , True)
For i = LBound(rng) + 1 To UBound(rng)
sConditions = sConditions & "*" & rng(i).Address(, , , True)
sRanges = sRanges & "*" & rng(i).Address(, , , True)
Next i
If test = "+" Then
mySPS = Evaluate("=SumProduct(" & sConditions & "0)," & _
sRanges & "," & dec & "))")
ElseIf test = "-" Then
mySPS = Evaluate("=SumProduct(" & sConditions & "<0)," & _
sRanges & "," & dec & "))")
Else
mySPS = Evaluate("=SumProduct(" & sRanges & "," & dec & "))")
End If
End Function
'Call like
'=mySPS("-",2,L1:L3,M1:M3,N1:N3)
'for negatives
'=mySPS("+",2,L1:L3,M1:M3,N1:N3)
'for positives
'=mySPS("",2,L1:L3,M1:M3,N1:N3)
'for both

'--------------

Please help me to induce a little more task in this very good UDF..

goes like this

'Call like
'=mySPX("-",2,L1:L100="UNITPRICE",M1:M3="discount",N1:N100 ="notax",O1:O100="QTY")
'for negatives
'=mySPX("+",2,L1:L100="UNITPRICE",M1:M3="discoun t",N1:N100="notax",O1:O100="QTY")
'for positives
'=mySPX("",2,L1:L100="UNITPRICE",M1:M3="discount ",N1:N100="notax",O1:O100="QTY")
'for both

wherein (PARAMETERS may vary from "<=")
"UNITPRICE" is a numeric value
"discount" is a numeric value (e.g. 0.05 for 5%)
"notax" is a numeric value as "0" or "tax" is a numeric value as "(+ or -)"
"QTY" is a numeric value (+ or -)

I wish that this is possible in this excel vbe forum.

happy holidays...
dribler2