Cumulative macro

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

: 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 !!!!!!!!!!!!!!!

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

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