|
Very Slow code
Why should the following (supposedly simple piece of) code run so slowly:-
Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Application.EnableEvents = False For Each mycell In Range("C11:K11,M11:U11") If mycell.Value = "" Then mycell.Offset(32).Value = "" ElseIf mycell.Value = 0 Then mycell.Offset(32).Value = 0 End If Next MsgBox "End" Application.ScreenUpdating = True Application.EnableEvents = True End Sub Thanks Sandy |
Very Slow code
don't know the size of your work book, but try adding this
Calculation = xlCalculationManual Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual For Each mycell In Range("C11:K11,M11:U11") If mycell.Value = "" Then mycell.Offset(32).Value = "" ElseIf mycell.Value = 0 Then mycell.Offset(32).Value = 0 End If Next MsgBox "End" Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub -- Gary "Sandy" wrote in message ... Why should the following (supposedly simple piece of) code run so slowly:- Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Application.EnableEvents = False For Each mycell In Range("C11:K11,M11:U11") If mycell.Value = "" Then mycell.Offset(32).Value = "" ElseIf mycell.Value = 0 Then mycell.Offset(32).Value = 0 End If Next MsgBox "End" Application.ScreenUpdating = True Application.EnableEvents = True End Sub Thanks Sandy |
Very Slow code
Gary
It certainly speeds up the code execution but it prevents all other sheet calculations! Is there another way without interfering with the cell formulae? Sandy "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... don't know the size of your work book, but try adding this Calculation = xlCalculationManual Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual For Each mycell In Range("C11:K11,M11:U11") If mycell.Value = "" Then mycell.Offset(32).Value = "" ElseIf mycell.Value = 0 Then mycell.Offset(32).Value = 0 End If Next MsgBox "End" Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub -- Gary "Sandy" wrote in message ... Why should the following (supposedly simple piece of) code run so slowly:- Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Application.EnableEvents = False For Each mycell In Range("C11:K11,M11:U11") If mycell.Value = "" Then mycell.Offset(32).Value = "" ElseIf mycell.Value = 0 Then mycell.Offset(32).Value = 0 End If Next MsgBox "End" Application.ScreenUpdating = True Application.EnableEvents = True End Sub Thanks Sandy |
Very Slow code
did you see the end of your code that i pasted? it turns calc on after your code
runs. Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic -- Gary "Sandy" wrote in message ... Gary It certainly speeds up the code execution but it prevents all other sheet calculations! Is there another way without interfering with the cell formulae? Sandy "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... don't know the size of your work book, but try adding this Calculation = xlCalculationManual Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual For Each mycell In Range("C11:K11,M11:U11") If mycell.Value = "" Then mycell.Offset(32).Value = "" ElseIf mycell.Value = 0 Then mycell.Offset(32).Value = 0 End If Next MsgBox "End" Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub -- Gary "Sandy" wrote in message ... Why should the following (supposedly simple piece of) code run so slowly:- Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Application.EnableEvents = False For Each mycell In Range("C11:K11,M11:U11") If mycell.Value = "" Then mycell.Offset(32).Value = "" ElseIf mycell.Value = 0 Then mycell.Offset(32).Value = 0 End If Next MsgBox "End" Application.ScreenUpdating = True Application.EnableEvents = True End Sub Thanks Sandy |
Very Slow code
Don
Sadly that won't work. Let me explain a bit more. If the value in ("C11:K11,M11:U11") is 0 then the value in corresponding Row 43 cell (Offset(32)) must be 0 however if it is 0 (integers) then the value (in Row 43) is input by the user and can be anywhere from 1 to approx 60. The values in (C43:K43,M43:U43") are used in other calculations, and conditional formatting is included. Thing is though if I use the same code in an otherwise blank worksheet, ie no calculating cells or conditional formatting it still runs slowly?!? Sandy "Don Guillett" wrote in message ... Might?? even be simpler (depending on data) For Each mycell In Range("C11:K11,M11:U11") mycell.Offset(32).Value = mycell.Value Next -- Don Guillett Microsoft MVP Excel SalesAid Software "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... don't know the size of your work book, but try adding this Calculation = xlCalculationManual Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual For Each mycell In Range("C11:K11,M11:U11") If mycell.Value = "" Then mycell.Offset(32).Value = "" ElseIf mycell.Value = 0 Then mycell.Offset(32).Value = 0 End If Next MsgBox "End" Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub -- Gary "Sandy" wrote in message ... Why should the following (supposedly simple piece of) code run so slowly:- Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Application.EnableEvents = False For Each mycell In Range("C11:K11,M11:U11") If mycell.Value = "" Then mycell.Offset(32).Value = "" ElseIf mycell.Value = 0 Then mycell.Offset(32).Value = 0 End If Next MsgBox "End" Application.ScreenUpdating = True Application.EnableEvents = True End Sub Thanks Sandy |
Very Slow code
Gary
My apologies I didn't see the end part - with that in it works very well. Thank you Curiosity - What does the "xlCalculationSemiAutomatic" do. Thanks again Sandy "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... did you see the end of your code that i pasted? it turns calc on after your code runs. Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic -- Gary "Sandy" wrote in message ... Gary It certainly speeds up the code execution but it prevents all other sheet calculations! Is there another way without interfering with the cell formulae? Sandy "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... don't know the size of your work book, but try adding this Calculation = xlCalculationManual Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual For Each mycell In Range("C11:K11,M11:U11") If mycell.Value = "" Then mycell.Offset(32).Value = "" ElseIf mycell.Value = 0 Then mycell.Offset(32).Value = 0 End If Next MsgBox "End" Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub -- Gary "Sandy" wrote in message ... Why should the following (supposedly simple piece of) code run so slowly:- Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Application.EnableEvents = False For Each mycell In Range("C11:K11,M11:U11") If mycell.Value = "" Then mycell.Offset(32).Value = "" ElseIf mycell.Value = 0 Then mycell.Offset(32).Value = 0 End If Next MsgBox "End" Application.ScreenUpdating = True Application.EnableEvents = True End Sub Thanks Sandy |
Very Slow code
i think it may be this, but i'm sure someone who knows more than me will answer
: Data table calculations Data tables recalculate whenever a worksheet is recalculated, even if they have not changed. To speed up calculation of a worksheet that contains a data table, you can change the Calculation options to automatically recalculate the worksheet but not data tables. -- Gary "Sandy" wrote in message ... Gary My apologies I didn't see the end part - with that in it works very well. Thank you Curiosity - What does the "xlCalculationSemiAutomatic" do. Thanks again Sandy "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... did you see the end of your code that i pasted? it turns calc on after your code runs. Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic -- Gary "Sandy" wrote in message ... Gary It certainly speeds up the code execution but it prevents all other sheet calculations! Is there another way without interfering with the cell formulae? Sandy "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... don't know the size of your work book, but try adding this Calculation = xlCalculationManual Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual For Each mycell In Range("C11:K11,M11:U11") If mycell.Value = "" Then mycell.Offset(32).Value = "" ElseIf mycell.Value = 0 Then mycell.Offset(32).Value = 0 End If Next MsgBox "End" Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub -- Gary "Sandy" wrote in message ... Why should the following (supposedly simple piece of) code run so slowly:- Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Application.EnableEvents = False For Each mycell In Range("C11:K11,M11:U11") If mycell.Value = "" Then mycell.Offset(32).Value = "" ElseIf mycell.Value = 0 Then mycell.Offset(32).Value = 0 End If Next MsgBox "End" Application.ScreenUpdating = True Application.EnableEvents = True End Sub Thanks Sandy |
| All times are GMT +1. The time now is 01:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com