MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Cumulative macro


Posted by Rayn on May 13, 2001 9:09 PM

Hi

The macro below calculates the correct value for the first cell within
a specified range, however it does not continue progression to the remainder of the range
the formula in it needs to be variable. (that is waht i want)

to work chose a first cell and for second cell press control
to select than press the macro button.

Thank you for your help

Sub ReportIntervals()


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) / (rCell2.Row - rCell1.Row) + rCell1

Next rAllCells

Set rCell1 = Nothing
Set rCell2 = Nothing
Set rFillRange = Nothing


End Sub


Posted by Dave Hawley on May 14, 2001 2:24 AM

Hi Rayn

I'm having a real hard time trying fathom out what you are trying to do here. I take it your two selected ranges contain more than 1 cell each ? You have used rCell2 - rCell1 and rCell2.Row - rCell1.Row in your loop, unless these are single cells you are trying to subtract a Range Object from another Range Object. As you have found out the default for this will only ever be the first cell within the Range. I'm taking a bit of a wild guess here but try this:


Sub ReportIntervals()

Dim i As Integer
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))
i = 1
For Each rAllCells In rFillRange
rAllCells = _
(rCell2.Cells(i, 1) - rCell1.Cells(i, 1)) / _
(rCell2.Cells(i, 1).Row - rCell1.Cells(i, 1).Row) + rCell1.Cells(i, 1)
i = i + 1
Next rAllCells

Set rCell1 = Nothing
Set rCell2 = Nothing
Set rFillRange = Nothing


End Sub


Perhaps if you could provide an easy to follow example ,I can help more.


Dave


OzGrid Business Applications

Posted by Rayn on May 14, 2001 5:21 PM

: Sub ReportIntervals() : : 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) / (rCell2.Row - rCell1.Row) + rCell1 : Next rAllCells : Set rCell1 = Nothing : Set rCell2 = Nothing : Set rFillRange = Nothing : : End Sub

Hi Rayn I'm having a real hard time trying fathom out what you are trying to do here. I take it your two selected ranges contain more than 1 cell each ? You have used rCell2 - rCell1 and rCell2.Row - rCell1.Row in your loop, unless these are single cells you are trying to subtract a Range Object from another Range Object. As you have found out the default for this will only ever be the first cell within the Range. I'm taking a bit of a wild guess here but try this: Dim i As Integer Set rFillRange = Range(rCell1.Offset(1, 0), rCell2.Offset(-1, 0)) i = 1 For Each rAllCells In rFillRange rAllCells = _ (rCell2.Cells(i, 1) - rCell1.Cells(i, 1)) / _ (rCell2.Cells(i, 1).Row - rCell1.Cells(i, 1).Row) + rCell1.Cells(i, 1) i = i + 1 Next rAllCells Set rCell1 = Nothing Perhaps if you could provide an easy to follow example ,I can help more. Dave

Hi dave

Here is an example

Lets say Cell A1 has the number 2 and cell A5 has the number 5
on selection i want the macro to do the following:

($A$5-$A$1/number of intervals, which is 4 cells)-A1
and than go on to fill the range between A1 and A5 with the same formula

thank you Dave !!!!!!!!!!!!!!!


Posted by Dave Hawley on May 15, 2001 2:25 AM

Hi Rayn

This sounds very familiar to me, I would swear I wrote some code for this exact reason about 5-6 weeks ago. If it was you and you have the link to to it let me know and I'll modify the code fo you.


Dave
OzGrid Business Applications

Posted by Rayn on May 15, 2001 3:48 PM

Hi Rayn This sounds very familiar to me, I would swear I wrote some code for this exact reason about 5-6 weeks ago. If it was you and you have the link to to it let me know and I'll modify the code fo you. Dave

Hi dave

yes it was me that you wrote the code for. the code i provided at the begining of this message is the code
you wrote for me. however it was giving results not in order (for example some of the values calculated were correct but they were not inserted in the correct cells)


Thank you DAVE