Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SLOW Code...
Ernst,
Do you have Calculation turned off?... Application.Calculation = xlCalculationManual 'do stuff Application.Calculation = xlCalculationAutomatic Regards, Jim Cone San Francisco, USA "Ernst Guckel" wrote in message ... 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 - |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SLOW Code...
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" skrev i melding ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
QUERY & HELP: so slow executing VBA code... :S | Excel Worksheet Functions | |||
Code WAY too slow... (worksheet_change event) | Excel Programming | |||
code running super slow... | Excel Programming | |||
Slow Code | Excel Programming | |||
Is this slow code? | Excel Programming |