Excel 2007, changing multiple formulas

[J Streger]

I have a workbook that adds a worksheet to the workbook, and then updates
formulas on that workbook to include the new worksheet. The code takes 4
seconds to run in 2003, and 168 seconds in 2007. So I ddi some testing and in
a blank worksheet I added this code:

Sub junk()

Dim inx As Integer
Dim iny As Integer

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For inx = 0 To 299
For iny = 0 To 19

Me.Range("A1").Offset(inx, iny).Formula= "=" &
Me.Range("A1").Offset(inx, iny + 52).Address
Next
Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

And tested it in Excel 2003 and 2007, IN 2003, the code barely took a
second. In 2007 it took over a minute. now I know the trick for setting
multiple cell values using arrays, but what can you do if it's the formulas
you want to update. 40x longer for code to run is just unacceptable!

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

User of MS Office 2003

 

[Barb Reinhardt]

I think I'd do it this way

Dim myRange As Range
Dim aWS As Worksheet

Set aWS = ActiveSheet


Set myRange = aWS.Range("A1").Resize(300, 20)

myRange.FormulaR1C1 = "=RC[+52]"
End Sub

 

[J Streger]

Thank you. Looks like the days of looping through cells is numbered. Well I
guess I can loop through the cells and make a union of the cells I want to
affect, then affect them all at once. So very annoying to have to re-code
functions.

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

User of MS Office 2003

I think I'd do it this way

Dim myRange As Range
Dim aWS As Worksheet

Set aWS = ActiveSheet


Set myRange = aWS.Range("A1").Resize(300, 20)

myRange.FormulaR1C1 = "=RC[+52]"
End Sub

--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.

I have a workbook that adds a worksheet to the workbook, and then updates
formulas on that workbook to include the new worksheet. The code takes 4
seconds to run in 2003, and 168 seconds in 2007. So I ddi some testing and in
a blank worksheet I added this code:

Sub junk()

Dim inx As Integer
Dim iny As Integer

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For inx = 0 To 299
For iny = 0 To 19

Me.Range("A1").Offset(inx, iny).Formula= "=" &
Me.Range("A1").Offset(inx, iny + 52).Address
Next
Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

And tested it in Excel 2003 and 2007, IN 2003, the code barely took a
second. In 2007 it took over a minute. now I know the trick for setting
multiple cell values using arrays, but what can you do if it's the formulas
you want to update. 40x longer for code to run is just unacceptable!

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

User of MS Office 2003