Update formulas by 1 row

[westcoastrhiann]

I would like to write a macro that I can run once a week to update certain cell formulas by 1 row. I'm thinking maybe offset but can't quite wrap my head around it.

Here's what I need

So in week 1 my spreadsheet would look something like this:

	A	B	C
1	5	=A1+1
2	10	=A2+2
3	15	=A3+3
4	20	=A4+4
5	25	=A5+5

<tbody>
</tbody>

After I run the macro in Week 1 the formulas would be:

A	B	C
1	5	=A2+1
2	10	=A3+2
3	15	=A4+3
4	20	=A5+4
5	25	=A6+5

<tbody>
</tbody>

After I run the macro in Week 2 the formulas would be:

A	B	C
1	5	=A3+1
2	10	=A4+2
3	15	=A5+3
4	20	=A6+4
5	25	=A7+5

<tbody>
</tbody>

What is the easiest way to update the cell row reference by 1?

Thanks!

 

[Joe4]

Maybe stored the "jump" in some other cell, and increase the single cell by 1 each week.
So, let's say that the cell is M1.
We start out with this formula:

=OFFSET(A1,$M$1,0)+1

and copy down for all rows.
The initial value in M1 is 0.

Each week, we up the value in M1 (i.e. 1, then 2 the following week, etc) to make our formulas jump down one row.

 

[westcoastrhiann]

I was hoping for something more automated, can I incorporate a macro to update the cell reference?

 

[Joe4]

Sure, but it really isn't any shorter/faster.
Instead of updating a single cell, you would need to select a macro to run.
So, it won't really save you any time.

If you want to go the VBA route, please let us know the following:
- Is this always going to be the formulas in column B?
- Will they be starting on row 1 or row 2?
- Will it always just be updating the current formulas (not adding or removing anything)?

 

[Joe4]

If you really want a VBA solution, there are many ways to go about it. Here is one:

Sub MyIncrementer()

    Dim r As Long, lr As Long
    Dim frm As String, newFrm As String
    Dim fArray() As String
    Dim part1 As Long, part2 As Long
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column C
    lr = Cells(Rows.Count, "C").End(xlUp).Row
    
'   Loop through all rows, starting with row 1
    For r = 1 To lr
'       Get formula of column C current row
        frm = Mid(Cells(r, "C").Formula, 2)
'       Split at plus sign
        fArray = Split(frm, "+")
'       Get different parts of formula, and add one to row number
        part1 = Mid(fArray(0), 2) + 1
        part2 = fArray(1)
'       Rebuild new formula
        newFrm = "=A" & part1 & "+" & part2
'       Apply new formula to column C in current row
        Cells(r, "C").Formula = newFrm
    Next r
    
    Application.ScreenUpdating = True
    
    MsgBox "Process complete!"
    
End Sub