# Calculating Cells For No Reason

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
ADVERTISEMENT
Jun 15, 2009

This is something that's been bugging me for the last few hours.

I've written a fairly simple VBA

View 9 Replies
View Related
Apr 21, 2007

I'm currently trying to put together a rather large application to automate a release procedure.

Somewhere in a procedure I have the following

'previous code...

WS.Columns(8).Select

Selection.Copy

WS.Columns(1).Select

Selection.Insert Shift:=xlToRight

WS.Columns(1).NumberFormat = "General"

End Sub

The WS is defined in the beginning of that sub as follows:

Dim WS As Worksheet 'iSpares sheet

Set WS = Workbooks(FrmReleaseFlow.Caption).Worksheets("iSpares")

whereas the FrmReleaseFlow is a userform, active at that time, the caption contains the filename currently working on (in NAME.XLS format, no path), the file is open and the sheet iSpares exists...

The problem is that the code in above procedure gets executed to just that line and then the sub exits and the program continues in the sub right after this sub was called...

The weird thing is, if I set a breakpoint on the line WS.Columns(8).Select, then the execution halts there. If I then continue, the other lines are not executed. Breakpoint on the other four lines are never seen...

So the problem lies within that line WS.Columns(8).Select ...

If I execute this command in the Immediate window, while the code is halted on a breakpoint, it does work as it should...

View 9 Replies
View Related
Mar 17, 2007

I have a macro that uses user input to locate a file. If no file is found matching the user input, a message box displays informing the user. However, if a file is found that matches the user input, the file should simply open. If a file is found, the macro successfully opens the file however it will still display the message box saying no file is found. Since the message box is triggered by an error, there must be another error I haven't accounted for in my code.

Private Sub CommandButton1_Click()

Private Sub CrmFrm1_Click()

If Len(TextBox1) <> 12 Then

MsgBox "Incorrect Case File Number"

FrmNew.TextBox1.SetFocus

Exit Sub

End If

Dim MyCase

MyCase = Dir("S:AccountingProbationTest Files" & FrmNew.TextBox1.Value)

On Error Goto ErrorHandler

Workbooks.Open Filename:="S:AccountingProbationTest Files" & FrmNew.TextBox1.Value

Unload Me

ErrorHandler:

MsgBox "No file found for this Case Number." & Chr(13) & "Please proceed to Template.", 0, "No File Found"

Unload Me

Exit Sub

End Sub

View 2 Replies
View Related
Mar 25, 2007

to ensure the user ends up looking at UserForm5 after having closed the UserForm2 with the x sign.

It works just great - the user gets logged on and can use the UserForm2 but when he/she closes the form with the cross and tries to lo on aner using the UserForm5 - which pops up autmatically due to the above code, both of the UserForms get cloed (or hidden).

Private Sub CommandButton1_Click()

Dim popup As Variant

If ExecuteExcel4Macro("'D:[target.xls]Trg Info'!R1C255") _

= UserForm5.TextBox2.Value And ExecuteExcel4Macro("'D:[target.xls]Trg Info'!R1C256") _

= UserForm5.TextBox3.Value Then

With UserForm5

.TextBox2.Value = ""

.TextBox3.Value = ""

.Hide

End With

UserForm2.Show

With UserForm2 .....................

View 9 Replies
View Related
Jul 2, 2008

I have come across this only a couple times and have tried in vain to find out what the "--" does in formulas

one example I have run accross in the forums is below

= SUMPRODUCT(--(ISNUMBER( FIND("SMS",A1))))

Also, is there a good refrence source for formulas like this?

View 3 Replies
View Related
Feb 18, 2014

I have created several macros where i use an active-X control button. This button is pressed and the macro begins...........but every now and again this button changes size for no apparent reason.

I have read countless articles/posts about what "may" be causing the problem but i have never read anything which clearly identifies the soln to this problem and the problem has been out there for years. To make matters worse the problem can not be duplicated on any regular frequency that i am aware of............so its not like i can post a file that will show the problem.............

View 4 Replies
View Related
Apr 15, 2009

My sub is supposed to take the values from Sheet1 and paste them into 16x10 tables on Sheet2. However, for some odd reason, the loop is skipping some rows. If you take a look, there are 2184 values in Sheet1, but only 1368 end up in Sheet2...SKUs such as 518166,518167,510573,513746,513752 are just being skipped for no reason.

View 2 Replies
View Related
Sep 23, 2013

A

B

C

D

E

F

G

740048

08/29/13

08/30/13

202

[code]....

I would like to have the count of column E. I would like to sepperate current month and year to date using column C. I would like it to reflect it in the below chart.

M

Code

N

Year to Date Date

O

Month to Date

105

1

202

5

4

402

1

View 3 Replies
View Related
Sep 19, 2007

I've just seen a formula with two - (minus) signs right next to each other placed before a section in a formula while reading possible answers to my post. Here is the link: Understanding this formula. I've seen it before and am intrigued as to why it is there and what it actually does. Assume it is used such: = --(1+2). Would the answer then not be =--3 = 3?

View 4 Replies
View Related
May 31, 2013

How can I total overtime based on a "Reason Code" and whether the employee chose "Cash" or "Time" compensation?

View 2 Replies
View Related
May 4, 2007

This is going to be difficult to explain, but I'll try... I need to be able to format a sheet that has all data in column format. Column A contains a number and B an application. C contains the issue data. I need the issue data to be moved under column A and B as illustrated below. What formula can I do to accomplish this?

Original:

Final:

Can't get html or the image tags to work for some reason.

View 9 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
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
Jan 26, 2014

I am trying to calculate an average of 800 cells, where I would like to exclude a cell when a certain character is in it, namely "<".

View 4 Replies
View Related
Jun 2, 2009

I have the following on a spreadsheet that keeps track of when student work is handed in and calculates how many days late it is so a penalty mark can be given.

A1= User entered value : a fixed submission date that a piece of work is due to be handed in on.

A2 = User entered value : The date entered if work is handed in on time or early

A3 = User entered value : The date the work was handed in if late

A4 = User entered value : The date the piece of work is due to be handed in if an extension has been granted

A5 = Calculated value : How many days late a piece of work is

Essentially I want the calculation in A5 to reflect how many days late a piece of work is. SUM(A3-A1) would do this of course. If the work is early or on time, A2 is ignored.

Sometimes however students have permission to hand work in late, in which case another calculation is needed to work out how many (if any) days over their extension date (A4) their work is. How do I combine these two calculations into the same cell, so that if A3 is empty it checks A4 to see if the student has an extension date and then returns a 'days late' value if applicable?

View 9 Replies
View Related
Jul 29, 2008

I am using Arrays throughout a large report and each time I move around in the report or simply open the report I have to wait 5 to 10 seconds to wait for the cells to calculate. Is there a way to prevent this from happening other than changing the calculation option to manual.

Is this and array problem? I don't get this when I use multiple vlookups and choose formulas? Any ideas to speed up or prevent all together?

View 9 Replies
View Related
Oct 18, 2009

I want to average the cells in column B but ignore values in the corresponding rows with a 1 or 7 in column C.

This will be used in a years data where 1 - 7 are days of the week and I want to separate weekdays from weekends when calculating an average.

View 9 Replies
View Related
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
View Related
Jan 2, 2008

I have what is probably a simple problem for most, but can't figure out what to do.

In the sample sheet attached, I have times in column E, and an action describing what has happened in column G. What I want to do is calculate the length of time between an opening action and closing one, but don't know how to go about it, as there can be an empty cell(and sometimes more) between each open and close.

View 9 Replies
View Related