Unwanted Calculation

Excel 2003.  I am using a VBA procedure to step through a worksheet
from bottom to top with a for-to loop, looking at the cell in column A
for each row.  If column A contains different text than the previous
cell in column A, I insert a row about that one with text to indicate
what the numbers in each cell refer to.  To speed up the process, I
use Application.Calculation=xlManual before starting the loop.  As the
loop runs, Excel flashes "Ready" and "Calculate" in the statusbar.
Since the statusbar says "Calculate", I presume it really is
recalculating the sheet each time it inserts a row, thereby slowing
down my procedure.  Is Excel really calculating like I think it is?
And, if so, how can I prevent this?  Thanks.
0
Lucky
12/4/2009 3:22:44 PM
excel.programming 6508 articles. 2 followers. Follow

5 Replies
628 Views

Similar Articles

[PageSpeed] 58

Try putting this...

Application.EnableEvents = False

before you start your calculations and reset it with this...

Application.EnableEvents = True

after the calculations are done. However, you should probably include an On 
Error trap and put that last statement in its code block just in case your 
code "errors out" so that you are not left in a non-enabled state 
afterwards.

-- 
Rick (MVP - Excel)


"Lucky" <kingofwhiles@aol.com> wrote in message 
news:dd3ec871-8a64-485b-96de-c4b89c7813e5@j4g2000yqe.googlegroups.com...
> Excel 2003.  I am using a VBA procedure to step through a worksheet
> from bottom to top with a for-to loop, looking at the cell in column A
> for each row.  If column A contains different text than the previous
> cell in column A, I insert a row about that one with text to indicate
> what the numbers in each cell refer to.  To speed up the process, I
> use Application.Calculation=xlManual before starting the loop.  As the
> loop runs, Excel flashes "Ready" and "Calculate" in the statusbar.
> Since the statusbar says "Calculate", I presume it really is
> recalculating the sheet each time it inserts a row, thereby slowing
> down my procedure.  Is Excel really calculating like I think it is?
> And, if so, how can I prevent this?  Thanks. 

0
Rick
12/4/2009 3:33:07 PM
I don't think Excel is calculating, but it sounds like its doing a screen 
refresh.

Try putting
Application.screenupdating=false ' at the start

Application.screenupdating=true ' at the end

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Lucky" <kingofwhiles@aol.com> wrote in message 
news:dd3ec871-8a64-485b-96de-c4b89c7813e5@j4g2000yqe.googlegroups.com...
> Excel 2003.  I am using a VBA procedure to step through a worksheet
> from bottom to top with a for-to loop, looking at the cell in column A
> for each row.  If column A contains different text than the previous
> cell in column A, I insert a row about that one with text to indicate
> what the numbers in each cell refer to.  To speed up the process, I
> use Application.Calculation=xlManual before starting the loop.  As the
> loop runs, Excel flashes "Ready" and "Calculate" in the statusbar.
> Since the statusbar says "Calculate", I presume it really is
> recalculating the sheet each time it inserts a row, thereby slowing
> down my procedure.  Is Excel really calculating like I think it is?
> And, if so, how can I prevent this?  Thanks.
> 


0
Charles
12/4/2009 3:51:57 PM
Thanks, Rick and Charles.  I did disable events and screen updating,
but to no avail.  So, I guess this will just remain a mystery.  I will
look at the calculation site.
Lucky
0
Lucky
12/4/2009 4:39:06 PM
A last note on this.  I looked at the calculation site, and it
mentioned turning off worksheet.enablecalculation, so I tried that.
That eliminated the problem.  Thanks again!
0
Lucky
12/4/2009 4:55:57 PM
Hi

I think excel is not evaluating the formula but re-constructing the
formula as every time you delete/insert a row, CELL ADDRESSES CHANGE.

:)

Hemant Hegde
0
Hemant
12/5/2009 8:37:39 PM
Reply:

Similar Artilces:

Unwanted Attachments
Use OE6 (plus IE6) and last nite started receiving all emails with a ..eml attachment which when opened is in Firefox (not primary browser). Inbox Attachment column does not reflect the existence of an attachment however opened attachment displays: file:///C:/Documents%20and%20Settings/Owner/Local%20Settings/Temporary%20Internet%20Files/Content.IE5/67URGHCX/ATT00080.htm (please note the IE5 stuff) Also suddenly find keyboard arrows will not function other than right and up. Scroll Lock is not on. Non-tech type seeks suggestions. Clear the Temporary Internet Files: F...

hours and minutes calculation
does anyone know how to make a spread sheet formulae to calculate hours and minutes, which reports in jours and minutes? Hi Charlie! Try: =INT(B1-A1)&" jours "&ROUND(MOD(B1-A1,1)*(24*60),0)&" minutes " I've rounded the minutes to the nearest minute. -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au ...

Calculation of Credit Card %
Let's say a balance of 1500.00 at 12.99% how do we calculate the FINANCE CHARGES on XL? TX. What do you want to know? -- Kind regards, Niek Otten Microsoft MVP - Excel "Trainee" <Trainee@discussions.microsoft.com> wrote in message news:F246DE4B-EE72-4015-8D50-B4ADCA3E6BCC@microsoft.com... > Let's say a balance of 1500.00 at 12.99% how do we calculate the FINANCE > CHARGES on XL? > TX. If A1 contains 1500.00 and B1 the 12.99 in C1 input =A1*B1% -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful...

Calculating the Difference in a Query
I need to calculate the difference between two fields in a query. The query is called “qryRemote” and within the query are the fields “10-8Time” and “10- 97Time”. The data entered into these fields are in the format “0000” through to “2400”. I need to subtract the “10-8Time from the “10-97Time” and then sum the results and display the result in a report. For example, after the query is run the results are: 10-8Time 10-97Time --------------------------------- 1307 1331 1858 1909 2018 2023 First the difference is determined: 10-8Time is subtracted form t...

Sum Calculation Incorrect
Hi, I've got a problem that I don't even know where to start for fixing it. I have a Query, qryPortfolio which has a field MVbase. Another field Type can be 'Equities' 'Cash' or 'Options'. If I filter on Options and group everything to just get one figure for the sum of MVbase, it comes up with a figure that is incorrect, as in, if I export the data to excel, excel comes up with the correct sum but access won't. It seems it's leaving out certain numbers (though I can't narrow it to exact records...) all the numbers involved are doubles so I can'...

Removing Unwanted Cells
Hi all, I've inhereted a spreadsheet off a colleague, and there are LOTS of empty rows with formatting but no information (the rows with data go down to 240, but I actually have around 4,000 rows!). I've been able to remove the formatting, but actually reducing the number of rows is another thing all together. I've tried simly going to the bottom, selecting the empty rows and *Right-Click*>Delete, and also *Right-Click*>Clear Contents. Neither of which seem to work. This is really a problem of aesthetics, it would just be nice to lean up the spreadsheet a bit. Any ideas?...

MS Word French Calculations
I have some documents which do calculations. These were prepared in MS Word 2000. The construction is Tables and fill in fields. The formulas are written to produce results. When I try to use the same in Word 2007 French Edition it gives all kinds of error messages. 1) Does French edition require formulas to be written in some different syntax? 2) In general Word 2007 uses different syntax? Thanks Nihar Mody -- Nihar Mody ------------------------------------------------------------------------ Nihar Mody's Profile: http://forums.techarena.in/members/183443.h...

Time calculation for a givenn period
Hello everyone, Please consider the following scenario: I have a set of resource for which the total resource hours remaining (A1) for a year (Jan to Dec) has been calculated by subtracting the scheduled hrs. from the total hrs. Lets say I have project start date as "May 1, 2005' and end date 'April 30, 2006' and from this I can find the time in hrs between these two dates. Now I have to find the resource availability for this period. I know the resource availability for the present year (A1) (that is from May 1 to Dec 31). But I need to know the resource availability ...

Calculating latest running 3 month total
Hi everyone, It's my first time posting so I hope I am asking a not too basic question...I tried searching for previous posting relating to this but had no luck. My question is, I have a worksheet with months starting in July 07 - June 08. Each month, as data become available, a new month's of data will be added to the worksheet. If possible, I'd like another column in my worksheet to always calculate the lastest three months. For the first couple of months, July 07 and August 07, as there will be less than three month's worth of data, it would only calculate what's ...

Calculating ranges
Hello, I require a formula that brings a result based on 2 different calculations. First I need to calculate a dollar amount for weights between the ranges of 0 to 500 lbs, 501 to 1000 lbs, 1001 to 2000 lbs and so on up the scale. I will have a column for each range. The second part of the calcuation is that the end result can't be less than $18 or more than $200. Here's a table that might help: Weight 500 1000 2000 470 $19.10 750 $35.00 1500 $50.00 220 $18.00 Any help will ...

Calculating diff between date/ time only between certain hours 02-24-10
I am trying to calculate the difference in hours between a date/time range but only the hours in a certain range. 1/27/10 4:49 1/27/10 11:32 I am trying to find the diff in hours only between 6:00 and 23:59, any hours that fall outside of that time frame should not be counted. Each calculation will have different dates so if there is a way to do this without specifying the date that would be great! GMv1 - If your start date is in A4 and your end date is in B4, then you can do this: =MOD(B4,1)-IF(MOD(A4,1)<0.25,0.25,MOD(A4,1)) This works becase date/times are stor...

Formula to calculate pro-rate + 1 month
Hi all, I'm not an Excel expert but I am wondering how I might accomplish the following. I work at an ISP and lets assume a customer wants our 256 x 256 package which is $31.00 month. Obviously, if they sign up on say the 10th of the month, our billing system bills them the 10th - End of Month + 1 FULL MONTH. I'm wondering how I can put say $31.00 + $22.95 (phone service charge per month) into a formula and have it figure out the pro-rate depending on which date I use + the 1 full month? Make sense? I need a formula to calculate pro-rate (so let's use the 10th) 10th through e...

Stamp Duty, calculator refund
E Stamp duty is owned and operated by We Share home loans a refund home loan referral service. Up to $2500 in cash back refunds on your home loan for applying directly with your bank.http:// www.estampduty.com.au/ ...

Time problem, Auto calculation???
Hello all, I'm trying to create my own time sheet. Inputing 24 hr time format works just fine. Format is subtract morning(start) from afternoon(end). yeilds the correct results. Problem, I'd like to input standard 12 hr time. I can't get this to work correctly. Is there a formula to convert 12hr time to 24hr time? TIA Frantic3D Hi! Not sure what you're asking.... If you want the result to be a decimal value 8 vs 8:00 =(your_formula)*24 Biff "Frantic3d" <thuffner3@yahoo.com> wrote in message news:Xns9684E3281978CFrantic3d@65.24.3.143... > Hello al...

Using SUM to calculate values of cells containing formulae
Hi I'm new to this forum so "Hello" to everybody. I've had a look aroun the forum but I can't seem to find the answer to my problem. I'm sur it must be possible but I've tried everything I know short of writing Macro. I hope one of you wonderful people will be able to help, I can' believe I am the first person to come up against this problem Here's the problem (somewhat simplified) ; I have a column of cells containing the following formulae Cell 1 (K2) =VLOOKUP(I2,$A2:$E34,5) Cell 2 (K3) =VLOOKUP(I3,$A35:$E103,5) Cell 3 (K4) =VLOOKUP(I4,$A104:$E176,5...

Inserting a calculated field in a Pivot Table in Excel 2003
In the past I know I have done this but I can't see the option now to insert a calculated field. Does someone know where this is located? Thanks so much. Disregard. I figured it out. Thanks to anyone who answers before reading this. "Nadine" wrote: > In the past I know I have done this but I can't see the option now to insert > a calculated field. Does someone know where this is located? Thanks so much. ...

calculate number of years
the website says to calculate number of years type: =year(A3)-year(A2). when i type this is i get a weird answer. problem is not that i get the number signs....i get a date 1900. for example: 1/1/1900 0:00. please advise for this is for homework, university. thank you to whomever responds. D L Barnard Format the cell for General or Number, not Date. Or, you can use the DATEDIF function: =DATEDIF(date1,date2,"y") See www.cpearson.com/excel/datedif.htm for more information about DATEDIF. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC ...

S- Curve Calculation and chart
Dear All, I have to prepare S-curve for the Project with following informarion Project Duration : 30 Months Project Cost : 90,000,000 Note : I tried Total Cost / Duration = Monthly work done amount I need calculation based on S- Curve Please do the need ful at the earliest. Wish you all a very happy New year-2010 Background: http://www.visitask.com/s-curve.asp Now, try this: http://www.ozgrid.com/forum/showthread.php?t=32857 Or this: http://leeds-faculty.colorado.edu/Lawrence/Tools/SCurve/scurve.htm This may work for you too: http://farestplanner.wordpress.com/2009/...

Average lead time calculation #2
Scenario - we have an item on a PO with an average 30 day lead time and three receipts against PO. The first receipt is 15 days early, the second receipt is on time, and the third receipt is 15 days late (all based on promise date and actual receipt date). In this scenario, does GP average all the receipts for a an average lead time of 30 days or does it weight late receipts heavier than early ones? Thanks, Frank Hamelly NOVA Solutions LLC ...

How can I calculate overtime?
I am trying to make a timesheet that will allow the user to enter their time and will add all of the hours worked into categories. I have gotten to the point that time can be entered and it adds, but I can not get the formula that will allow me to generate the hours in the correct categories. This is what it looks like: Day Sunday Monday Tuesday Date 8/21/2005 8/22/2005 8/23/2005 Time In 08:30 Time Out 18:00 Other Out 12:30 Other In 13:00 Total hours 00:00 08:00 09:00 Overtime Vacation Personal 08:00 Regular 09:00 Total Hours 00:00 08:00 09:00 The bottom half I w...

calculating a minimum fee
I'm looking for a formula that will calculate the following information: If 15% of the sum of a range of cells is less than $40.00 then the fee is $40.00. If 15% of the sum of a range of cells is $40.00 or higher, then the fee is 15%. I hope that made sense. In other words, the fee is 15% or $40, whichever is higher. Hi Look at this: =MAX(A1*0.15,40) Regards, Per "Jackie" <Jackie@discussions.microsoft.com> skrev i meddelelsen news:5B6BA4D3-CBBE-4C8A-A7AF-A2409C6799F2@microsoft.com... > I'm looking for a formula that will calculate the...

Calculations not keeping up with macro
Hello, I have a spreadsheet with 51 tabs. Tab_1 is a summary of data contained on the remaining 50 sheets (such as averages and sums). A macro iterates through values of 1 - 100. During each iteration a variable with dependents changes on each of the 50 sheets and resulting data for each iteration is output on the summary tab (output such as the averages and sums for each iteration). The macro works great when I step through it, the problems occur when I run it at full speed. The output data at each iteration does not keep up with the macro, thus the outputs are inaccurate. I...

Calculated control #Name? error
I have simple form with a control source of a data table. On the form I have a number of text boxes with calcuated controls for example: Name: txtTotalFTE Format: Fixed Control Source=[DirExLaborNbr]+[DirTempLaborNbr]+[IndSanNbr]+ [IndFLTNbr]+[IndLeadNbr]+[IndQANbr] This works fine, correctly showing the total. In another text box I have the following but the result is #Name?: Name: txtTotalHrs Format: Fixed Control Source=[txtTotalFTE]*8 I have several other text boxes where I need to reference other calculated controls (with much more complex calculations than this one) but I always ge...

Pivot Table
I have a Pivot Table with the following format: ALMACEN ARTCOD MOVENTSAL Total 9 S 11.36 S 209.48 E 79.45 E 55 S 137.88 I need the result of Total S - Total E. Can I do this with a calculated field? Thanks in advance Regards, Emece.- Post some source data... -- HTH... Jim Thomlinson "Emece" wrote: > I have a Pivot Table with the following format: > > ALMACEN > ARTCOD MOVENTSAL Total > 9 S 11.36 > S 209.48 > E 79.45 > E 55 > S 137.88 > > I...

Calculating or estimating the area between to curves
I have seen some great posts about colouring the area between 2 lines but I need to calculate numerically the area between 2 lines. A close estimate would do. My curves are smoothed lines but they have many roots to fit a trendline whose equation I could have integrated. If I use straight lines between points I have too big an error. Even Simpsons Rule is not accurate enough at times. Is there a way that Excel can return a figure for the area under a curve - I could even use the number of pixels it has shaded between the 2 lines! Hello, Broadband! You wrote on Sun, 13 Aug 2006 01:51:...