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 |
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 |