summing ranges

[Markus Scheible]

Hi newsgroup,

I've got some problems with summing ranges, maybe someone
could give me a tip?

I have a range of 744 entries and I want to sum every 24
cells from the first cell of the range - this sum should
be written in another range which also has 744 entries -
so this second range should get the sum, then 23 empty
cells and then the sum of the next 24 cells from the first
range...

Now I'm thinking about a solution with modulo but
therefore I would need the cell index or something else...
but maybe someone knows a better solution? Nevertheless,
how can I relatively address a cell within a range?

Thanks a lot in advance.

Best regards!
Markus
..

[Rob van Gelder]

Sub test()
Const cInterval = 24
Dim rngS As Range, rngD As Range, i As Long

Set rngS = Range("A1")
Set rngD = Range("C1")
For i = 0 To 744 - 1 Step cInterval
rngD.Offset(i).Value =
WorksheetFunction.Sum(rngS.Offset(i).Resize(cInterval))
Next
End Sub

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Markus Scheible" <[email protected]> wrote in message
news:[email protected]...
>
> Hi newsgroup,
>
> I've got some problems with summing ranges, maybe someone
> could give me a tip?
>
> I have a range of 744 entries and I want to sum every 24
> cells from the first cell of the range - this sum should
> be written in another range which also has 744 entries -
> so this second range should get the sum, then 23 empty
> cells and then the sum of the next 24 cells from the first
> range...
>
> Now I'm thinking about a solution with modulo but
> therefore I would need the cell index or something else...
> but maybe someone knows a better solution? Nevertheless,
> how can I relatively address a cell within a range?
>
> Thanks a lot in advance.
>
> Best regards!
> Markus
> .
>
>

[Markus Scheible]

Hi Rob,


>Sub test()
> Const cInterval = 24
> Dim rngS As Range, rngD As Range, i As Long
>
> Set rngS = Range("A1")
> Set rngD = Range("C1")
> For i = 0 To 744 - 1 Step cInterval
> rngD.Offset(i).Value =
>WorksheetFunction.Sum(rngS.Offset(i).Resize(cInterval))
> Next
>End Sub


pretty good :o)

Thanks a lot and have a nice day!

Markus

[Rob van Gelder]

Sub test()
Const cInterval = 24
Dim rngS As Range, rngD As Range, i As Long

Set rngS = Range("A1")
Set rngD = Range("C1")
For i = 0 To 744 - 1 Step cInterval
rngD.Offset(i).Value =
WorksheetFunction.Sum(rngS.Offset(i).Resize(cInterval))
Next
End Sub

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Markus Scheible" <[email protected]> wrote in message
news:[email protected]...
>
> Hi newsgroup,
>
> I've got some problems with summing ranges, maybe someone
> could give me a tip?
>
> I have a range of 744 entries and I want to sum every 24
> cells from the first cell of the range - this sum should
> be written in another range which also has 744 entries -
> so this second range should get the sum, then 23 empty
> cells and then the sum of the next 24 cells from the first
> range...
>
> Now I'm thinking about a solution with modulo but
> therefore I would need the cell index or something else...
> but maybe someone knows a better solution? Nevertheless,
> how can I relatively address a cell within a range?
>
> Thanks a lot in advance.
>
> Best regards!
> Markus
> .
>
>

[Markus Scheible]

Hi Rob,


>Sub test()
> Const cInterval = 24
> Dim rngS As Range, rngD As Range, i As Long
>
> Set rngS = Range("A1")
> Set rngD = Range("C1")
> For i = 0 To 744 - 1 Step cInterval
> rngD.Offset(i).Value =
>WorksheetFunction.Sum(rngS.Offset(i).Resize(cInterval))
> Next
>End Sub


pretty good :o)

Thanks a lot and have a nice day!

Markus