Use a formula of

=SUMPRODUCT(--((MOD(COLUMN(RC[-2]:RC[22]),7)-COLUMN(RC[-2])+1)=1),RC[-2]:RC[22])

where =1 is column B, =2 is column C, ..., =0 is column H etc.


--

HTH

Bob
"Ludo" wrote in message
...
Hi,

i have a worksheet with a dynamic number of columns, allways a
multiple of 7 columns.
The first column (A) contains the week number, then from column B on
starts the data, witch is a multiple of 7 columns wide. In my last 7
colums need i a formula to add the values from the previous data.
Example:
The formula need te be in cell P4 and is the sum of cell B4+I4 (see
offset from 7 columns each).
Because the number of data columns is dynamic, the add formula can be
also on W4 and is then the sum of B4+I4+P4

Any idea how to code this in a compact way?
I'm trying to write it in a select case (see below), but this isn't
realy dynamic
assume there are in a later stadia more columns needed, i have to
change the code, and i wan't to avoid that.

While DifferentUnitsInFamily 0
Select Case DifferentUnitsInFamily
Case 1
ActiveCell.FormulaR1C1 = "=SUM(RC[-7])"
Case 2
ActiveCell.FormulaR1C1 = "=SUM(RC[-14],RC[-7])"
Case 3
ActiveCell.FormulaR1C1 =
"=SUM(RC[-21],RC[-14],RC[-7])"


End Select

Any help apreciated,

Regards,
Ludo