I hope this code will be self-explanatory enough. If not, feel free to ask
more questions. Basically it takes the value in the 'number installed to
date' cells and keeps the value and rebuilds the formula to add that value as
a part of the new formula.

Example, let's assume that column A has the 'installed this period' numbers
starting at row 2 and in column B is the 'number installed to date' formula,
which might start out initially looking like this:
=A2 + 0
and you put 7 in A2, so B2 shows 7. After running this code the formula in
B2 would be:
=A2 + 7
and A2 will be set to zero.

But to start things off, if you don't want to put in the =A2+0 formula, you
can simply put in =A2 to initialize things and get a value to build on, but
it does need some value in column B to use as the basis of the new formulas.

Sub PrepareForNewPeriod()
'you must have the sheet with the
'formulas and values in it selected
'before running this macro
' jlatham
'
Const PeriodColumn = "A" ' installed this period
Const ToDateColumn = "B" ' total to date values
Const FirstDataRow = 2 '1st row with formula
Dim cOffset As Integer
Dim lastRow As Long
Dim anyFormula As String
Dim rOffset As Long
Dim baseCell As Range

'calculate offset from
'ToDateColumn to PeriodColumn
cOffset = Range(PeriodColumn & 1).Column - _
Range(ToDateColumn & 1).Column
'find last row to change
'formulas in
If Val(Right(Application.Version, 2)) < 12 Then
'in pre-2007 Excel
lastRow = Range(ToDateColumn & _
Rows.Count).End(xlUp).Row
Else
'in Excel 2007 (or later)
lastRow = Range(ToDateColumn & _
Rows.CountLarge).End(xlUp).Row
End If
'set up a base cell location as the
'first cell with a 'to date' value/formula in it
Set baseCell = Range(ToDateColumn & FirstDataRow)
Do Until baseCell.Offset(rOffset, 0).Row lastRow
'build new formula
baseCell.Offset(rOffset, 0).FormulaR1C1 = _
"=RC[" & cOffset & "] + " & _
baseCell.Offset(rOffset, 0).Value
' zero out period entries
baseCell.Offset(rOffset, cOffset) = 0
' to next row
rOffset = rOffset + 1
Loop
End Sub

"Pieter" wrote:

I am only a beginner so could you please keep it simple.
I need to build a spreadsheet for weekly reporting.
I need one column for "number installed to date".
I need another column for "number installed this period" which will add onto the "number installed to date".
Every week I will have to zero out the "number installed this period column", without changing the values in the "number installed to date" column.
Once zeroed, I need to enter new figures for the week, which ideally would add themselves to "number installed to date".
Any help would be much appreciated.

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com