N
NWO
Hello all.
(1) How can I convert the below routine to a UDF
(2) How can I run this routine using a sheet with about 30 different form
types, each with a string of monthly receipts? Guess what I'm asking is how
would I create a UDF in the form CYCYLETIME(INPUT REFERENCE RANGE, END
PENDING REFERENCE CELL) so I can repeat the use of the function across
several columns or rows nased on the UDFs input values.
Thank you.
Below posts refer.
Mark
Expand AllCollapse All
(1) How can I convert the below routine to a UDF
(2) How can I run this routine using a sheet with about 30 different form
types, each with a string of monthly receipts? Guess what I'm asking is how
would I create a UDF in the form CYCYLETIME(INPUT REFERENCE RANGE, END
PENDING REFERENCE CELL) so I can repeat the use of the function across
several columns or rows nased on the UDFs input values.
Thank you.
Below posts refer.
Mark
Gary''s Student said:This is just an example:
1. put monthly receipt values in column A
2. put the target (fixed) value in cell B1
3. enter and run this macro:
Sub Macro1()
Sum = 0
target = Cells(1, 2).Value
For i = 1 To 65536
Sum = Sum + Cells(i, 1).Value
If Sum >= target Then Exit For
denom = Sum
If i = 65535 Then Exit Sub
Next
j = i - 1
result = (target - denom) / Cells(i, 1).Value
Cells(1, 3).Value = j + result
End Sub
It will perform the calculation and enter the result in cell C1. If you are
not familiar with macros, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Expand AllCollapse All