Insert calculation with VBA is slow

[Les Stout]

Hi all, i have a spread sheet that i have to insert two calculations in,
in columns "I" & "J" and i am doing it by looping down the sheet as the
length is variable, sometimes 100 rows and sometimes 10,000 rows !! My
question is, is there a quicker way to do this, as when you get to the
bigger spread sheet it takes quite long ?

Best regards,

Les Stout

 

[moondark]

Hey there,

Of course, looping down is the easiest way, so why shouldn't you go
it.

so while the stopcondition is false do
Insert the formulae or values into the cells I&J of the current
column
increment the rowindex
loop

That was the slow way, to make excel calculate much faster, we're now
going to deactivate calculation and screenupdating, while inserting.

The Code will look like:

Code:
--------------------
rowindex=1
application.screenupdating=false
application.calculation = xlManual
On Error goto Resetting
while ([stop_condition])
Cells(rowindex,9)="I"
Cells(rowindex,10)="J"
rowindex = rowindex + 1
loop

Resetting:
Application.screenupdating = true
Application.calculation= xlAutomatic

--------------------



Regards,

Simon

 

[Norman Jones]

Hi Les,

Try something like:

'=============>>
Public Sub TryIt()
Dim rng As Range
Dim Lrow As Long
Dim CalcMode As Long
Const col As String = "I" '<<== CHANGE

Lrow = Cells(Rows.Count, col).End(xlUp).Row

On Error GoTo XIT

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

Set rng = Range(col & "2:" & col & Lrow)
With rng
.FormulaR1C1 = "=SUM(RC[1]:RC[3])" '<<== CHANGE
.Offset(0, 1).FormulaR1C1 = _
"=SUM(RC[5]:RC[7])" '<<== CHANGE
End With

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<=============

 

[Norman Jones]

Hi Les,

Set rng = Range(col & "2:" & col & Lrow)

Change 2 to the first formula insertion row.

---
Regards,
Norman

Hi Les,

Try something like:

'=============>>
Public Sub TryIt()
Dim rng As Range
Dim Lrow As Long
Dim CalcMode As Long
Const col As String = "I" '<<== CHANGE

Lrow = Cells(Rows.Count, col).End(xlUp).Row

On Error GoTo XIT

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

Set rng = Range(col & "2:" & col & Lrow)
With rng
.FormulaR1C1 = "=SUM(RC[1]:RC[3])" '<<== CHANGE
.Offset(0, 1).FormulaR1C1 = _
"=SUM(RC[5]:RC[7])" '<<== CHANGE
End With

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<=============

---
Regards,
Norman

Hi all, i have a spread sheet that i have to insert two calculations in,
in columns "I" & "J" and i am doing it by looping down the sheet as the
length is variable, sometimes 100 rows and sometimes 10,000 rows !! My
question is, is there a quicker way to do this, as when you get to the
bigger spread sheet it takes quite long ?

Best regards,

Les Stout

 

[Les Stout]

Thanks Norman.

best regards,

Les Stout

 

[Les Stout]

Thanks Simon, alais moondark will also save this for my macro database.

Les Stout