Replacing a formula its too slow

[stakar]

Replacing formula to cells EXTREMELY slow
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('unChe
ck'!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 SLOW (pentium3 at 800Mhz)

I have already tried the screenupdating to false and calculation to
manual but there's no effect.

Is there any suggestions making the code running faster ???

Thanks in advance
Stathis

 

[Charles Williams]

Hi Stathis,

I would suggest doing the tow flavours of countif once for each of your 5
columns, and then just refer to the result in your IF statement.
That should run about 500 times faster.

I do not see a application.Calculation=xlmanual statement in the sub ...

Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com