# Takes Too Long ‘calculating Cells’

Aug 3, 2006
This code is taking way too long to display the actions that it executes. It didn’t used to be that way. I was wondering if anyone knows why this may be. The Excel file is large – over 8 MB.

Private Sub Worksheet_Change(ByVal Target As Range)

Const WS_RANGE As String = "N:N"

Dim Cmnt

On Error Goto ws_exit:

Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

If .Row > 3 Then

If Me.Cells(.Row, "N").Value = "" Or Me.Cells(.Row, "N").Value = "O" Or Me.Cells(.Row, "N").Value = "H" Then

Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 0

End If

If Me.Cells(.Row, "N").Value = "C" And Me.Cells(.Row, "O").Value = "DR" Then

Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 39

End If

If Me.Cells(.Row, "N").Value = "C" And Me.Cells(.Row, "O").Value = "HJB" Then

Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 6

End If.....................................

View 3 Replies
ADVERTISEMENT
Mar 10, 2004

Any code I can stick into the end (plus maybe start) of a macro to display the length of time it took for the macro to run?

View 9 Replies
View Related
Jan 20, 2014

[URL]

Excel takes about 10 minutes in the saving process. When I say 10 minutes, I mean, the excel screen freezes (says not responding) for about 10 minutes, then it actually saves at the very end in the normal time any other file would take as you watch the progress bar go forward.

I know many of the common answers and have tried. reducing the calculation time (which in turn reduces the saving time).

But in my circumstance, the calculation takes a very reasonable amount of time, and you see the progress % going forward.

- I would say I have about 2000 rows, and 15 columns.

- They have sumifs formulas.

- They link to a different workbook.

- The workbook I am working on saves to the network

- the source of my sumifs are also in the same folder on the network

- the recalculation takes about 10 seconds at most

- i have turned off recalculate before saving, it is all on manual calcs

- when i hit save, there are no calcs being performed

- there are no macros in the workbook

- there are only about 2 names in the name manager

- then it freezes for about 10 minutes.

- then the progress bar starts moving then it saves.

What is it doing in those 10 minutes?

1 more item to note, when I break the links to the workbook and thereby removing the sumifs formulas, its a snap.

Why does the existence of the sumifs extend saving time? I would completely understand if it elongated calculation time, but if calculation is off, then why does it even worry about it when saving?

View 4 Replies
View Related
Feb 12, 2010

I am using the below formula to distinctly count the number of customers that match the criteria that I have in Cells C7 and B10. The data is in a separate worksheet, that I am showing Named Detail of which will be changing on a monthly basis, so a pivot table does not want to be used. The detail data ranges from row 7-40,000, and the file is currently 8610KB's, and can potentially grow.

=SUM(IF(FREQUENCY(IF(Detail!$A$7:$A$40000=C$7,IF(Detail!$B$7:$B$40000=$B10,IF(Detail!$D$7:$D$40000<> "",MATCH(Detail!$D$7:$D$40000,Detail!$D$7:$D$40000,0)))),ROW(Cust)-MIN(ROW(Detail!$D$7:$D$40000))+1),1))

This formula works but takes an excessive amount of time for one caluclation, and I need this for multiple column and row critera. So, can this calculation be changed in order to get the same result with faster calculation time? I am using Excel 2003.

View 6 Replies
View Related
May 19, 2008

I have a large Excel 2007 file, around 60.000KB. 54.000KB are due to one of the worksheets where I have 8760rows x 160columns with data. The calculation time is not a problem, it is very fast, it only takes 2/3 seconds. The problem is when I open or save the file, it takes around 2 minutes... it is not too much, but it becomes too long when one has to open and save it several times. It there any trick to decrease the time when openning or saving an excel file??

View 3 Replies
View Related
Aug 26, 2008

I managed to put together an array formula to calculate the last date that a rep made a sale. It checks two other tabs in the workbook to find the date, and if none is found, it leaves the cell empty.

{=IF(MAXA(IF('Daily Compliance'!A:A=B48,'Daily Compliance'!O:O),IF('Daily Compliance 11-07 to 4-08'!B:B=B48,'Daily Compliance 11-07 to 4-08'!A:A))=0,"",MAXA(IF('Daily Compliance'!A:A=B48,'Daily Compliance'!O:O),IF('Daily Compliance 11-07 to 4-08'!B:B=B48,'Daily Compliance 11-07 to 4-08'!A:A)))}

