worksheet_calculate

[Guest]

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?

 

[RichardSchollar]

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.Count>0 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

 

[Guest]

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(H5>0,IF(I5>0,900,450),IF(I5>0,450,""))
just fill that down through E6 and E7 and I believe you'll get the same
results that your code is providing.