poor subroutine performance

[Alex East]

I have a routine that updates cell values conditionally based on other cell
values on the same sheet.
e.g.
sCopyRange = "D" & LTrim(Str(i))
If Left(Range(sCopyRange).Value, 3) = "QA " Then
SCopyRange1 = "L" & LTrim(Str(i))
If Len(Range(SCopyRange1).Value) > 1 Then
Range(sCopyRange).Value = "Verified Fixed"
Else
Range(sCopyRange).Value = "Fixed"
End If
End If


This routine takes under a second in workbook1, but take 2 minutes+ using
the same data in another workbook2. The main difference is that workbook2
has a larger amount of macros and code, and data in other sheets, but as
stated above the data on the active sheet is identical. Can someone please
explain why this should be and what I can do about improving performance in
workbook2?

 

[Bob Phillips]

Alex,

Three things you could try.

Turn off Screenupdating - Application.Screenupdating = False
Turn off automatic calculation - Application.Calculation =
xlCalculationManual
If it is worksheet code, disable events - APplication.EnableEvents = False

I suggest you do one at a time to see which has the biggest impact.

Remember to reset at the end.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)