Dynamic add formula
 

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

Dynamic add formula
 

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

Dynamic add formula
 

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