Cumulative Macro
Posted by Rayn on May 02, 2001 5:47 PM
Hi can someone help ???
i am trying to develop a macro to do the following:
If ia have a value in cell A1 and a value in cell A10
i want the macro to : (A10-A1/number of intervals)+A1
and fill in the intervals accordingly. so in cell A3
there will be (A10-A1/intervals)+ A2. therefore it is cumulative
and so on until all the intervals are filled.
Cell A1 and A10 are variables, they might chage to B1 and B20
so on.
Help will be greatly appreciated as i have spend a great deal of time with
no success. due to my limited VB experience
Thank you
Regards
Rayan
Posted by Mark W. on May 02, 2001 6:06 PM
Wouldn't it be...
(A10-A1)/(number of intervals-1)+A1
Posted by Mark W. on May 02, 2001 6:32 PM
And...
A function-like formulation that is dependent
only on the cell references of the 1st and last
"interval" would be:
=($A$10-$A$1)/(ROW($A$10)-ROW($A$1))+A1
Posted by Mark W. on May 02, 2001 6:40 PM
Or...made even more invariant...
Posted by Mark W. on May 02, 2001 6:48 PM
Oops...small bug! Should be...
Posted by Dave Hawley on May 02, 2001 11:23 PM
Hi Rayan
Not too sure I have the formula syntax correct or not, but try this code.
Before you Run it select Cell A1 then holding down your Ctrl key select Cell A10. This will enable you to use it on a variable range.
Sub ReportIntervals()
'Written by OzGrid Business Applications
'www.ozgrid.com
'''''''''''''''''''''''''''''''''''''''''''''''''
'Reports interval between to single cell selections
'''''''''''''''''''''''''''''''''''''''''''''''''
Dim rCell1 As Range
Dim rCell2 As Range
Dim rFillRange As Range
Dim rAllCells As Range
Set rCell1 = Selection.Areas(1)
Set rCell2 = Selection.Areas(2)
Set rFillRange = Range(rCell1.Offset(1, 0), rCell2.Offset(-1, 0))
For Each rAllCells In rFillRange
rAllCells = (rCell2 - rCell1) / (rAllCells.Row - rCell1.Row) + rCell1
Next rAllCells
Set rCell1 = Nothing
Set rCell2 = Nothing
Set rFillRange = Nothing
End Sub
Dave
OzGrid Business Applications
Posted by Rayn on May 02, 2001 11:47 PM
Dave
Thanks Dave. i have been trying to this for a while but my VBA Knoweldge is very limited
Regards
Rayn
Posted by Dave Hawley on May 02, 2001 11:58 PM
Glad it helped Rayn. Looks like my grammar knowledge is limited :o) "to" should be two
Dave
OzGrid Business Applications
