Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|