Move the range to the right

[StuartM1]

Hi all,

Hope everyone's enjoying their Holidays.

Please help. I inherited a spreadsheet with formulas that refer to a separate range. With each new month, the entire range needs to move over one column to consider only the latest (12 mo) periods.

Is there a quick and easy way to change the cell references inside the formulas from, for example,

A2:C50 to B2:D50

Thanks very much!!

 

[Michael M]

Higlight the range of formula cells>>Copy >>Select the first new cell >>Paste
OR
Highlight the formula cells>>>grab the little black box in the bottom right of the last cell>> drag one cell to the right

 

[Oaktree]

You can... but I wouldn't recommend doing this.

Instead, why not use something like =OFFSET(A2,0,A1,49,12) where A1 has the month number? That way, if you ever want to go back to the previous month, you'd just change A1's value.

That said, you can get the explicit answer to your question with code like this that uses .offset and .directprecedents together.

Sub test()

Dim my_cell

For Each my_cell In Selection

    With my_cell

        .Formula = Replace(.Formula, .DirectPrecedents.Address(0, 0), .DirectPrecedents.Offset(0, 1).Address(0, 0))

    End With

Next my_cell

End Sub