Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing a formula its too slow
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 --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing a formula its too slow
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 "stakar " wrote in message ... 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 --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help on replacing this formula | Excel Discussion (Misc queries) | |||
Replacing formula | Excel Programming | |||
Replacing Formula | Excel Programming | |||
replacing value by formula | Excel Programming | |||
Replacing a value from a formula | Excel Programming |