Long Calculation Loop Upon Update
Sep 2, 2009
My current task is to create a spreadsheet that will auto-hide/show rows based upon values in other cells. I am currently about halfway 40% complete with this aspect of the file. I've read over many forums, and the best what that I've seen individuals do it on other sample spreadsheets posted were a =Rand() in a random cell somewhere in the sheet, and a Private Sub worksheet_calculate(). I'm far from a VBA expert. I'm trying to learn as I go along. Here is my current VBA:
View 4 Replies
ADVERTISEMENT
Jul 18, 2009
I work with a lot of time based worksheets for a 24/7 operation and constantly use the formula:
=IF($E17>$D17,(HOUR($E17)+(MINUTE($E17)/60))-(HOUR($D17)+(MINUTE($D17)/60)),((HOUR($E17)+24)+(MINUTE($E17)/60))-(HOUR($D17)+(MINUTE($D17)/60)))
to calculate times worked where $D17 is start time and $E17 is finish time. Shifts can start and finish the same day ($E17>$D17) or go past midnight ($D17>$E17).
To make matters even more interesting, there are a number of variables that require adding either "and" and/or "or" conditions to this formula so I can end up with several of these nested and it gets pretty complicated.
I'd like to have a UDF so I can just enter the formula:
=CalcTime(Start Time,Finish Time)
View 9 Replies
View Related
Feb 26, 2009
I've got a sheet which I need to analyse and split into several different sheets but the raw data format leaves a lot to be desired as there are blank rows inserted randomly between rows of data. I need the 'good' data to stay in the same order so I've written a macro to sort through the data and delete any blank rows leaving the good stuff behind.
The problem is this takes ages as there can be up to 30000 rows that need to be checked and I need to do this 5-6 times a day. I just wondered weather there was a quicker way to do this? The code I've got is detailed below:
Sub Prep2()
'Delete all blank data rows
Dim Rows As Double
Dim Rownum As Double
Application.ScreenUpdating = False
Rows = Selection.SpecialCells(xlLastCell).Row
For Rownum = 2 To Rows
If Cells(Rownum, 11) "" Then GoTo NxtRownum Else
Cells(Rownum, 11).EntireRow.Delete shift:=xlUp
Rows = Rows - 1
NxtRownum:
Next Rownum
Application.ScreenUpdating = True
End Sub
View 9 Replies
View Related
May 14, 2014
I have a very long routine, looping through 35,000-ish rows several times to detect and delete unwanted items. Occasionally, it gets locked into an endless loop and CTRL+Break will not stop it - I have to kill Excel through the Task Manager.
How can I find out where the endless loop is so I can detect what's causingit and fix it?
View 6 Replies
View Related
Jan 17, 2007
I have two spreadsheets 1 of which gathers data on individuals for a daily basis for the duration of 1 month via a database query. The other sources this data (not via a lookup) for 20 staff members of staff and details this in a report. The report allows staff members names to change and automatically update their information from the previous sheet.
My problem is that even though the file size is 694kb when asked to update the sheet takes an age to load. Is there anything I can do with my sheet?
View 9 Replies
View Related
Jul 20, 2006
I've created a bit of a monster Excel model taking in an enormous range of inputs and outputs. Most of the formulas are vlookups and sumif's. The workbook is now about 15meg. I've got autocalculate turned off because of the great deal of time it takes to recalculate. When I do try and re-calc some of the cells update but very many of them do not change at all. If I then go into these cells and edit them (F2 enter) the correct output is given! If I close the book and re-open it normally shows the correct outputs, after a while it goes back to the error above.
View 3 Replies
View Related
Feb 11, 2009
I'm trying to make a scatterplot so what I did was recorded a macro and now I want to be able to update it using a for-loop. Here's my code.
View 4 Replies
View Related
Jul 19, 2012
I have a Database and every entry in the database has its own corresponding sheet with the data in a more viewer-friendly format. I want to assign a macro to a button to loop through all the sheets, copy the cells and paste to the database (to update the database).
Also, nothing prevents the user from deleting the sheets or mixing them. So is there a way to take the reference number from the database in column B, search for the corresponding sheet which has the reference number in cell B3 and then update it accordingly.
Code:
Sub Button19_Click()
Dim WS_Count As Integer
Dim I As Integer
[Code]...
View 1 Replies
View Related
Sep 5, 2008
I have used the function = now() to have the most updated time but it updates a workbook when I open it in the first place. How I can avoid this?
View 9 Replies
View Related
Oct 18, 2013
Let's say that in column A I have numbers,"Yes" and "No". I want in column B to have only the numbers from column A, in the same order without any empty ranges, and everytime I add in column A a new number, column B to update automatically with that number. Let's have an example:
A B
Yes 12
12 13
No 10
13
No
10
Yes
And if I want to add in column A:
A B
Yes 12
12 13
No 10
13 25
No 15
10
Yes
25
15
So the column be will update automatically. I already tried =IFERROR(INDEX($A$1:$A$10,SMALL(IF(ISNUMBER($A$1:$A$10),ROW($A$1:$A$10)),ROWS(B$1:B1))-ROW($A$1)+1),") but using this many times get's my file very heavy and the excel is working slow.
View 13 Replies
View Related
Oct 27, 2008
I have some VB code which sequentially opens over 200 workbooks to extract data from each and populate another workbook. These workbooks do have links to other workbooks in them.
For some reason when some of these workbooks are opened I get a requestor window asking whether I want to Update or Don't Update the data. I always want to Update the workbook and believe this can be done in VB by hiding the requestor?
View 7 Replies
View Related
Jun 21, 2007
I have a vlookup to another workbook. It works fine if both workbooks are open. But if both are not open and I open the workbook with the links and click Update, #VALUE! returns. I have attached the two files. I don't think it is my formula, but here it is anyway. =IF( COUNTIF([Tempozgrid.xls]June!$A$52:$A$83,A3),VLOOKUP(A3,[Tempozgrid.xls]June!$A$52:$L$82,12,FALSE),0)
View 2 Replies
View Related
Mar 31, 2008
With Sheets("regrade pharm_standalone")
For Each r In .Range("standaloneTerritory")
If r.Value = "X101" Then
r.EntireRow.Copy
Sheets("X101").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues
End If
Next r
End With
-------------------
I need to repeat this loop for values from X101 to X151. In all cases, the sheet name is equal to the value I'm looking up (eg: value = X102 goes to sheet X102).
I have a named range called 'territories' that contains the list of X101 -> X152.
I'm hoping to make the code perform the loop for each of the territories without my having to copy & paste and change the 'X101' 51 times as this would seem a rather silly thing to do!
View 9 Replies
View Related
Nov 19, 2008
make a calculation(addition) and use the answer to multiply against another addition calculation....
The sum of (Monday!A1:A4) multiplied by the sum of (Monday!B1:B4) plus (Tuesday!A1:A4) multiplied by the sum of (Tuesday!B1:B4) and so on.
View 2 Replies
View Related
Dec 11, 2008
I have a basic formula =C17+'Asset Depreciation 2008 Onwards'!C24, and I want to copy it down just using the drag function. Problem is that the second reference range of cells are in rows and hence when I copy it down it doesn’t automatically update the cell references because it want to update them by column number instead of row number. IE I want it to display =C17+'Asset Depreciation 2008 Onwards'!
D24, instead of C25. Do you know if there is any way of telling Excel that I want it to increase the column number by 1 every time, instead of the row number for this part of the formula?
View 5 Replies
View Related
Apr 18, 2007
I have been trying to remove or disable the message on update to other worksheet " To update all linked click yes......" I have try the following unsuccessfully
Sub auto_open()
Application.AskToUpdateLinks = False
End Sub
On the menu bar choose Edit ---> Links Can not choose manual, as the option is grey out (disable) I would prefer a vba solution, but I am open to anything to get rid of this message
View 2 Replies
View Related
May 14, 2014
Macro which loops through a number of files and calls the same macro in each of them. Unfortunately when I add "Application.Run..." to the code, it no longer loops through the process and instead stops after updating the first file in the loop. If I remove the "Application.Run..." code and add any other code, the loop works fine and it continues through the process repeating all the steps for each file found.
Why it stops after one file when using "Application.Run..." to call the macros?
NB I have a list of path and file names starting in row 8 of columns A and C. Each file in the list has a macro called UpdateS1 and promoupdate1.
Sub C_Run_Loop_Macro()
Dim lastRow As Long
Dim i As Long
[Code]....
View 4 Replies
View Related
Sep 11, 2013
I have working code that returns a row number within a for loop based on parameters I set.
Each time the for loop runs I would like to store this row number, then after the loop has finished, delete all stored rows.
Code:
for rowNum = 1 to x (some variable end row number which I already have worked out using End(xlUp).Row)
if x = y then
*storedRow = rowNum
end if
next rowNum
*
Lines with a * are the bits I can't work out. I've been trying to understand arrays by reading posts on what other people have done, but I can't fit (or fully understand) the reDims, or reDim preserves into my code. I've seen what appear to be quite complex ways involving uBounds and LBounds, but unfortunately I can't see how to use them.
All I want is to simply keep adding a row numbers to a variable, (i.e. row 2, 5, 20, 33, 120, etc) and then delete those specific rows.
View 4 Replies
View Related
Aug 30, 2006
I am looping through each cell in a range and I would like to loop in reverse order.
Dim CELL As range
Dim TotalRows As Long
TotalRows = Cells(Rows.Count, 1).End(xlUp).Row
For Each CELL In Range("C1", "C" & TotalRows)
CELL.Select
'Code here to delete a row based on criteria
Next
I have tried:
For Each CELL In Range("C" & TotalRows, "C1")
and it does not make a difference. I need to loop in reverse order since what I am doing in the loop is deleting a row. I am looking at a cell and determining its value. If the value is so much, then the row gets deleted. The problem is that the next row "moves up" one row (taking the pervious cell's address) and therefore the For Each Next loop thinks it has already looked at that row.
View 7 Replies
View Related
Feb 7, 2008
I have some numbers in a column that I need to copy 12 times (each one) into another column. The problem is that I got like 200 records that will be converted in 15000 aprox. I've uploaded an example of what I need,
View 3 Replies
View Related
Nov 4, 2013
I have a workbook that contains, say, 50 worksheets: the first two worksheets summarise the data and are static in that they don't move position. However, the next four worksheets contain certain data for any given month. Each time a new month comes along, say, November, I insert four new worksheets after the two static ones as a result October's four worksheets are simply moved down the line in terms of worksheet order.
I need a macro to refer to the first six worksheets only (not the other tabs). I opted for index referencing for each worksheet, ie one - six. Now within these six worksheets in any given month, I need to sort the data by a certain column. The problem: in sheets 1,4,5 and 6 I need to rank by column E, but in sheets 2 and 3 I need to rank by column C. I have stepped through the code, which works for sheets 3-6, but doesn't seem to refer to sheets 1-2.
Sub WorksheetLoop()
'
' Loop through an indexed number of worksheets; _
' & this ensures that the worksheet range is dynamic _
' and is able to adjust when new sheets are added/removed, etc.
'
'Dim ws As Worksheet
Dim i As Long
Dim ws As Worksheet
[code]....
View 2 Replies
View Related
Oct 24, 2009
I've worked on a solution for this thread (http://www.excelforum.com/excel-prog...-automate.html) but have been mentally challenged with how to avoid changing the loop counter in one of the loops I have used to resort an array of file names from the getopenfile dialog.
The aim of the shown code (see post 12 of the above link for attached file) is to check if the file containing the macro is included in the array returned by getopenfile while sorting the array of file names, and if so, moving it to the end of the array for "deletion" by redimming the array to exclude the last item. This problem of the open file being selected in the dialog may never arise, but... as the OP's request in the other thread was to allow two-way comparisons between numerous files, I've considered it likely enough to test for.
Here's the code I have settled for esp between the commented lines of hash symbols, which does change the counter (see the commented exclamation marks), but prevents an infinite loop (on my second try!) by using a second boolean flag of "HasCounterBeenChanged". Is there a better way of doing this? Or, alternatively (not in my thread title), is it possible to prevent the active file being selected through one of the arguments in the getopenfilename method?
View 3 Replies
View Related
Apr 8, 2009
I've attached the workbook to make this easier. If you look at the file master.xls you will see 2 tabs. On the Tool tab I've highlighted some blue cells and some rose cells. If you look at the blue cell G68 there is a long formula that reads up to 3 cells from the Specs tab and then plugs in the correct data from the blue cells on the right side of the Tools tab page. I need the rose cell F69 to do the same thing only with the data from the rose cells to the right. When I try and duplicate the fromula from G68 into F69 I get errors. I hope I made this clear enough. The formula in G68 is long, is there a way to shorten it? Also sometimes in this workbook when I try to type a formula in a cell I get the text I typed instead of the formula. For example I might enter in A1, =B2. Instead of getting the data from B2, it reads =B2. I've looked at how the cell is formatted but can't get it to work.
View 3 Replies
View Related
Jul 3, 2013
I am using the sum function to sum the following cells and the formula is too long. How can I make this work?
'Raw Data'!B3,'Raw Data'!D3,'Raw Data'!F3,'Raw Data'!H3,'Raw Data'!J3,'Raw Data'!L3,'Raw Data'!N3,'Raw Data'!P3,'Raw Data'!R3,'Raw Data'!T3,'Raw Data'!V3,'Raw Data'!X3,'Raw Data'!Z3,'Raw Data'!AB3,'Raw Data'!AD3,'Raw Data'!AF3,'Raw Data'!AH3, 'Raw Data'!AJ3, 'Raw Data'!AL3, 'Raw Data'!AN3, 'Raw Data'!AP3, 'Raw Data'!AR3, 'Raw Data'!AT3, 'Raw Data'!AV3, 'Raw Data'!AX3, 'Raw Data'!AZ3, 'Raw Data'!BB3, 'Raw Data'!BD3, 'Raw Data'!BF3, 'Raw Data'!BH3, 'Raw Data'!BJ3, 'Raw Data'!BL3, 'Raw Data'!BN3, 'Raw Data'!BP3, 'Raw Data'!BR3, 'Raw Data'!BT3, 'Raw Data'!BV3, 'Raw Data'!BX3, 'Raw Data'!BZ3, 'Raw Data'!CB3, 'Raw Data'!CD3, 'Raw Data'!CF3, 'Raw Data'!CH3, 'Raw Data'!CJ3, 'Raw Data'!CL3, 'Raw Data'!CN3, 'Raw Data'!CP3, 'Raw Data'!CR3, 'Raw Data'!CT3, 'Raw Data'!CV3, 'Raw Data'!CX3, 'Raw Data'!CZ3,
View 7 Replies
View Related
Jun 17, 2014
In my Personal Macro book, I have 2 modules, one for Functions and one for "macros".
Is there a point where it's more efficient to make a new module (or is the opposite true and it's more efficient if they are all in one)?
Does having a plethora of sub routines in a module slow down excel even though the routines are short? or would having them in different modules slow it down more?
View 4 Replies
View Related
Jun 2, 2009
I know that a variable of string type is equal to "" before it's assigned a value. So I reset the value of strings to empty (i.e. "") when I want them clear of a value. I also know that variables of Variant Type are equal to NULL when they are unassigned so I reset them to NULL when I want to clear them of a value.
But what about a Long? When you declare a variable of Long Type or Int Type or Double Type, what is it's inital value when empty and unassigned. I assume it's not 0 since 0 is an actual number.
I want to reset my lng variable and clear it of a value. So I'm wondering what should I set to... How can i do this?
View 3 Replies
View Related
Dec 20, 2006
1. if I have a custom command Bar that is very long and I want it displayed in two rows how do I code it to start a new row at a certain point (button)?
- can I control the length of it?
2. How do I make the Formatting toolbar inactive on opening a particular workbook?
View 9 Replies
View Related
Aug 10, 2008
I need to build a udf that simplifies a 10 nested if statement . i could achieve my task by using a if and statement, but it would exceed the 7 nested limit.
I have company names in column A, industry code in column b and four varibles in column C,D,E,F . The g column would be a weighted average of columns C,D,E,F. The weighted average score would have the logic of:
If the industry code is equal to "TE", then (C2*.7)+(D2*.1)+(E2*0)+(E2*0)
if the industry code is equal to "EN", then (C2*.3)+(D2*.3)+(E2*.3)+(E2*.1)
if the industry code is equal to "SF", then (C2*.4)+(D2*.6)+(E2*0)+(E2*0)
if the industry code is equal to "H", then (C2*.1)+(D2*.1)+(E2*.1)+(E2*.7)
if the industry code is equal to "MM", then (C2*.1)+(D2*.2)+(E2*.3)+(E2*.4)
if the industry code is equal to "EE", then (C2*0)+(D2*.0)+(E2*.8)+(E2*.1)
if the industry code is equal to "ENG", then (C2*0)+(D2*0)+(E2*.5)+(E2*.5)
if the industry code is equal to "ETT", then (C2*0)+(D2*.2)+(E2*.3)+(E2*.5)
if the industry code is equal to "HH", then (C2*0)+(D2*.9)+(E2*.1)+(E2*0)
if the industry code is equal to "HHW", then (C2*.7)+(D2*.3)+(E2*0)+(E2*0)
View 9 Replies
View Related
Feb 8, 2010
im not really sure what div/0's are na dhow to counter them but i understand from the error that im dividing by something that is not valid yet.
below is my formula, among others like this. i need some assistance in getting the ISERROR code in as i am struggling; ....
View 9 Replies
View Related
Mar 4, 2014
I'm working on a spreadsheet that i have a long if statement and it keeps till me i have a error. I stated reading and come to find out you can only have 7 statement.
=IF($R$6="X",VLOOKUP($A25,VISION,2,FALSE),IF($R$7="X",VLOOKUP($A25,VISION,3,FALSE),IF($R$9="X",[code]....
View 14 Replies
View Related
