Insert calculation with VBA is slow
 

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


*** Sent via Developersdex http://www.developersdex.com ***

Insert calculation with VBA is slow
 

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


--
moondark
------------------------------------------------------------------------
moondark's Profile: http://www.excelforum.com/member.php...o&userid=27390
View this thread: http://www.excelforum.com/showthread...hreadid=475759

Insert calculation with VBA is slow
 

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


"Les Stout" wrote in message
...
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


*** Sent via Developersdex http://www.developersdex.com ***

Insert calculation with VBA is slow
 

Hi Les,

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

Change 2 to the first formula insertion row.

---
Regards,
Norman


"Norman Jones" wrote in message
...
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


"Les Stout" wrote in message
...
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


*** Sent via Developersdex http://www.developersdex.com ***

Insert calculation with VBA is slow
 

Thanks Norman.

best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Insert calculation with VBA is slow
 

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

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***