|
Very slow macro
Hi All...........
I have this macro repeated 36 times in my workbook, varying the names, etc..........they work fine, except they take a LONG time to execute, with the Status Bar indicating "Calculating Cells", and incrementing every 5%.........can anyone please show me how to speed this up?.....the two Ranges are D8:BC30 and D33:BC46. Sub UpdateSkeletonCrewX() Worksheets("SkeletonCrewX").Select ActiveSheet.Unprotect Range("FieldSkeletonCrewX1").ClearContents Range("FieldSkeletonCrewX2").ClearContents Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX1").Formula = "=IF(ISNA(VLOOKUP(($C8&""_""&D$1&""_""&D$4),Master Namefile!$B:$S,14,FALSE)), """",VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamef ile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX2").Formula = "=IF(ISNA(VLOOKUP(($C30&""_""&D$1&""_""&D$4),Maste rNamefile!$B:$S,14,FALSE)) ,"""",VLOOKUP(($C30&""_""&D$1&""_""&D$4),MasterNam efile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("c3").Formula = "=now()" Range("C3").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Range("a5").Select End Sub TIA Vaya con Dios, Chuck, CABGx3 |
Very slow macro
Every time a cell is changed the entire workbook must recalculate. To get
around this you can turn the caluclations off while the macro runs. The same as tools-options-Calculation- manual sub DoSomeStuff on error goto ErrorHandler Application.calculation = xlManual 'Do your stuff ErrorHandler: Application.calculation = xlAutomatic End Sub As always when you play with application level settings it is a good idea to use an error handler to rest things in case of (or more accurately when) a crash. -- HTH... Jim Thomlinson "CLR" wrote: Hi All........... I have this macro repeated 36 times in my workbook, varying the names, etc..........they work fine, except they take a LONG time to execute, with the Status Bar indicating "Calculating Cells", and incrementing every 5%.........can anyone please show me how to speed this up?.....the two Ranges are D8:BC30 and D33:BC46. Sub UpdateSkeletonCrewX() Worksheets("SkeletonCrewX").Select ActiveSheet.Unprotect Range("FieldSkeletonCrewX1").ClearContents Range("FieldSkeletonCrewX2").ClearContents Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX1").Formula = "=IF(ISNA(VLOOKUP(($C8&""_""&D$1&""_""&D$4),Master Namefile!$B:$S,14,FALSE)), """",VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamef ile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX2").Formula = "=IF(ISNA(VLOOKUP(($C30&""_""&D$1&""_""&D$4),Maste rNamefile!$B:$S,14,FALSE)) ,"""",VLOOKUP(($C30&""_""&D$1&""_""&D$4),MasterNam efile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("c3").Formula = "=now()" Range("C3").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Range("a5").Select End Sub TIA Vaya con Dios, Chuck, CABGx3 |
Very slow macro
Thanks Jim, but I must have done something wrong.......it takes longer
now........ Sub UpdateSkeletonCrewX() On Error GoTo ErrorHandler Application.Calculation = xlManual Worksheets("SkeletonCrewX").Select ActiveSheet.Unprotect Range("FieldSkeletonCrewX1").ClearContents Range("FieldSkeletonCrewX2").ClearContents Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX1").Formula = "=IF(ISNA(VLOOKUP(($C8&""_""&D$1&""_""&D$4),Master Namefile!$B:$S,14,FALSE)), """",VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamef ile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX2").Formula = "=IF(ISNA(VLOOKUP(($C30&""_""&D$1&""_""&D$4),Maste rNamefile!$B:$S,14,FALSE)) ,"""",VLOOKUP(($C30&""_""&D$1&""_""&D$4),MasterNam efile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("c3").Formula = "=now()" Range("C3").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Range("a5").Select ErrorHandler: Application.Calculation = xlAutomatic End Sub Vaya con Dios, Chuck, CABGx3 "Jim Thomlinson" wrote in message ... Every time a cell is changed the entire workbook must recalculate. To get around this you can turn the caluclations off while the macro runs. The same as tools-options-Calculation- manual sub DoSomeStuff on error goto ErrorHandler Application.calculation = xlManual 'Do your stuff ErrorHandler: Application.calculation = xlAutomatic End Sub As always when you play with application level settings it is a good idea to use an error handler to rest things in case of (or more accurately when) a crash. -- HTH... Jim Thomlinson "CLR" wrote: Hi All........... I have this macro repeated 36 times in my workbook, varying the names, etc..........they work fine, except they take a LONG time to execute, with the Status Bar indicating "Calculating Cells", and incrementing every 5%.........can anyone please show me how to speed this up?.....the two Ranges are D8:BC30 and D33:BC46. Sub UpdateSkeletonCrewX() Worksheets("SkeletonCrewX").Select ActiveSheet.Unprotect Range("FieldSkeletonCrewX1").ClearContents Range("FieldSkeletonCrewX2").ClearContents Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX1").Formula = "=IF(ISNA(VLOOKUP(($C8&""_""&D$1&""_""&D$4),Master Namefile!$B:$S,14,FALSE)), """",VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamef ile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX2").Formula = "=IF(ISNA(VLOOKUP(($C30&""_""&D$1&""_""&D$4),Maste rNamefile!$B:$S,14,FALSE)) ,"""",VLOOKUP(($C30&""_""&D$1&""_""&D$4),MasterNam efile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("c3").Formula = "=now()" Range("C3").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Range("a5").Select End Sub TIA Vaya con Dios, Chuck, CABGx3 |
Very slow macro
don't know if will work in your case, but i have created the formula in each
cell without the = sign. then i go back and add the = sign when i'm done so it calculates a lot less times. so, if the formula in A3 was =A1+A2, i would put A1+A2 in the cell. after i created all of the text for the formulas, i would go back and add the = sign then use the code: Range("A3").Formula = "='" & Range("A3").Formula like i said, it cut my code run times on one spreadsheet from 3 minutes to 30 seconds. obviously i had more elaborate formulas than this, just giving an example. -- Gary "CLR" wrote in message ... Thanks Jim, but I must have done something wrong.......it takes longer now........ Sub UpdateSkeletonCrewX() On Error GoTo ErrorHandler Application.Calculation = xlManual Worksheets("SkeletonCrewX").Select ActiveSheet.Unprotect Range("FieldSkeletonCrewX1").ClearContents Range("FieldSkeletonCrewX2").ClearContents Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX1").Formula = "=IF(ISNA(VLOOKUP(($C8&""_""&D$1&""_""&D$4),Master Namefile!$B:$S,14,FALSE)), """",VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamef ile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX2").Formula = "=IF(ISNA(VLOOKUP(($C30&""_""&D$1&""_""&D$4),Maste rNamefile!$B:$S,14,FALSE)) ,"""",VLOOKUP(($C30&""_""&D$1&""_""&D$4),MasterNam efile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("c3").Formula = "=now()" Range("C3").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Range("a5").Select ErrorHandler: Application.Calculation = xlAutomatic End Sub Vaya con Dios, Chuck, CABGx3 "Jim Thomlinson" wrote in message ... Every time a cell is changed the entire workbook must recalculate. To get around this you can turn the caluclations off while the macro runs. The same as tools-options-Calculation- manual sub DoSomeStuff on error goto ErrorHandler Application.calculation = xlManual 'Do your stuff ErrorHandler: Application.calculation = xlAutomatic End Sub As always when you play with application level settings it is a good idea to use an error handler to rest things in case of (or more accurately when) a crash. -- HTH... Jim Thomlinson "CLR" wrote: Hi All........... I have this macro repeated 36 times in my workbook, varying the names, etc..........they work fine, except they take a LONG time to execute, with the Status Bar indicating "Calculating Cells", and incrementing every 5%.........can anyone please show me how to speed this up?.....the two Ranges are D8:BC30 and D33:BC46. Sub UpdateSkeletonCrewX() Worksheets("SkeletonCrewX").Select ActiveSheet.Unprotect Range("FieldSkeletonCrewX1").ClearContents Range("FieldSkeletonCrewX2").ClearContents Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX1").Formula = "=IF(ISNA(VLOOKUP(($C8&""_""&D$1&""_""&D$4),Master Namefile!$B:$S,14,FALSE)), """",VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamef ile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX2").Formula = "=IF(ISNA(VLOOKUP(($C30&""_""&D$1&""_""&D$4),Maste rNamefile!$B:$S,14,FALSE)) ,"""",VLOOKUP(($C30&""_""&D$1&""_""&D$4),MasterNam efile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("c3").Formula = "=now()" Range("C3").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Range("a5").Select End Sub TIA Vaya con Dios, Chuck, CABGx3 |
Very slow macro
Thanks Gary, good idea, I'll give it a ponder...........
Although I'm just using two ranges in the macro to add the formulas to, they consist of about 1400 cells.......I would have to be able to add the equal sign to all the cells in a range........ But it actually seems like it's the "Calculation" that's taking all the time and it only seems to run once at the end......even with my original code.......... Vaya con Dios, Chuck, CABGx3 "Gary Keramidas" wrote in message ... don't know if will work in your case, but i have created the formula in each cell without the = sign. then i go back and add the = sign when i'm done so it calculates a lot less times. so, if the formula in A3 was =A1+A2, i would put A1+A2 in the cell. after i created all of the text for the formulas, i would go back and add the = sign then use the code: Range("A3").Formula = "='" & Range("A3").Formula like i said, it cut my code run times on one spreadsheet from 3 minutes to 30 seconds. obviously i had more elaborate formulas than this, just giving an example. -- Gary "CLR" wrote in message ... Thanks Jim, but I must have done something wrong.......it takes longer now........ Sub UpdateSkeletonCrewX() On Error GoTo ErrorHandler Application.Calculation = xlManual Worksheets("SkeletonCrewX").Select ActiveSheet.Unprotect Range("FieldSkeletonCrewX1").ClearContents Range("FieldSkeletonCrewX2").ClearContents Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX1").Formula = "=IF(ISNA(VLOOKUP(($C8&""_""&D$1&""_""&D$4),Master Namefile!$B:$S,14,FALSE)), """",VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamef ile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX2").Formula = "=IF(ISNA(VLOOKUP(($C30&""_""&D$1&""_""&D$4),Maste rNamefile!$B:$S,14,FALSE)) ,"""",VLOOKUP(($C30&""_""&D$1&""_""&D$4),MasterNam efile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("c3").Formula = "=now()" Range("C3").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Range("a5").Select ErrorHandler: Application.Calculation = xlAutomatic End Sub Vaya con Dios, Chuck, CABGx3 "Jim Thomlinson" wrote in message ... Every time a cell is changed the entire workbook must recalculate. To get around this you can turn the caluclations off while the macro runs. The same as tools-options-Calculation- manual sub DoSomeStuff on error goto ErrorHandler Application.calculation = xlManual 'Do your stuff ErrorHandler: Application.calculation = xlAutomatic End Sub As always when you play with application level settings it is a good idea to use an error handler to rest things in case of (or more accurately when) a crash. -- HTH... Jim Thomlinson "CLR" wrote: Hi All........... I have this macro repeated 36 times in my workbook, varying the names, etc..........they work fine, except they take a LONG time to execute, with the Status Bar indicating "Calculating Cells", and incrementing every 5%.........can anyone please show me how to speed this up?.....the two Ranges are D8:BC30 and D33:BC46. Sub UpdateSkeletonCrewX() Worksheets("SkeletonCrewX").Select ActiveSheet.Unprotect Range("FieldSkeletonCrewX1").ClearContents Range("FieldSkeletonCrewX2").ClearContents Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX1").Formula = "=IF(ISNA(VLOOKUP(($C8&""_""&D$1&""_""&D$4),Master Namefile!$B:$S,14,FALSE)), """",VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamef ile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX2").Formula = "=IF(ISNA(VLOOKUP(($C30&""_""&D$1&""_""&D$4),Maste rNamefile!$B:$S,14,FALSE)) ,"""",VLOOKUP(($C30&""_""&D$1&""_""&D$4),MasterNam efile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("c3").Formula = "=now()" Range("C3").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Range("a5").Select End Sub TIA Vaya con Dios, Chuck, CABGx3 |
Very slow macro
Hi Chuck,
I made these changes... ScreenUpdating = False Changed the Range names to the range address ... (so I wouldn't have to create the named ranges). Added a timer and msgbox in two places Simplified the Now function code In xl97, it took about 3.7 seconds to add the formulas and the same length of time to do the calculation. Of course, my "MasterNameFile" sheet is blank. Maybe you can play with it to see how the time is allocated on your machine. (divide the time by 1000 to get seconds) Regards, Jim Cone San Francisco, USA '---------------------- Private Declare Function timeGetTime Lib "winmm.dll" () As Long Sub UpdateSkeletonCrewX() Dim TimeStart As Long Dim TimeStop As Long Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With Worksheets("SkeletonCrewX") .Unprotect .Range("D8:BC30").ClearContents .Range("D33:BC46").ClearContents TimeStart = timeGetTime .Range("D8:BC30").Formula = "=IF(ISNA(VLOOKUP(($C8&""_""&D$1&""_""&D$4),Master Namefile!$B:$S,14,FALSE)),"""",VLOOKUP(($C8&""_""& D$1&""_""&D$4),MasterNamefile!$B :$S,14,FALSE))" .Range("D33:BC46").Formula = "=IF(ISNA(VLOOKUP(($C30&""_""&D$1&""_""&D$4),Maste rNamefile!$B:$S,14,FALSE)),"""",VLOOKUP(($C30&""_" "&D$1&""_""&D$4),MasterNamefile! $B:$S,14,FALSE))" TimeStop = timeGetTime MsgBox "Formulas took " & TimeStop - TimeStart .Range("c3").Value = Now Application.CutCopyMode = False .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True .Range("a5").Select End With TimeStart = timeGetTime Application.Calculation = xlCalculationAutomatic TimeStop = timeGetTime Application.ScreenUpdating = True MsgBox "Calculation took " & TimeStop - TimeStart End Sub '------------------------------- "CLR" wrote in message ... Thanks Gary, good idea, I'll give it a ponder........... Although I'm just using two ranges in the macro to add the formulas to, they consist of about 1400 cells.......I would have to be able to add the equal sign to all the cells in a range........ But it actually seems like it's the "Calculation" that's taking all the time and it only seems to run once at the end......even with my original code.......... Vaya con Dios, Chuck, CABGx3 |
Very slow macro
i may be wrong, but i think even though calculation is off, when you enter a
formula, it calculates that cell. so, it is still calculating every formula creation. i'd be curious to know if that helped you as much as it helped me. i have formula that links to 20 sheets. the formula changes to access different cells in the 20 sheets 9 more times. so there are 10 formulas accessing 20 linked sheets for each day of the month. that's a lot of calculating. that's why my code execution time was cut down so much. and yes, i had calc mode set to manual, but it still had to build each formula and calc the cell as it built it. what i ended up doing, was just building the formula for the 1st row, and autofilling down because every cell in the linked sheet was 1 below the other just like in my summary sheet. that save time, too, since i didn't have to build formulas for each day. -- Gary "CLR" wrote in message ... Thanks Gary, good idea, I'll give it a ponder........... Although I'm just using two ranges in the macro to add the formulas to, they consist of about 1400 cells.......I would have to be able to add the equal sign to all the cells in a range........ But it actually seems like it's the "Calculation" that's taking all the time and it only seems to run once at the end......even with my original code.......... Vaya con Dios, Chuck, CABGx3 "Gary Keramidas" wrote in message ... don't know if will work in your case, but i have created the formula in each cell without the = sign. then i go back and add the = sign when i'm done so it calculates a lot less times. so, if the formula in A3 was =A1+A2, i would put A1+A2 in the cell. after i created all of the text for the formulas, i would go back and add the = sign then use the code: Range("A3").Formula = "='" & Range("A3").Formula like i said, it cut my code run times on one spreadsheet from 3 minutes to 30 seconds. obviously i had more elaborate formulas than this, just giving an example. -- Gary "CLR" wrote in message ... Thanks Jim, but I must have done something wrong.......it takes longer now........ Sub UpdateSkeletonCrewX() On Error GoTo ErrorHandler Application.Calculation = xlManual Worksheets("SkeletonCrewX").Select ActiveSheet.Unprotect Range("FieldSkeletonCrewX1").ClearContents Range("FieldSkeletonCrewX2").ClearContents Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX1").Formula = "=IF(ISNA(VLOOKUP(($C8&""_""&D$1&""_""&D$4),Master Namefile!$B:$S,14,FALSE)), """",VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamef ile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX2").Formula = "=IF(ISNA(VLOOKUP(($C30&""_""&D$1&""_""&D$4),Maste rNamefile!$B:$S,14,FALSE)) ,"""",VLOOKUP(($C30&""_""&D$1&""_""&D$4),MasterNam efile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("c3").Formula = "=now()" Range("C3").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Range("a5").Select ErrorHandler: Application.Calculation = xlAutomatic End Sub Vaya con Dios, Chuck, CABGx3 "Jim Thomlinson" wrote in message ... Every time a cell is changed the entire workbook must recalculate. To get around this you can turn the caluclations off while the macro runs. The same as tools-options-Calculation- manual sub DoSomeStuff on error goto ErrorHandler Application.calculation = xlManual 'Do your stuff ErrorHandler: Application.calculation = xlAutomatic End Sub As always when you play with application level settings it is a good idea to use an error handler to rest things in case of (or more accurately when) a crash. -- HTH... Jim Thomlinson "CLR" wrote: Hi All........... I have this macro repeated 36 times in my workbook, varying the names, etc..........they work fine, except they take a LONG time to execute, with the Status Bar indicating "Calculating Cells", and incrementing every 5%.........can anyone please show me how to speed this up?.....the two Ranges are D8:BC30 and D33:BC46. Sub UpdateSkeletonCrewX() Worksheets("SkeletonCrewX").Select ActiveSheet.Unprotect Range("FieldSkeletonCrewX1").ClearContents Range("FieldSkeletonCrewX2").ClearContents Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX1").Formula = "=IF(ISNA(VLOOKUP(($C8&""_""&D$1&""_""&D$4),Master Namefile!$B:$S,14,FALSE)), """",VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamef ile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX2").Formula = "=IF(ISNA(VLOOKUP(($C30&""_""&D$1&""_""&D$4),Maste rNamefile!$B:$S,14,FALSE)) ,"""",VLOOKUP(($C30&""_""&D$1&""_""&D$4),MasterNam efile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("c3").Formula = "=now()" Range("C3").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Range("a5").Select End Sub TIA Vaya con Dios, Chuck, CABGx3 |
Very slow macro
one other thing i do at the end, is do a pastespecial values over the
formulas, so previous months don't have to re-link those long formulas. that data never changes, so being static is fine. even if something happens, the macro only takes a few seconds to regenerate the whole month. -- Gary "Gary Keramidas" wrote in message ... i may be wrong, but i think even though calculation is off, when you enter a formula, it calculates that cell. so, it is still calculating every formula creation. i'd be curious to know if that helped you as much as it helped me. i have formula that links to 20 sheets. the formula changes to access different cells in the 20 sheets 9 more times. so there are 10 formulas accessing 20 linked sheets for each day of the month. that's a lot of calculating. that's why my code execution time was cut down so much. and yes, i had calc mode set to manual, but it still had to build each formula and calc the cell as it built it. what i ended up doing, was just building the formula for the 1st row, and autofilling down because every cell in the linked sheet was 1 below the other just like in my summary sheet. that save time, too, since i didn't have to build formulas for each day. -- Gary "CLR" wrote in message ... Thanks Gary, good idea, I'll give it a ponder........... Although I'm just using two ranges in the macro to add the formulas to, they consist of about 1400 cells.......I would have to be able to add the equal sign to all the cells in a range........ But it actually seems like it's the "Calculation" that's taking all the time and it only seems to run once at the end......even with my original code.......... Vaya con Dios, Chuck, CABGx3 "Gary Keramidas" wrote in message ... don't know if will work in your case, but i have created the formula in each cell without the = sign. then i go back and add the = sign when i'm done so it calculates a lot less times. so, if the formula in A3 was =A1+A2, i would put A1+A2 in the cell. after i created all of the text for the formulas, i would go back and add the = sign then use the code: Range("A3").Formula = "='" & Range("A3").Formula like i said, it cut my code run times on one spreadsheet from 3 minutes to 30 seconds. obviously i had more elaborate formulas than this, just giving an example. -- Gary "CLR" wrote in message ... Thanks Jim, but I must have done something wrong.......it takes longer now........ Sub UpdateSkeletonCrewX() On Error GoTo ErrorHandler Application.Calculation = xlManual Worksheets("SkeletonCrewX").Select ActiveSheet.Unprotect Range("FieldSkeletonCrewX1").ClearContents Range("FieldSkeletonCrewX2").ClearContents Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX1").Formula = "=IF(ISNA(VLOOKUP(($C8&""_""&D$1&""_""&D$4),Master Namefile!$B:$S,14,FALSE)), """",VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamef ile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX2").Formula = "=IF(ISNA(VLOOKUP(($C30&""_""&D$1&""_""&D$4),Maste rNamefile!$B:$S,14,FALSE)) ,"""",VLOOKUP(($C30&""_""&D$1&""_""&D$4),MasterNam efile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("c3").Formula = "=now()" Range("C3").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Range("a5").Select ErrorHandler: Application.Calculation = xlAutomatic End Sub Vaya con Dios, Chuck, CABGx3 "Jim Thomlinson" wrote in message ... Every time a cell is changed the entire workbook must recalculate. To get around this you can turn the caluclations off while the macro runs. The same as tools-options-Calculation- manual sub DoSomeStuff on error goto ErrorHandler Application.calculation = xlManual 'Do your stuff ErrorHandler: Application.calculation = xlAutomatic End Sub As always when you play with application level settings it is a good idea to use an error handler to rest things in case of (or more accurately when) a crash. -- HTH... Jim Thomlinson "CLR" wrote: Hi All........... I have this macro repeated 36 times in my workbook, varying the names, etc..........they work fine, except they take a LONG time to execute, with the Status Bar indicating "Calculating Cells", and incrementing every 5%.........can anyone please show me how to speed this up?.....the two Ranges are D8:BC30 and D33:BC46. Sub UpdateSkeletonCrewX() Worksheets("SkeletonCrewX").Select ActiveSheet.Unprotect Range("FieldSkeletonCrewX1").ClearContents Range("FieldSkeletonCrewX2").ClearContents Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX1").Formula = "=IF(ISNA(VLOOKUP(($C8&""_""&D$1&""_""&D$4),Master Namefile!$B:$S,14,FALSE)), """",VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamef ile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX2").Formula = "=IF(ISNA(VLOOKUP(($C30&""_""&D$1&""_""&D$4),Maste rNamefile!$B:$S,14,FALSE)) ,"""",VLOOKUP(($C30&""_""&D$1&""_""&D$4),MasterNam efile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("c3").Formula = "=now()" Range("C3").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Range("a5").Select End Sub TIA Vaya con Dios, Chuck, CABGx3 |
Very slow macro
Hi Jim............thanks for the effort, but no cigar yet............
In my program, your code functions just like you said (the timer thing is neat),but took nearly 1 minute for each of two cycles. Since I couldn't see what was going on, I remmed out the screen updating lines and saw nearly the same thing that I saw when trying Jim Thomlinson's suggestion, ie: as soon as I fire the macro, the Status line reads "Cell:" with a completely full progress indicator bar for a full 60 seconds, then it reads "Calculating Cells: xx%" and cycles through from 5-100% and that takes nearly 60 seconds as well........ It would appear that my original macro runs quickly enough, it's just the "Calculating" that is taking all the time. What might speed that up, a faster computer?, or newer XL maybe? Vaya con Dios, Chuck, CABGx3 "Jim Cone" wrote in message ... Hi Chuck, I made these changes... ScreenUpdating = False Changed the Range names to the range address ... (so I wouldn't have to create the named ranges). Added a timer and msgbox in two places Simplified the Now function code In xl97, it took about 3.7 seconds to add the formulas and the same length of time to do the calculation. Of course, my "MasterNameFile" sheet is blank. Maybe you can play with it to see how the time is allocated on your machine. (divide the time by 1000 to get seconds) Regards, Jim Cone San Francisco, USA '---------------------- Private Declare Function timeGetTime Lib "winmm.dll" () As Long Sub UpdateSkeletonCrewX() Dim TimeStart As Long Dim TimeStop As Long Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With Worksheets("SkeletonCrewX") .Unprotect .Range("D8:BC30").ClearContents .Range("D33:BC46").ClearContents TimeStart = timeGetTime .Range("D8:BC30").Formula = "=IF(ISNA(VLOOKUP(($C8&""_""&D$1&""_""&D$4),Master Namefile!$B:$S,14,FALSE)), """",VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamef ile!$B :$S,14,FALSE))" .Range("D33:BC46").Formula = "=IF(ISNA(VLOOKUP(($C30&""_""&D$1&""_""&D$4),Maste rNamefile!$B:$S,14,FALSE)) ,"""",VLOOKUP(($C30&""_""&D$1&""_""&D$4),MasterNam efile! $B:$S,14,FALSE))" TimeStop = timeGetTime MsgBox "Formulas took " & TimeStop - TimeStart .Range("c3").Value = Now Application.CutCopyMode = False .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True .Range("a5").Select End With TimeStart = timeGetTime Application.Calculation = xlCalculationAutomatic TimeStop = timeGetTime Application.ScreenUpdating = True MsgBox "Calculation took " & TimeStop - TimeStart End Sub '------------------------------- "CLR" wrote in message ... Thanks Gary, good idea, I'll give it a ponder........... Although I'm just using two ranges in the macro to add the formulas to, they consist of about 1400 cells.......I would have to be able to add the equal sign to all the cells in a range........ But it actually seems like it's the "Calculation" that's taking all the time and it only seems to run once at the end......even with my original code.......... Vaya con Dios, Chuck, CABGx3 |
Very slow macro
Hi Gary.........
I tried what you said, and it filled the Ranges very quickly using only the TEXT version, and added the equal signs relatively quickly too, but unfortunately it put the same formula in every cell in the range instead of stepping them to be appropriate for each cell location.........maybe I'm trying too hard, I'm under the gun to get this thing done, so I guess I better get it done first and worry about the speed thing after the users start complaining.........<g Vaya con Dios, Chuck, CABGx3 "Gary Keramidas" wrote in message ... i may be wrong, but i think even though calculation is off, when you enter a formula, it calculates that cell. so, it is still calculating every formula creation. i'd be curious to know if that helped you as much as it helped me. i have formula that links to 20 sheets. the formula changes to access different cells in the 20 sheets 9 more times. so there are 10 formulas accessing 20 linked sheets for each day of the month. that's a lot of calculating. that's why my code execution time was cut down so much. and yes, i had calc mode set to manual, but it still had to build each formula and calc the cell as it built it. what i ended up doing, was just building the formula for the 1st row, and autofilling down because every cell in the linked sheet was 1 below the other just like in my summary sheet. that save time, too, since i didn't have to build formulas for each day. -- Gary "CLR" wrote in message ... Thanks Gary, good idea, I'll give it a ponder........... Although I'm just using two ranges in the macro to add the formulas to, they consist of about 1400 cells.......I would have to be able to add the equal sign to all the cells in a range........ But it actually seems like it's the "Calculation" that's taking all the time and it only seems to run once at the end......even with my original code.......... Vaya con Dios, Chuck, CABGx3 "Gary Keramidas" wrote in message ... don't know if will work in your case, but i have created the formula in each cell without the = sign. then i go back and add the = sign when i'm done so it calculates a lot less times. so, if the formula in A3 was =A1+A2, i would put A1+A2 in the cell. after i created all of the text for the formulas, i would go back and add the = sign then use the code: Range("A3").Formula = "='" & Range("A3").Formula like i said, it cut my code run times on one spreadsheet from 3 minutes to 30 seconds. obviously i had more elaborate formulas than this, just giving an example. -- Gary "CLR" wrote in message ... Thanks Jim, but I must have done something wrong.......it takes longer now........ Sub UpdateSkeletonCrewX() On Error GoTo ErrorHandler Application.Calculation = xlManual Worksheets("SkeletonCrewX").Select ActiveSheet.Unprotect Range("FieldSkeletonCrewX1").ClearContents Range("FieldSkeletonCrewX2").ClearContents Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX1").Formula = "=IF(ISNA(VLOOKUP(($C8&""_""&D$1&""_""&D$4),Master Namefile!$B:$S,14,FALSE)), """",VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamef ile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX2").Formula = "=IF(ISNA(VLOOKUP(($C30&""_""&D$1&""_""&D$4),Maste rNamefile!$B:$S,14,FALSE)) ,"""",VLOOKUP(($C30&""_""&D$1&""_""&D$4),MasterNam efile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("c3").Formula = "=now()" Range("C3").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Range("a5").Select ErrorHandler: Application.Calculation = xlAutomatic End Sub Vaya con Dios, Chuck, CABGx3 "Jim Thomlinson" wrote in message ... Every time a cell is changed the entire workbook must recalculate. To get around this you can turn the caluclations off while the macro runs. The same as tools-options-Calculation- manual sub DoSomeStuff on error goto ErrorHandler Application.calculation = xlManual 'Do your stuff ErrorHandler: Application.calculation = xlAutomatic End Sub As always when you play with application level settings it is a good idea to use an error handler to rest things in case of (or more accurately when) a crash. -- HTH... Jim Thomlinson "CLR" wrote: Hi All........... I have this macro repeated 36 times in my workbook, varying the names, etc..........they work fine, except they take a LONG time to execute, with the Status Bar indicating "Calculating Cells", and incrementing every 5%.........can anyone please show me how to speed this up?.....the two Ranges are D8:BC30 and D33:BC46. Sub UpdateSkeletonCrewX() Worksheets("SkeletonCrewX").Select ActiveSheet.Unprotect Range("FieldSkeletonCrewX1").ClearContents Range("FieldSkeletonCrewX2").ClearContents Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX1").Formula = "=IF(ISNA(VLOOKUP(($C8&""_""&D$1&""_""&D$4),Master Namefile!$B:$S,14,FALSE)), """",VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamef ile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX2").Formula = "=IF(ISNA(VLOOKUP(($C30&""_""&D$1&""_""&D$4),Maste rNamefile!$B:$S,14,FALSE)) ,"""",VLOOKUP(($C30&""_""&D$1&""_""&D$4),MasterNam efile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("c3").Formula = "=now()" Range("C3").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Range("a5").Select End Sub TIA Vaya con Dios, Chuck, CABGx3 |
Very slow macro
Yeah Gary, I hear ya, but this book has 36 sheets like this and it gets too
big if I leave all the data there and could present an erroneous impression of being current data when changes are being made etc etc........so I just clear the fields with a WorksheetDeactivate macro.........but I DO like your thinking......... Thanks again, Vaya con Dios, Chuck, CABGx3 "Gary Keramidas" wrote in message ... one other thing i do at the end, is do a pastespecial values over the formulas, so previous months don't have to re-link those long formulas. that data never changes, so being static is fine. even if something happens, the macro only takes a few seconds to regenerate the whole month. -- Gary "Gary Keramidas" wrote in message ... i may be wrong, but i think even though calculation is off, when you enter a formula, it calculates that cell. so, it is still calculating every formula creation. i'd be curious to know if that helped you as much as it helped me. i have formula that links to 20 sheets. the formula changes to access different cells in the 20 sheets 9 more times. so there are 10 formulas accessing 20 linked sheets for each day of the month. that's a lot of calculating. that's why my code execution time was cut down so much. and yes, i had calc mode set to manual, but it still had to build each formula and calc the cell as it built it. what i ended up doing, was just building the formula for the 1st row, and autofilling down because every cell in the linked sheet was 1 below the other just like in my summary sheet. that save time, too, since i didn't have to build formulas for each day. -- Gary "CLR" wrote in message ... Thanks Gary, good idea, I'll give it a ponder........... Although I'm just using two ranges in the macro to add the formulas to, they consist of about 1400 cells.......I would have to be able to add the equal sign to all the cells in a range........ But it actually seems like it's the "Calculation" that's taking all the time and it only seems to run once at the end......even with my original code.......... Vaya con Dios, Chuck, CABGx3 "Gary Keramidas" wrote in message ... don't know if will work in your case, but i have created the formula in each cell without the = sign. then i go back and add the = sign when i'm done so it calculates a lot less times. so, if the formula in A3 was =A1+A2, i would put A1+A2 in the cell. after i created all of the text for the formulas, i would go back and add the = sign then use the code: Range("A3").Formula = "='" & Range("A3").Formula like i said, it cut my code run times on one spreadsheet from 3 minutes to 30 seconds. obviously i had more elaborate formulas than this, just giving an example. -- Gary "CLR" wrote in message ... Thanks Jim, but I must have done something wrong.......it takes longer now........ Sub UpdateSkeletonCrewX() On Error GoTo ErrorHandler Application.Calculation = xlManual Worksheets("SkeletonCrewX").Select ActiveSheet.Unprotect Range("FieldSkeletonCrewX1").ClearContents Range("FieldSkeletonCrewX2").ClearContents Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX1").Formula = "=IF(ISNA(VLOOKUP(($C8&""_""&D$1&""_""&D$4),Master Namefile!$B:$S,14,FALSE)), """",VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamef ile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX2").Formula = "=IF(ISNA(VLOOKUP(($C30&""_""&D$1&""_""&D$4),Maste rNamefile!$B:$S,14,FALSE)) ,"""",VLOOKUP(($C30&""_""&D$1&""_""&D$4),MasterNam efile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("c3").Formula = "=now()" Range("C3").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Range("a5").Select ErrorHandler: Application.Calculation = xlAutomatic End Sub Vaya con Dios, Chuck, CABGx3 "Jim Thomlinson" wrote in message ... Every time a cell is changed the entire workbook must recalculate. To get around this you can turn the caluclations off while the macro runs. The same as tools-options-Calculation- manual sub DoSomeStuff on error goto ErrorHandler Application.calculation = xlManual 'Do your stuff ErrorHandler: Application.calculation = xlAutomatic End Sub As always when you play with application level settings it is a good idea to use an error handler to rest things in case of (or more accurately when) a crash. -- HTH... Jim Thomlinson "CLR" wrote: Hi All........... I have this macro repeated 36 times in my workbook, varying the names, etc..........they work fine, except they take a LONG time to execute, with the Status Bar indicating "Calculating Cells", and incrementing every 5%.........can anyone please show me how to speed this up?.....the two Ranges are D8:BC30 and D33:BC46. Sub UpdateSkeletonCrewX() Worksheets("SkeletonCrewX").Select ActiveSheet.Unprotect Range("FieldSkeletonCrewX1").ClearContents Range("FieldSkeletonCrewX2").ClearContents Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX1").Formula = "=IF(ISNA(VLOOKUP(($C8&""_""&D$1&""_""&D$4),Master Namefile!$B:$S,14,FALSE)), """",VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamef ile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("FieldSkeletonCr ewX2").Formula = "=IF(ISNA(VLOOKUP(($C30&""_""&D$1&""_""&D$4),Maste rNamefile!$B:$S,14,FALSE)) ,"""",VLOOKUP(($C30&""_""&D$1&""_""&D$4),MasterNam efile!$B:$S,14,FALSE))" Worksheets("SkeletonCrewX").Range("c3").Formula = "=now()" Range("C3").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Range("a5").Select End Sub TIA Vaya con Dios, Chuck, CABGx3 |
| All times are GMT +1. The time now is 10:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com