Updating formulas with new rows at set intervals?
 

Hi there,

I have a row of formulas that I want to update with new row references that
are a set # of rows away from the orginal.

ie) Row 1: =A1+B1, =C1+D1, =F1+G1, =H1+L1

I need Row 1 to look like this, adding 5 rows onto each first cell reference:

ie) Row 1: =F1+B1, =H1+D1, =K1+G1, =M1+L1

Is there an easy way to do this besides going into each cell indidually to
make the change? I have a lot of sheets and a lot of columns that need to be
changed.

Thanks!

Updating formulas with new rows at set intervals?
 

Select your cells, and run the macro below. I have assumed that all your
cell formulas are simple additions as shown.

HTH,
Bernie
MS Excel MVP

Sub IncrementFirstCellRerence()
Dim myF As String
Dim myC As Range

For Each myC In Selection.SpecialCells(xlCellTypeFormulas)
myF = myC.Formula
myF = Mid(myF, 2, Len(myF))
myF = Split(myF, "+")(0)
myC.Formula = Replace(myC.Formula, myF, _
Range(myF).Offset(0, 5).Address(False, False), , 1)
Next myC
End Sub

"lulabirdy10" wrote in message
...
Hi there,

I have a row of formulas that I want to update with new row references
that
are a set # of rows away from the orginal.

ie) Row 1: =A1+B1, =C1+D1, =F1+G1, =H1+L1

I need Row 1 to look like this, adding 5 rows onto each first cell
reference:

ie) Row 1: =F1+B1, =H1+D1, =K1+G1, =M1+L1

Is there an easy way to do this besides going into each cell indidually to
make the change? I have a lot of sheets and a lot of columns that need to
be
changed.

Thanks!

Updating formulas with new rows at set intervals?
 

Sorry, I should have set events to false and calculation mode to manual...


Sub IncrementFirstCellRerence()
Dim myF As String
Dim myC As Range
Dim CalcMode As Variant

With Application
.ScreenUpdating = False
CalcMode = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
.DisplayAlerts = False
End With

For Each myC In Selection.SpecialCells(xlCellTypeFormulas)
myF = myC.Formula
myF = Mid(myF, 2, Len(myF))
myF = Split(myF, "+")(0)
myC.Formula = Replace(myC.Formula, myF, _
Range(myF).Offset(0, 5).Address(False, False), , 1)
Next myC

With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
.Calculation = CalcMode
End With

End Sub

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Select your cells, and run the macro below. I have assumed that all your
cell formulas are simple additions as shown.

HTH,
Bernie
MS Excel MVP

Sub IncrementFirstCellRerence()
Dim myF As String
Dim myC As Range

For Each myC In Selection.SpecialCells(xlCellTypeFormulas)
myF = myC.Formula
myF = Mid(myF, 2, Len(myF))
myF = Split(myF, "+")(0)
myC.Formula = Replace(myC.Formula, myF, _
Range(myF).Offset(0, 5).Address(False, False), , 1)
Next myC
End Sub

"lulabirdy10" wrote in message
...
Hi there,

I have a row of formulas that I want to update with new row references
that
are a set # of rows away from the orginal.

ie) Row 1: =A1+B1, =C1+D1, =F1+G1, =H1+L1

I need Row 1 to look like this, adding 5 rows onto each first cell
reference:

ie) Row 1: =F1+B1, =H1+D1, =K1+G1, =M1+L1

Is there an easy way to do this besides going into each cell indidually
to
make the change? I have a lot of sheets and a lot of columns that need
to be
changed.

Thanks!