SLOW Code...

Hello,

I have been working in excel for a little while now and am able to do a
few things in code but my abilities are still limited. I have a piece of
code that is extremely slow and was looking for help on tracking down the
problem.

I have a tab (Crew Schedules) and a tab (Crew Raw Data). One is for the
crew schedules and the other is for saving a revolving six weeks. When
someone enters data into the crew schedule the 'Change Event' fires - puts
the data just entered into the Crew Raw Data and then puts the formula back
into the cell inorder to lookup th enewly stored data. This is great. All
is well except sometimes a user needs to copy schedule data onto the crew
schedule. The change event fires and runs right but it is VERY SLOW. It can
be minutes depending on the volume of data being pasted. The data will never
be more than 28 rows by 8 columns... but still.

Any ideas on how to move this faster? Here is some code...

Application.ScreenUpdating = False
Application.EnableEvents = False

For Each C In Target

With C

'Lookup Row / Column values
intCol = Cells(5, C.Column).Value
intRow = Cells(C.Row, 26).Value


'Move the data to the database
Worksheets("Crew Raw Data").Cells(intRow, intCol).Value = C.Value

FixFormula:

'Fix the formula
Select Case C.Row
Case 76
C.Formula = "=IF(INDIRECT(" & strQuote & "'Crew Raw Data'!" & _
strQuote & "& ADDRESS($Z" & C.Row & "," & strCol & "$5))=" & _
strQuote & strQuote & ",0,INDIRECT(" & _
strQuote & "'Crew Raw Data'!" & strQuote & " & ADDRESS($Z" & _
C.Row & "," & strCol & "$5)))"
Case Else
C.Formula = "=IF(INDIRECT(" & strQuote & "'Crew Raw Data'!" & _
strQuote & "& ADDRESS($Z" & C.Row & "," & strCol & "$5))=" & _
strQuote & strQuote & "," & strQuote & strQuote & ",INDIRECT(" & _
strQuote & "'Crew Raw Data'!" & strQuote & " & ADDRESS($Z" & _
C.Row & "," & strCol & "$5)))"

End Select

Thanks for looking,
Ernst Guckel

Ernst,

Do you have Calculation turned off?...

Application.Calculation = xlCalculationManual
'do stuff
Application.Calculation = xlCalculationAutomatic

Regards,
Jim Cone
San Francisco, USA


"Ernst Guckel" <ErnstGuckel@discussions.microsoft.com> wrote in message
news:D39F0A1F-EA22-479B-80A6-C84BF71882E8@microsoft.com...
> Hello,
>
> I have been working in excel for a little while now and am able to do a
> few things in code but my abilities are still limited. I have a piece of
> code that is extremely slow and was looking for help on tracking down the
> problem.
>
> I have a tab (Crew Schedules) and a tab (Crew Raw Data). One is for the
> crew schedules and the other is for saving a revolving six weeks. When
> someone enters data into the crew schedule the 'Change Event' fires - puts
> the data just entered into the Crew Raw Data and then puts the formula back
> into the cell inorder to lookup th enewly stored data. This is great. All
> is well except sometimes a user needs to copy schedule data onto the crew
> schedule. The change event fires and runs right but it is VERY SLOW. It can
> be minutes depending on the volume of data being pasted. The data will never
> be more than 28 rows by 8 columns... but still.
- snip -

Hi Ernst

If the Change event is changeing cell content, then each one of those
changes will fire a new Change event. And if those are starting to change
cell content, then each one of those changes ... and so on.

Application.EnableEvents = False
'actions
Application.EnableEvents = True

prevents that from happening.

But this is not an universal solution, you may actually need some or all of
the functionality of the code when cells are changed also by this event. In
those cases the code must be rewritten a bit.

If you write something into a cell then Target (the parameter in the code)
is a single cell. So Target.Count = 1. If you paste an array of cells then
Target.Count > 1 . You should perhaps have your code behave differently when
Target.Count > 1, like turning off EnableEvents and loop each cell in Target
to do something with each one.

HTH. Best wishes Harald

"Ernst Guckel" <ErnstGuckel@discussions.microsoft.com> skrev i melding
news:D39F0A1F-EA22-479B-80A6-C84BF71882E8@microsoft.com...
> Hello,
>
> I have been working in excel for a little while now and am able to do a
> few things in code but my abilities are still limited. I have a piece of
> code that is extremely slow and was looking for help on tracking down the
> problem.
>
> I have a tab (Crew Schedules) and a tab (Crew Raw Data). One is for the
> crew schedules and the other is for saving a revolving six weeks. When
> someone enters data into the crew schedule the 'Change Event' fires - puts
> the data just entered into the Crew Raw Data and then puts the formula
back
> into the cell inorder to lookup th enewly stored data. This is great.
All
> is well except sometimes a user needs to copy schedule data onto the crew
> schedule. The change event fires and runs right but it is VERY SLOW. It
can
> be minutes depending on the volume of data being pasted. The data will
never
> be more than 28 rows by 8 columns... but still.
>
> Any ideas on how to move this faster? Here is some code...
>
> Application.ScreenUpdating = False
> Application.EnableEvents = False
>
> For Each C In Target
>
> With C
>
> 'Lookup Row / Column values
> intCol = Cells(5, C.Column).Value
> intRow = Cells(C.Row, 26).Value
>
>
> 'Move the data to the database
> Worksheets("Crew Raw Data").Cells(intRow, intCol).Value = C.Value
>
> FixFormula:
>
> 'Fix the formula
> Select Case C.Row
> Case 76
> C.Formula = "=IF(INDIRECT(" & strQuote & "'Crew Raw Data'!" & _
> strQuote & "& ADDRESS($Z" & C.Row & "," & strCol & "$5))=" & _
> strQuote & strQuote & ",0,INDIRECT(" & _
> strQuote & "'Crew Raw Data'!" & strQuote & " & ADDRESS($Z" & _
> C.Row & "," & strCol & "$5)))"
> Case Else
> C.Formula = "=IF(INDIRECT(" & strQuote & "'Crew Raw Data'!" & _
> strQuote & "& ADDRESS($Z" & C.Row & "," & strCol & "$5))=" & _
> strQuote & strQuote & "," & strQuote & strQuote & ",INDIRECT("
& _
> strQuote & "'Crew Raw Data'!" & strQuote & " & ADDRESS($Z" & _
> C.Row & "," & strCol & "$5)))"
>
> End Select
>
> Thanks for looking,
> Ernst Guckel