Dynamic add formula

[Ludo]

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

 

[Javed]

You can use the procedure.Just select the cell immediate right to your
data.if O7 is the last data then select P7.
Then copy the formula to all required cells.
I have used array formula for simplification


Sub Formula7Offset()

Dim AddStt As String, Col As Long

'Calculating the address of the range to use in formula (From B column
to the previous cell)
AddStt = Cells(ActiveCell.Row, 2).Address(rowabsolute:=False) & ":" &
ActiveCell.Offset(0, -1).Address(rowabsolute:=False)

'This line required for dynamically getting column no to use in
formula
Col = Range(AddStt).Columns.Count + 1

'Enters an array formula
ActiveCell.FormulaArray = "=+SUM(IF(MOD(COLUMN(" & AddStt &
")-1,7)=COLUMN()-" & Col & "," & AddStt & ",0))"

End Sub

 

[Bob Phillips]

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.