Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
updating formulas | Excel Discussion (Misc queries) | |||
How do I add rows at fixed intervals-add values after every 5 Rows | Excel Discussion (Misc queries) | |||
Updating formulas | Excel Worksheet Functions | |||
Creating Formulas In Excel To Calculate Time Intervals | Excel Worksheet Functions | |||
How do I fill down formulas so they iterate in intervals other th. | Excel Worksheet Functions |