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 |
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 |