Increment Cell Reference

**XLVBA** · 05-07-2012, 04:54 PM

I have a worksheet that contains the days of the week (Mon, Tues, etc...) in B4:B10. In C4:G10 these cells = a cell on other sheets.

Each week I have to go in and manually change each cell reference to look at the next row down on the sheet they pull from. Is there a way that I can set it so that when I push a command button, it will just increment the cell reference row by one?

Example: C4 is ='3rd'!B23
I would like to push a button so that C4 is ='3rd'!B24

**mrice** · 05-07-2012, 05:02 PM

Try

Sub Test()
Dim Cell As Range
For Each Cell In Selection
    If Cell.Formula Like "=*" Then
        Cell.Formula = Left(Cell.Formula, InStr(Cell.Formula, "!")) & Range(Right(Cell.Formula, Len(Cell.Formula) - InStr(Cell.Formula, "!"))).Offset(1, 0).Address
    End If
Next Cell
End Sub

This will increment the formulae on selected cells.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010.

**vandan_tanna** · 05-07-2012, 05:13 PM

I would use formula rather than macro for this.

Assuming it is always the last row you want.

Example: C4 is ='3rd'!B23. Change that to

=OFFSET('3rd'!B1,COUNTA('3rd'!B:B)-1,0,1,1)

I hope some version of the above formula helps you with your need.

**XLVBA** · 05-08-2012, 08:33 AM

Mrice: The macro worked beautifully! Thank you! In some cases I also needed it to jump down 7 rows, but simply changing the 1 to a 7 took care of that as well.
Thanks Vandan; but it's not always the last row.