Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
what's the problem with this ?
small correction:
Function mySP(rng1 As Range, rng2 As Range, dec As Integer) As Double Dim v As Double, IamTheCount As Long IamTheCount = rng1.Count mySP2 = 0 For i = 1 To IamTheCount v = Round(rng1.Cells(i, 1).Value * rng2.Cells(i, 1).Value, dec) mySP = mySP + v Next End Function -- Gary's Student "dribler2" wrote: so short am i not to know the solution..never know the programming language. anyone know how to make this UDfunction work.. Function mySP(rng1 As Range, rng2 As Range, Optional dec As Long = 2) mySP = Application.Caller.Parent.Evaluate("SumProduct(Rou nd(" & rng1 * rng2, dec & "))") End Function 'syntax =mySP(A1:A100,B1:B100,1) ----SUM OF ROUNDED PRODUCT MADE OF 2 FACTOR ONLY happy holidays... driller. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
what's the problem with this ?
Thanks Gary,,
I make a new workbook named ''temp.xls'' on sheet 1 tabview codeVBAProject (temp.xls)insert module on Module 1 I just paste your udf save close then on sheet1, i place the factors from A1:B3 on cell C1 i click the fx button select category user definedtemp.xls!Module1.mySP [unfortunately, there is no formbox] =temp.xls!Module1.mySP() yet, i still type on C1: =temp.xls!Module1.mySP(A1:A3,B1:B3,2) the result is #NAME! error. i may be missing someting, please advice. thanks again.. dribler2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
what's the problem with this ?
Gary''student
Here's the UDfunction, after some close/open file and auto-debug from excel, this work out for me '--------------------------------- Function mySP(rng1 As Range, rng2 As Range, dec As Integer) As Double Dim v As Double, IamTheCount As Long IamTheCount = rng1.Count mySP = 0 For i = 1 To IamTheCount v = Round(rng1.Cells(i, 1).Value * rng2.Cells(i, 1).Value, dec) mySP = mySP + v Next End Function '----------------------------- at last my quest is over for the function of sum of rounded products for 2 factors---- ----------------------------- i will post another thread for some more generic/productive questions---maybe for 3 to 4 more factorial products... you are always dependable... more power driller "dribler2" wrote: Thanks Gary,, I make a new workbook named ''temp.xls'' on sheet 1 tabview codeVBAProject (temp.xls)insert module on Module 1 I just paste your udf save close then on sheet1, i place the factors from A1:B3 on cell C1 i click the fx button select category user definedtemp.xls!Module1.mySP [unfortunately, there is no formbox] =temp.xls!Module1.mySP() yet, i still type on C1: =temp.xls!Module1.mySP(A1:A3,B1:B3,2) the result is #NAME! error. i may be missing someting, please advice. thanks again.. dribler2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
problem with a conditional max problem | Excel Discussion (Misc queries) | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming |