Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel is running too fast
Dear Expert,
I need to copy certain fields from one sheet to a sheet called "conso" as a summary page. All duties are easy. Just copy and paste. I preset in macro to use a value "23" in cell A266 ... then formula in other cells in the sheet will run and calculate to give the final figures ... Copy A267:E272 to conso sheet .... Copy M267:M272 to conso sheet .. Copy BP267:BQ272 .... to conso sheet Then, replace the value "27" in cell A266 (same cell), then formula in other cells will run and calculate again .... Repeat the same jobs ...."Copy A267:E272 to conso sheet .... Copy M267:M272 to conso sheet .. Copy BP267:BQ272 .... to conso sheet" Finally, replace "49" in cell A266 (same cell), then formula in other cells will run and calculate again. Same duty ...."Copy A267:E272 to conso sheet .... Copy M267:M272 to conso sheet .. Copy BP267:BQ272 .... to conso sheet" Actually, replacing the values via macro in cell A266 is very easy. I set changing the value 20 times in cell A266. Say. 23,27,49,53,57,89,112 ...etc ..etc ... Do the same things ..."Copy A267:E272 to conso sheet .... Copy M267:M272 to conso sheet .. Copy BP267:BQ272 .... to conso sheet" Look like all should be alright. The problem is that .... I used "Calculate" or "ActiveWorkbook.Calculate" in VB scripts to refresh all formula and thus values before copying over to conso sheet... But in the end, after changing the values 6 times in cell A266, the rest of 14 outcomes are same as 6th value data. I used Application.Wait (now + ("0:00:10")) in order to hold a wait before copying. I found EXCEL was stale rather than refreshing formulas/data.... So what should I write in VB scripts to make sure all cells are fully re-calculated (refresh) whenever I input a new value in cell A266 before I copy something over to conso sheet please ? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel is running too fast
Hi Elton
You should not need to re-calculate after each value change in you controlling cell. A couple of questions: 1. Does the destination range in your conso sheet changes for each iteration of the loop? 2. Are you pasting values in the conso sheet? I'm thinking about formula references to the original (source) range being retained. I can't think of anything else to check at present, but there are too many unknowns here to work out a solution. It sounds to me as if you need to focus on the destination, rather than the source from what you describe. Nick H -- Nick "Elton Law" wrote: Dear Expert, I need to copy certain fields from one sheet to a sheet called "conso" as a summary page. All duties are easy. Just copy and paste. I preset in macro to use a value "23" in cell A266 ... then formula in other cells in the sheet will run and calculate to give the final figures ... Copy A267:E272 to conso sheet .... Copy M267:M272 to conso sheet .. Copy BP267:BQ272 .... to conso sheet Then, replace the value "27" in cell A266 (same cell), then formula in other cells will run and calculate again .... Repeat the same jobs ...."Copy A267:E272 to conso sheet .... Copy M267:M272 to conso sheet .. Copy BP267:BQ272 .... to conso sheet" Finally, replace "49" in cell A266 (same cell), then formula in other cells will run and calculate again. Same duty ...."Copy A267:E272 to conso sheet .... Copy M267:M272 to conso sheet .. Copy BP267:BQ272 .... to conso sheet" Actually, replacing the values via macro in cell A266 is very easy. I set changing the value 20 times in cell A266. Say. 23,27,49,53,57,89,112 ...etc ..etc ... Do the same things ..."Copy A267:E272 to conso sheet .... Copy M267:M272 to conso sheet .. Copy BP267:BQ272 .... to conso sheet" Look like all should be alright. The problem is that .... I used "Calculate" or "ActiveWorkbook.Calculate" in VB scripts to refresh all formula and thus values before copying over to conso sheet... But in the end, after changing the values 6 times in cell A266, the rest of 14 outcomes are same as 6th value data. I used Application.Wait (now + ("0:00:10")) in order to hold a wait before copying. I found EXCEL was stale rather than refreshing formulas/data.... So what should I write in VB scripts to make sure all cells are fully re-calculated (refresh) whenever I input a new value in cell A266 before I copy something over to conso sheet please ? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel is running too fast
Hi Elton
I don't think the time taken in the calculation has any material effect for the symptoms you describe. From a code point of view, the macro will not advance to the next instruction until the previous action has completed. You could add a DoEvents statement between iterations of the loop, but I doubt that that will make any difference. This yields control to the operating system to give it a chance to handle other tasks. You answered my question about values, but not the one about the output range in "conso". How do you change the output destination for each iteration of the loop? Can you give me an example of your code for the copy, say for range A267:E272, please? Nick "Elton Law" wrote: Hi Nick, Actually, copy and paste values from a sheet to conso sheet. But the value in sheet are certainly a complicated formula which takes sometime to calculate. Dependent Variables is in cell A266. When I input a figure in cell A266, calculation will go on and take few seconds to work .... Then I will copy the answer and paste values to conso sheet only. Honestly, duties are clerical and easy. But Excel calculation is too slow or copy/paste is too fast that it takes action even formula hasn't come out the final outcome. So want to have some VB scripts rather than "wait" or "calculate" (as I try before) that can allow Excel to refreash all data before going to next steps in scripts. Thanks "NickH" wrote: Hi Elton You should not need to re-calculate after each value change in you controlling cell. A couple of questions: 1. Does the destination range in your conso sheet changes for each iteration of the loop? 2. Are you pasting values in the conso sheet? I'm thinking about formula references to the original (source) range being retained. I can't think of anything else to check at present, but there are too many unknowns here to work out a solution. It sounds to me as if you need to focus on the destination, rather than the source from what you describe. Nick H -- Nick "Elton Law" wrote: Dear Expert, I need to copy certain fields from one sheet to a sheet called "conso" as a summary page. All duties are easy. Just copy and paste. I preset in macro to use a value "23" in cell A266 ... then formula in other cells in the sheet will run and calculate to give the final figures ... Copy A267:E272 to conso sheet .... Copy M267:M272 to conso sheet .. Copy BP267:BQ272 .... to conso sheet Then, replace the value "27" in cell A266 (same cell), then formula in other cells will run and calculate again .... Repeat the same jobs ...."Copy A267:E272 to conso sheet .... Copy M267:M272 to conso sheet .. Copy BP267:BQ272 .... to conso sheet" Finally, replace "49" in cell A266 (same cell), then formula in other cells will run and calculate again. Same duty ...."Copy A267:E272 to conso sheet .... Copy M267:M272 to conso sheet .. Copy BP267:BQ272 .... to conso sheet" Actually, replacing the values via macro in cell A266 is very easy. I set changing the value 20 times in cell A266. Say. 23,27,49,53,57,89,112 ...etc ..etc ... Do the same things ..."Copy A267:E272 to conso sheet .... Copy M267:M272 to conso sheet .. Copy BP267:BQ272 .... to conso sheet" Look like all should be alright. The problem is that .... I used "Calculate" or "ActiveWorkbook.Calculate" in VB scripts to refresh all formula and thus values before copying over to conso sheet... But in the end, after changing the values 6 times in cell A266, the rest of 14 outcomes are same as 6th value data. I used Application.Wait (now + ("0:00:10")) in order to hold a wait before copying. I found EXCEL was stale rather than refreshing formulas/data.... So what should I write in VB scripts to make sure all cells are fully re-calculated (refresh) whenever I input a new value in cell A266 before I copy something over to conso sheet please ? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros Running Sometimes Slow Sometimes Fast | Excel Discussion (Misc queries) | |||
Need fast help in excel counting | Excel Discussion (Misc queries) | |||
How to get fast using ms-Excel and ms-Word? | Excel Discussion (Misc queries) | |||
OH, I need help fast.(Excel-Adobe) | Excel Discussion (Misc queries) | |||
Code is Running Too Fast | Excel Programming |