Replacing formula to cells EXTREMELY slow

[stakar]

Hi!
I have the following code
------------------------------------------------------------------------------

Private Sub ToggleButton3_Click()
Dim i As Long
Dim Lastrow As Long

Application.ScreenUpdating = False
Lastrow = 1000

If ToggleButton3.Value = True Then
'Fill in the checking formula
For i = 0 To 5
Cells(4, i + 9).Formula
"=IF($A4="""","""",COUNTIF(INDEX('Check'!4:4,1,$AI$2):INDEX('Check'!4:4,1,$AI$3),
& i & "))"
Next
Else
For i = 0 To 5
Cells(4, i + 9).Formula
"=IF($A4="""","""",COUNTIF(INDEX('unCheck'!4:4,1,$AI$2):INDEX('unCheck'!4:4,1,$AI$3),
& i & "))"
Next
End If

Range("I4:N" & Lastrow).FillDown
Application.ScreenUpdating = True

End Sub
---------------------------------------------------------------------------

This code replace a formula each time a toggle button is pressed.
The cells range that the formula is replaced are the I4 :N1000

Its working, but extremely , extremely SLOW!
Is there any suggestions making the code running faster ???

Thanks in advanc

 

[Norman Jones]

Hi Stakar,

Try turning the calculation mode off and on,

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

'Code

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

 

[stakar]

I have already did that but there is no change!
Maybe its something else to make it run faster ???