But it takes sometimes up to 4-5 minutes to make the calculations. Is there possibly a way to simplify it so that it calculates faster, with the same results?

View 3 Replies
View Related
Feb 4, 2013

I have this one query though in regards to loops.

I am trying to create a macro that can take the average of the the first 24 cells within a sheet, place the answer onto a cell in the next sheet (e.g. sheet2 in cell A1), then go back to the previous sheet, take the average of the next 24 cells within the sheet and paste the average of this new set in A2. I want to create a loop that will do this 365 times.

I have only managed to create the following code, however its only obtainning the average for the first set of 24 cells starting from B6 in sheet 1. I dont know how to use offsets that well....

VB:

Sub Oval1_Click()

For i = 1 To 365

Sheets("H1 - Riser Turret pressure").Select

Range("B4").Select

ActiveCell.FormulaR1C1 = "=AVERAGE(Sheet1!R[2]C:R[25]C)"

Range("B4").Offset(1, 0).Select

Next i

End Sub

View 6 Replies
View Related
May 15, 2008

I want to obtain from some elements the number of cells it takes to appear:

We have for example A,B,C, and D,

and they appear in the next order:

1A

2C

3D

4A

5D

6B

7C

8A

9A

What I want to know is how much last in appear each element.

1A1

2C2

3D3

4A3

5D2

6B6

7C5

8A4

9A1

For example, the first “A” last one in appear, but the next element “C” last two in appear. In the forth line again cames the “A”, then are three cells. The “C” was in the cell2, and cames again in the seventh cell, then it takes five cells. In the cells eight and nine are two “A”, then in the cell nine takes one cell in appear again.

View 10 Replies
View Related
Mar 30, 2013

I have a spreadsheet of barcodes 114,618 rows long. I have a program that requires me to enter in each and every barcode in a certain format. This is the format that it requires me to place the barcodes into.

23423432, 23432342, 234324323, 234322344, 432432432, .............

so and and so forth.

Only problem is my POS system exports the barcodes in a format that looks like this:

545554545435

345435435435

354543534534

354534545453

Now I have a Macro to add a comma to the end of these barcodes. That part was a breeze, however, I have hit a brick wall when it comes to placing them into an ongoing sentence type format, that my program needs. I have found several ways to combine and merge and "justify" the content, however these typically only cover small amounts of content, and doesn't even come close to hitting anything over 100,000. I don't know if maybe I need to be using another program in tandem with Excel.

View 3 Replies
View Related
Feb 24, 2011

- In column A, I have list of items that take up 700 cells (i.e. the text is in cells A1, A2... A700).

- I have applied conditional formatting to these 700 cells, so some of them are now highlight YELLOW based on a criteria (i.e. cells A3, A14, A422, A654 are yellow)

- I want to create a list of these yellow cells in column B.

View 5 Replies
View Related
Jan 8, 2013

How do i separate example: #EM/13/10000749#JTDEl_1234#abc-NORTH#1##No Traffic Data into separate cells like 0749 to A1 , JTDEl_1234 to A2 , abc-NORTH to A4 , 1 to A7 , No Traffic Data to A5

And then continued with another text of the same standard to the next row by just copying from "Notepad" and pasting it in excel.

All the text is following the same standard.

Example :

#EM/13/10000749#JTDEl_1234#abc-NORTH#1##No Traffic Data

#EM/14/10000730#JTDEl_1256#abc-SOUTH#3##No Connection

#EM/09/10001683#JTDEF_5674#abc-EAST#2##Low Data

View 2 Replies
View Related
Jan 4, 2014

how to add alternating cells when all are numbers, e.g. B8 to KW8 are all numbers and I want to add B8, D8, F8, etc. until KV8 and then separately add C8, E8, G8, etc until KW8. Is there a more efficient way to do this than =sum(b8,D8, etc)? I also need to do the same with =counta(b8, d8, etc).

View 2 Replies
View Related
May 8, 2012

I need a formula that will search the range D8-D100 to confirm that all cells within that range are either 11 characters in length or blank. I will use it inside of an error message that will look something like this:

=IF(****formula that checks to make sure all of the cells in that range are blank or 11 characters****=TRUE, "", "Please make sure that all cells are 11 digits or blank")

View 7 Replies
View Related
May 15, 2007

I have a large spreadsheet, within which i am trying to remove commas from all cells. I get the error 'formula is too long' when I carry out the search. Some of the cells are >1024 characters in length and contain dates, text etc.

View 5 Replies
View Related
Oct 19, 2006

I have two conditions setup in Options>View - Zero Values.", "style="background: #FFFFFF;padding: 2px;font-size: 10px;width: 550px;"");' onmouseout='GAL_hidepopup();'>formatting.htm" target="_blank">conditional formatting. The first is setup for alternate row coloring with this formula inside Conditional Formatting:

Formula is = MOD(ROW(),2)

My Second Condition is

Cell Value is between $P$10 and $Q$10. This sets the font bold and a different color. The two cell values are two dates. I want to change the cells font color and bolding as long as the value is within that date range. It works fine, but for cells that are on the row that is colored the second condition doesn't apply for some reason.

View 7 Replies
View Related
Oct 17, 2008

Code:.....

I am constantly editing this (we currently have over 100 accounts) and therefore the totals are changing.I have a formula for Total but I need formulas for the other two, based on when the cells in columns F and J are blank or have dates in them: For active, the total is the sum of all numbers in column M but only when there is a date in column F and a BLANK in column J. For yet to enter, the total is the sum of all the numbers in column M but only when both column F and column J are blank. At the moment, my accounts run from row 6 to row 142, with the first line of totals in row 145, however this is constantly expanding.

View 4 Replies
View Related
Mar 6, 2008

I have a bit of an odd issue I need to solve. I use an Excel spreadsheet to track daily income for the store over a months' period. I have each day set to give me a rough estimate of profitability for that day. I also want to create a cell that tells me how much we've made so far, based on the number of business days so far in the current month. For example:

Day 1, $1500, $800 in expenses (monthly expenses averaged over the month, plus labor costs for the day), so a total of $700 profit.

Day 2, $1700, $749 in expenses (same as before), so a total of $951 profit.

The formula I'm using to calculate that isn't an issue, but I have the same formula for every single day, and each day has it's own cell with the estimated profitability for that particular day. In two other cells, I put the total number of business days in that month (in this case, 26), and in another cell I put the current number of business days we've had this month (in this case, 4), which is used partially to calculate the other cells. I want to use them to create another cell to contain the estimated profitability based solely on the number of business days out of the month we've had.

So E3 through E33 contain the individual days, I want to calculate - since we've only had 4 business days - what E3 through E7 would total. When I change it to 5 business days, i want it to calculate E3 through E8. So if we've had 4 business days, it only calculates the totals from the first 4 cells, if 5 business days, then the first 5 cells, etc, etc.

I can sort of picture it in my head, but I can't seem to find the necessary equation for it. It may require a macro, but I'd rather use a formula if possible. I'm not a big fan of macros.

View 9 Replies
View Related
Nov 9, 2008

I am having a problem with some of the cells in a spreadsheet not calculating.

For example, in column A, all the cells have formulas that pull data from another sheet. When I enter the formula, only the formula shows (not the result). I can't figure out what is wrong as the formulas in column B work fine (the results are showing, but not using the same data that column A is).

I am thinking it may have something to do with the data being used to calculate the formulas but not sure. How can I resolve this?

View 7 Replies
View Related
Apr 1, 2009

I have the workbook set to Auto Calc the Cells, which seems to be a bad idea, because with the amount of formula in the workbook, it slows the whole thing down and makes it impossible to do anything within it.

Is there a way that I can speed this process up, using some code when I run certain sections?

There is a copy of what I am doing, so you can see the amount of formula.

View 13 Replies
View Related
Nov 11, 2008

I need to find the number of cells containing a specific word. Here's an example:

Site # Visit

1001 12-Aug-2008

1001 LATE

1002 08-Jul-2008

1003 09-Aug-2008

1004 LATE

1005 12-Aug-2008

1005 LATE

I need a formula that will calculate how many "LATE" cells there are.

View 3 Replies
View Related
Feb 19, 2009

I work with Excel the more I realize that I'm not that good at it Anyways, I'm in need of a quite advanced formula (for me) that sums up relative relative rows of a specified column. The rows relation depends on the ID-number of the person. Look at my attachment.

In Sheet1 I have an extract of the data I'm working with. It's a medical record of different people in a study. The ID (col. B) seperate the different people. One person can have several perscriptions (spelling?) of medicine. Every perscription has an "fddd", a daily dose (col. L). I want to add up all the daily doses for every seperate ID of my data in Sheet2. So for example, for ID1 I want it to sum L3:L6 in Sheet1 and return it to Sheet2!B2. If the ID is not found I want it to be blank.

View 4 Replies
View Related
Jun 17, 2009

See the attatched spreadsheet. I can not seem to figure out a formula that will calculate the totals for each type of data.

My goal is to have a sum for each location for # of audits, Critical Counts, and Major Counts.

View 2 Replies
View Related
Mar 25, 2014

At the moment I have a simple formula that calculates the due date of a task

The log in date is in cell B3, I use a simple formula in cell F3 "=B3+20" - This gives me the due date

I now need to add another log in date but use the same due date cell

So now ....

Either B3 or D3 will contain a date - whichever one is populated I will need the due date displayed in cell F3

View 10 Replies
View Related
May 19, 2008

I have a question maybe this isn't even possible or it is i'm not that high on excel calculations, but I would like to know this.

I have this document:

[url]

I would like to calculate that if i've rated a 5 (cell C) I would like excel to calculate the time (cell B), the thing is that I want to calculate how much time I've wasted watching movies in every rating 1-10.

View 10 Replies
View Related
Oct 21, 2008

If I enter anything in a cell on one work book I get calculating cell for about 3-5 seconds.

I don't have any formulas looking at the book. and I have deleted, renames, copy and pasted, exported to text and reimported and still it does it.

The thing that is odd though is that if I press return again it clears the calulation cells message.

View 9 Replies
View Related
Jan 8, 2009

I am running software that calls an Excel macro using DDE. The macro populates a sheet with information to be printed, then prints the sheet.

I would like for this to take place in the background with Excel never taking focus from the software that calls the Excel macro. Currently Excel some times takes focus from the software and some times does not. The macro also takes the information it prints and stores it to another sheet, but I don't think that's the culprit stealing focus from the other software.

View 7 Replies
View Related
May 11, 2013

I am look for a formula that takes the value from each worksheet and enter this to a summary sheet. I do not what to use formula = then press the enter key when you have select the cell with value in.

View 1 Replies
View Related
Jun 30, 2014

I am calculating ratios to show as 1:0 or 3:2 etc using this formula: =IFERROR(CONCATENATE((K2/GCD(K2,N2)),":",((N2/GCD(K2,N2)))),"Nil")... I have a lot of columns that have zeros in them and the formula i am using doesn't seem to work for those...

Example

A B

3 6 should equal: 1:2 works OK

2 0 should equal: 2:0 my current result: 1:0

3 0 should equal: 3:0 my current result: 1:0

0 1 should equal: 0:1 works OK

0 0 should equal: Nil works OK

4 1 should equal: 4:1 works OK

Anything with a zero in the first column comes up as 1:0

View 5 Replies
View Related
Dec 5, 2013

Formula "(Cell-Cell)/ABS(Cell)"

When calculating percentage difference do I always subtract the smaller number/cell from the larger number/cell, then divide by the smaller number/cell or vice versa? Maybe I am over thinking it, but it is confusing the heck out of me

I provided an example below to illustrate a spreadsheet I work on. The numbers go up and down. I want to know how I should go about setting up the formula. If I need to ensure the smaller number is always subtracted from the bigger number, it will require a lot of manual intervention on my part.

Row 3 is =(b2-b1)/ABS(b1)Row 4 is =(b2-b1)/ABS(b2)Row 5 is custom. I went through each cell and made sure the smaller number was being subtracted from the larger number, then divided by the smaller number

PercDiffExample.xlsx

View 4 Replies
View Related
Jan 23, 2014

I'm trying to calculate total occurrences of a given month. I have included the spreadsheet for reference with totals.

I only want to calculate when the Policy column has a "N". If it has a "Y" I do not need to do anything

If the Policy has a "N" I want the formula to look at the V and P columns and give me a total in the Total Occurrence column. I want it to look at the entire month for a grand total.

If the V or P column has a 4 or less it should equal 0.5, if it has a value is between 4 and 10 it would equal 1. So in the Total Occurrence column should calculate the all of the 0.5 and 1's based on each set of columns and give a total. In the example that I have given in the spreadsheet the total in the Total Occurrence column would be 2 because Jan. 1 has a N and a 8 which equals 1 and Jan. 3 has a N and a 4 which equals 0.5 and Jan. 5 has a N and a 4 which equals 0.5 for a total of 2 occurrences.

View 10 Replies
View Related