Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert a static formula to dynamic formula ? | Excel Worksheet Functions | |||
Dynamic Formula with Dynamic Address | Excel Worksheet Functions | |||
Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Charts and Charting in Excel | |||
Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Excel Programming | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel |