Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
worksheet_calculate
I have the following code which updates a cell once a value is entered into
one of the others. Private Sub worksheet_calculate() Application.ScreenUpdating = False If Range("H5") And Range("I5") 0 Then Range("E5") = "900" ElseIf Range("H5") Or Range("I5") 0 Then Range("E5") = "450" Else Range("E5") = "" End If If Range("H6") And Range("I6") 0 Then Range("E6") = "900" ElseIf Range("H6") Or Range("I6") 0 Then Range("E6") = "450" Else Range("E6") = "" End If If Range("H7") And Range("I7") 0 Then Range("E7") = "900" ElseIf Range("H7") Or Range("I7") 0 Then Range("E7") = "450" Else Range("E7") = "" End If Application.ScreenUpdating = True End Sub The problem is it takes a long time for the code to run. Can anyone help? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
worksheet_calculate
Hi
Do you really need to use the Calculate Event? Wouldn't a simple worksheet change do? Also, you are making bitwise comparisons with the And and Or that are conceivably slow - do you really want to be doing this, or do you literally want to check if either cell is 0 (OR) or both cells are greater than 0 (And)? Perhaps: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count0 Then Exit Sub If Not Intersect(Target,Range("H5:I7")) Is Nothing Then Application.EnableEvents = False If Range("H"&Target.Row)0 And Range("I"&Target.Row)0 Then Range("E"&Target.Row) = "900" ElseIf Range("H"&Target.Row)0 Or Range("I"&Target.Row)0 Then Range("E"&Target.Row) = "450" Else Range("E"&Target.Row) = "" End If Application.EnableEvents = True End If End Sub Hope this helps! Richard On 26 Jan, 08:17, enyaw wrote: I have the following code which updates a cell once a value is entered into one of the others. Private Sub worksheet_calculate() Application.ScreenUpdating = False If Range("H5") And Range("I5") 0 Then Range("E5") = "900" ElseIf Range("H5") Or Range("I5") 0 Then Range("E5") = "450" Else Range("E5") = "" End If If Range("H6") And Range("I6") 0 Then Range("E6") = "900" ElseIf Range("H6") Or Range("I6") 0 Then Range("E6") = "450" Else Range("E6") = "" End If If Range("H7") And Range("I7") 0 Then Range("E7") = "900" ElseIf Range("H7") Or Range("I7") 0 Then Range("E7") = "450" Else Range("E7") = "" End If Application.ScreenUpdating = True End Sub The problem is it takes a long time for the code to run. Can anyone help? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
worksheet_calculate
Your changes in the code could even be forcing more calculates which might
explain the long time to run. I agree with RichardSchollar that choosing another event such as Change might be more effective. If you do that, change things a little to make your code look like this Application.ScreenUpdating=False Application.EnableEvents=False ....your functional code here Application.EnableEvents=True Application.ScreenUpdating=True Or, you could perhaps do away with the VBA completely? In E5, for example: =IF(H50,IF(I50,900,450),IF(I50,450,"")) just fill that down through E6 and E7 and I believe you'll get the same results that your code is providing. "enyaw" wrote: I have the following code which updates a cell once a value is entered into one of the others. Private Sub worksheet_calculate() Application.ScreenUpdating = False If Range("H5") And Range("I5") 0 Then Range("E5") = "900" ElseIf Range("H5") Or Range("I5") 0 Then Range("E5") = "450" Else Range("E5") = "" End If If Range("H6") And Range("I6") 0 Then Range("E6") = "900" ElseIf Range("H6") Or Range("I6") 0 Then Range("E6") = "450" Else Range("E6") = "" End If If Range("H7") And Range("I7") 0 Then Range("E7") = "900" ElseIf Range("H7") Or Range("I7") 0 Then Range("E7") = "450" Else Range("E7") = "" End If Application.ScreenUpdating = True End Sub The problem is it takes a long time for the code to run. Can anyone help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|