Replacing a formula its too slow

  • Thread starter Thread starter stakar
  • Start date Start date
S

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
 
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
 
Back
Top