Code Takes Forever to Run - Why?
 

I'm having difficulty with the following code. If I remove the first 'IF'
statement regarding the comparison to < 1500, it runs fine (ie, quickly), but
inserting it causes the routine to take about 1 minute to run ... forever in
my world ... and I can't figure out why it's causing such a delay.

I don't have to have the code built this way, but I've pulled it out into
it's ow separate section and the result - slow run time - persists.

Any help is greatly appreciated.

Brett

Private Sub Worksheet_Change(ByVal Target As Range)
With Me
If Intersect(Target, .Range("FROI_Vol"), .Range("SROI_Vol"),
..Range("MSR_Vol")) Is Nothing Then
If .Range("Total_Vol").Value < 1500 Then
.Range("AutoTrigger_Impl_Fee") = 0
Else
If .Range("Total_Vol").Value < 501 Or RenewProduct.Value = True Then
PT_Yes.Enabled = False
PT_No.Enabled = False
PT_No.Value = True
Else
PT_Yes.Enabled = True
PT_No.Enabled = True
PT_No.Value = True
End If
End If
End If
End With

Code Takes Forever to Run - Why?
 

Application.EnableEvents=False
..Range("AutoTrigger_Impl_Fee") = 0
Application.EnableEvents=True
--
Gary''s Student - gsnu200812


"BJ" wrote:

I'm having difficulty with the following code. If I remove the first 'IF'
statement regarding the comparison to < 1500, it runs fine (ie, quickly), but
inserting it causes the routine to take about 1 minute to run ... forever in
my world ... and I can't figure out why it's causing such a delay.

I don't have to have the code built this way, but I've pulled it out into
it's ow separate section and the result - slow run time - persists.

Any help is greatly appreciated.

Brett

Private Sub Worksheet_Change(ByVal Target As Range)
With Me
If Intersect(Target, .Range("FROI_Vol"), .Range("SROI_Vol"),
.Range("MSR_Vol")) Is Nothing Then
If .Range("Total_Vol").Value < 1500 Then
.Range("AutoTrigger_Impl_Fee") = 0
Else
If .Range("Total_Vol").Value < 501 Or RenewProduct.Value = True Then
PT_Yes.Enabled = False
PT_No.Enabled = False
PT_No.Value = True
Else
PT_Yes.Enabled = True
PT_No.Enabled = True
PT_No.Value = True
End If
End If
End If
End With

Code Takes Forever to Run - Why?
 

gsnu

outstanding! thanks. in a nutshell can you educate me as to what i was
doing wrong and why the new code corrects works more efficiently?

bj

"Gary''s Student" wrote:

Application.EnableEvents=False
.Range("AutoTrigger_Impl_Fee") = 0
Application.EnableEvents=True
--
Gary''s Student - gsnu200812


"BJ" wrote:

I'm having difficulty with the following code. If I remove the first 'IF'
statement regarding the comparison to < 1500, it runs fine (ie, quickly), but
inserting it causes the routine to take about 1 minute to run ... forever in
my world ... and I can't figure out why it's causing such a delay.

I don't have to have the code built this way, but I've pulled it out into
it's ow separate section and the result - slow run time - persists.

Any help is greatly appreciated.

Brett

Private Sub Worksheet_Change(ByVal Target As Range)
With Me
If Intersect(Target, .Range("FROI_Vol"), .Range("SROI_Vol"),
.Range("MSR_Vol")) Is Nothing Then
If .Range("Total_Vol").Value < 1500 Then
.Range("AutoTrigger_Impl_Fee") = 0
Else
If .Range("Total_Vol").Value < 501 Or RenewProduct.Value = True Then
PT_Yes.Enabled = False
PT_No.Enabled = False
PT_No.Value = True
Else
PT_Yes.Enabled = True
PT_No.Enabled = True
PT_No.Value = True
End If
End If
End If
End With

Code Takes Forever to Run - Why?
 

Sure. The Worksheet_Change code responds to any changes in the worksheet.
We want it to respond to changes that the User types or pastes. We don't
want it to respond to changes that it makes. Otherwise we can get a long
series of re-entries into the routine.

So in general, if we want the sub to make changes in the sheet, we first
disable events, then makes the changes, then re-enable events.
--
Gary''s Student - gsnu200812


"BJ" wrote:

gsnu

outstanding! thanks. in a nutshell can you educate me as to what i was
doing wrong and why the new code corrects works more efficiently?

bj

"Gary''s Student" wrote:

Application.EnableEvents=False
.Range("AutoTrigger_Impl_Fee") = 0
Application.EnableEvents=True
--
Gary''s Student - gsnu200812


"BJ" wrote:

I'm having difficulty with the following code. If I remove the first 'IF'
statement regarding the comparison to < 1500, it runs fine (ie, quickly), but
inserting it causes the routine to take about 1 minute to run ... forever in
my world ... and I can't figure out why it's causing such a delay.

I don't have to have the code built this way, but I've pulled it out into
it's ow separate section and the result - slow run time - persists.

Any help is greatly appreciated.

Brett

Private Sub Worksheet_Change(ByVal Target As Range)
With Me
If Intersect(Target, .Range("FROI_Vol"), .Range("SROI_Vol"),
.Range("MSR_Vol")) Is Nothing Then
If .Range("Total_Vol").Value < 1500 Then
.Range("AutoTrigger_Impl_Fee") = 0
Else
If .Range("Total_Vol").Value < 501 Or RenewProduct.Value = True Then
PT_Yes.Enabled = False
PT_No.Enabled = False
PT_No.Value = True
Else
PT_Yes.Enabled = True
PT_No.Enabled = True
PT_No.Value = True
End If
End If
End If
End With

Code Takes Forever to Run - Why?
 

Hi. As a side note, I may have this wrong, so I'll just throw it our
for consideration.


If .Range("Total_Vol").Value < 1500 Then
.Range("AutoTrigger_Impl_Fee") = 0
Else
If .Range("Total_Vol").Value < 501 Or RenewProduct.Value = True...


It appears to me that if "Total_Vol" is say 499, or 500, then

the line <1500 gets executed, and the line with <501 never does.

Is this intentional?

- -
Dana DeLouis

(Also, since PT_No.Value = True is done either way, I think it could be
pulled out and listed only once.)


BJ wrote:
gsnu

outstanding! thanks. in a nutshell can you educate me as to what i was
doing wrong and why the new code corrects works more efficiently?

bj

"Gary''s Student" wrote:

Application.EnableEvents=False
.Range("AutoTrigger_Impl_Fee") = 0
Application.EnableEvents=True
--
Gary''s Student - gsnu200812


"BJ" wrote:

I'm having difficulty with the following code. If I remove the first 'IF'
statement regarding the comparison to < 1500, it runs fine (ie, quickly), but
inserting it causes the routine to take about 1 minute to run ... forever in
my world ... and I can't figure out why it's causing such a delay.

I don't have to have the code built this way, but I've pulled it out into
it's ow separate section and the result - slow run time - persists.

Any help is greatly appreciated.

Brett

Private Sub Worksheet_Change(ByVal Target As Range)
With Me
If Intersect(Target, .Range("FROI_Vol"), .Range("SROI_Vol"),
.Range("MSR_Vol")) Is Nothing Then
If .Range("Total_Vol").Value < 1500 Then
.Range("AutoTrigger_Impl_Fee") = 0
Else
If .Range("Total_Vol").Value < 501 Or RenewProduct.Value = True Then
PT_Yes.Enabled = False
PT_No.Enabled = False
PT_No.Value = True
Else
PT_Yes.Enabled = True
PT_No.Enabled = True
PT_No.Value = True
End If
End If
End If
End With