Looking For A More Memory-efficient Way
Mar 31, 2009
I am currently using Excel 2003 to pull from 2 external spreadsheets to compile data. The first sheet (seniority list.xls) being referenced contains all of our employees (approximately 350 rows, but that can vary from 340 to 450) as well as some information (employee ID, full time or part time, etc.). The second sheet being referenced (pay.xls) contains a breakdown of all employees and their pay for a particular bi-weekly period.
What I'm looking to do is:
(1) Find out all of my current employees. I do this by referencing seniority list.xls.
(2) For each employee, find out how many hours they worked each week. I need to do this by referencing pay.xls. Unfortunately, that file works quite differently than seniority list.xls. Unlike the latter, which contains one employee number per row, pay.xls could contain as many or as few rows per employee as their schedule dictates. Each row contains things such as overtime, regular time, double time, alternate regular time, alternate OT, vacation, sick pay, etc. In total, pay.xls could contain as many as approximately 8000 rows. To determine how many hours they work, I'm using this CSE (array) formula:
(Note that the employee ID is in column A of the current sheet.)
View 9 Replies
ADVERTISEMENT
May 25, 2007
I have a question about loops. I've read that loops are slow and there are better ways to achieve the same results but I do not know how to accomplish the same without using a loop. I've attached a code that looks in the first sheet and if in column "f" if criteria is met it will copy the row to a separate sheet. Then is goes to a separate sheet and does the same thing after it skips a row then copy header information. What i'm doing is consolidating multiple sheets to one for prioritizing data. The loop works but I've been going back and trying to make my workbook a little more effecient and really cannot figure out how I would do it differently.
Sub AVAILABLEBILLETS()
Dim xlCalc As XlCalculation
xlCalc = Application.Calculation
Application.Calculation = xlCalculationManual
On Error Goto CalcBack
Application. ScreenUpdating = False
Dim j As Integer
Dim I As Integer
Dim K As Integer
Dim fillcell As String
View 9 Replies
View Related
Sep 17, 2012
I have a bunch of array formulas that need to be activated by going on the cell, hitting F2, then hitting Ctrl+Shift+Enter. In the attached sheet I have done a record macro to automate this. I have another sheet with something similar just a lot more items on Sheet1 (~250 items). It will be very time consuming for me to record macro and hit F2, then hit Ctrl+Shift+Enter ~250 times so I am wondering if there is any way to activate all the array formulas on the sheet in one go using 1 formula. I'm not too concerned with file size.Also, not all arrays only include columns A and C, other columns (D, G, H and J) are included too.
View 3 Replies
View Related
Dec 29, 2009
following code be write down in more efficient manner :>>>
Range("B3") = Range("L" & Target.Row)
Range("C3") = Range("O" & Target.Row)
Range("D3") = Range("Q" & Target.Row)
Range("E3") = Range("R" & Target.Row)
View 9 Replies
View Related
May 13, 2006
I'm using the script below to do a cut & paste job. The script works fine. However, since I have a lot of data on my worksheet, it takes a very, very long time to complete the job. Based on the same logic used in my script, could you propose a more efficient way of doing it?
Sub test()
Application. ScreenUpdating = False
On Error Goto errortrap
Do
Columns("d:d").Select
Selection. Find(What:="*", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell. Offset(0, 1).Activate
ActiveCell.Offset(0, -1).Activate
Selection.Cut
ActiveCell.Offset(5, -2).Activate
ActiveSheet.Paste
Loop
errortrap:
End
End Sub
View 2 Replies
View Related
Feb 23, 2007
Was hoping to get some help with a mini project of mine.. Currently I am looking to create a progress/commission report (pull data from an access dbase) and populating an excel sheet. I can manage to pull in the data fine its just gettting into a format that is the complicated thing. I was hoping that someone might have a few moment to pick thru my code (see attached) and maybe suggest an alternative/more efficent way to do the refreshing.
View 5 Replies
View Related
Apr 25, 2007
In each VBA book I've read the authors will write their code like this:
nStart = UBound(GenArray1)
nEnd = UBound(GenArray2)
Redim preserve NewArray(nStart To nEnd)
Instead of the less line method of:
Redim preserve NewArray(UBound(GenArray1) To UBound(GenArray2))
View 7 Replies
View Related
Feb 8, 2013
I have a lot of data with the wrong number format. I want to find all cells with this particular number format and change it to another.
I know I can do this by looping through all the cells on the sheet or by using the find method, but both ways require looping. I am hoping there is a quicker way. I originally thought there was a SpecialCells type that was format conditional (not conditional formatting), but there doesn't seem to be one.
View 8 Replies
View Related
Sep 10, 2009
I have the following code on a userform. The form has several checkboxes used to determine which states to include on a report. I was wondering if anyone could think of a better way to code this. The code goes throught the checkboxes and assigns values to variables state# if the box is checked. I need the state# variable to fill in sequentially, so 1 first, 2 second, ect. Any boxes not checked will result in some state# = "". I also need to keep the order the states are checked consistent. If Arizona checkbox is checked, I always want it to be State1.
View 3 Replies
View Related
Dec 31, 2008
in a selected columnar range.
I want to write a fairly simple, fast macro to examine a selected range and simply delete the row for each blank cell that it finds in that range.
I have read around that loops aren't necessarily that fast and some of these ranges could be 1000s of rows long...
way to write something like this in VBA?
View 9 Replies
View Related
Jan 12, 2010
Have two ranges:
Range("Master") 5000R x 500C
Range("Filtered") 5000R x 500C
1st Column in Master is a calculated trigger column that sets itself to the row number if it needs to be copied to the Filtered range or is 0. .
Currently using two loops:
Loop 1 Build Collection of unique row numbers from the trigger column.
Loop 2 Process Collection: range copying row values from master to filtered.
View 9 Replies
View Related
Jul 16, 2009
the sheet is very long and monitors the sales activities of each individual every day extending vertically down the sheet until the present day.
my first priority was to get the information recorded but now i am wanting to use it.
how would an expert arrange this data to make analysis easier going forward?
ultimately i will be wanting to look at individuals, groups of individuals, certain types of activity etc within various time periods.
View 9 Replies
View Related
Oct 21, 2006
Is there a way to make the code below more efficient?
My data resides in "Buildings August 2006(2).xls " and I wish to copy some cells to "Backlog Analysis .xls"
'Copy J16 to B5
Windows("Buildings August 2006 (2).xls").Activate
Range("J16").Copy
Windows("Backlog Analysis.xls").Activate
Range("B5").PasteSpecial
I repeat the code above 7 more times to handle J29-B6, J33-B7, J42-B8, L56-B10, L57-B11, L60-B13 and L62-B14.
Is there an Array I can use to encompass all of the copy and paste commands?
This is step 1 of something I am doing for work
Step 2 will be to figure out how to work my way through all of the worksheets in "Buildings August 2006(2). I have seen numerous code examples for this so I think it shouldn't be too hard.
View 5 Replies
View Related
Feb 1, 2010
I was tasked with looking through a 10,000 row by 20 column spreadsheet for 628 different ID Codes and replacing them with their Descriptions. The ID Codes could appear individually in any of these cells (200,000 cells!).
The list of ID's and Descriptions hardly ever changes, so I decided to create the two-dimensional array as part of the find-replace macro shown below (only a few lines of each dimension are shown).
My question isn't related to that (although if you can suggest a better alternative please do). The main "work" of the macro is the loop at the end of the array declarations, which essentially loops through all 628 ID codes in array dimension 1 and does a Find-ReplaceAll with its corresponding Description from array dimesion 2.
This task, manually, could take days. My macro has whittled the task down to 2.5 minutes on a 5-year-old laptop, but I was hoping one of the gurus might suggest an even better method than 628 loop iterations. If not, so be it, the end users will appreciate what I've done and then have to find something to do with their "free time."
View 5 Replies
View Related
Oct 18, 2007
I'm trying to move my VBA programming to the next level and use more efficient code.
I'm wondering if there is a faster way to run the loop below, perhaps removing the 'For c = 2 to LastRow' as it seems a little clunky to me.
For c = 2 to LastRow
If Cells(c, Range("Product_Type").Column) = "" Then _
Cells(c, Range("Product_Type").Column).Interior.ColorIndex = 41
Next c
I've seen a similar thing somewhere where it was all done in one statement without the loop.
View 9 Replies
View Related
Feb 18, 2009
I am trying to creat a code that with take the value of an active cell and depending on this value will assign a backcolour to a label corresponding to the cell. so far so good.
I then want the code to offset to the next cell in the range read its value and assign a colour to that cells backcolor.
here is an idea.
range("A1").select
for n = 1 to 4
if activecell = "A" then
Label1.BackColor = RGB(0, 0, 0)
else
if activecell = "B" then
Label1.BackColor = RGB(0, 0, 255)
else
if activecell = "C" then
Label1.BackColor = RGB(0, 255, 0)
end if
activecell.offset(0,1).select
next n
Firstly I would like the next loop (refering to A2 in this example) to refer to Label2 not Label1 and so on.
secondly the example would loop through 4 cells in one row (A1:A4) but I would like the code to apply to several rows ie (A1:D4).
so thats 16 cell and 16 labels. I could code this in a very inefficient way but I am sure ther is a simple method.
View 9 Replies
View Related
Dec 29, 2007
Im having an awful time with the time it takes to calculate my formulas. I have probably written some of them ineffiecent.
=INDEX( Website_Query!$A$2:$Z$10000,MATCH(1,(Website_Query!$A$2:$A$9725=B1144)*(Website_Query!$C$2:$C$9725="EA "),0),4)
=Q1144*2
=IF(ISERROR(VLOOKUP(A1144,pricing!A$2:AL$1547,38,FALSE)),"",VLOOKUP(A1144,pricing!A$2:AL$1547,38,FALSE))
=IF(ISERROR(Q1144+AR1144),"",Q1144+AR1144)
=IF(AK1086="q2",AL1086+AS1086,IF(AM1086="q2",AN1086+AS1086,IF(AO1086="q2",AP1086+AS1086,IF(AQ1086="q2",AR1086+AS1086,""))))
="q"&INDEX(Website_Query!$A$2:$L$7725,MATCH(1,(Website_Query!$A$2:$A$7725=$B1086)*(Website_Query!$C$2:$C$7725=$C1086),0),5)
View 3 Replies
View Related
May 27, 2014
how to calculate averages and standard deviations based on different time periods without having to manually change the cells?
example:
1st average output at z3, 1st std dev output at z4
data to calculate from c3:c50
2nd average output at z5, 2nd std dev output at z6
data to calculate from c51:c98
3rd average output at z7, 3rd std dev output at z8
data to calculate from c99:c148
and it goes on based on this sequence. i would like to know how to do this without having to change the cells each time i want to calculate. basically what is the quickest way to calculate following this sequence?
View 6 Replies
View Related
Apr 10, 2014
Any way of shortening the attached code. The first part 'Sub Loop1()' works great but I can't figure out how to shorten the second part. As you can see the distance between columns is always consistent i.e. add 5 columns to find the next outcome to work out the next result. Ideally I wanted to somehow loop through the next 5 columns again and again until the cell is blank.
[Code] ......
View 2 Replies
View Related
Jan 24, 2010
I need to check each of 13,000 job titles to see if they include the string "VP".
For the time being, I want to find the most efficient way to check an individual title, before building an array or SUMPRODUCT function that will check all the titles in swoop.
So... I have tried:
=ISNUMBER(SEARCH(A1,B1))
..but it returns TRUE if A1 and B1 are both blank -- which they can be in my spreadsheet.
And I have tried:
=SIGN(SEARCH(A1,B1))
but it returns #N/A if A1 cannot be found within B1.
If I can, I want to avoid introducing an IF statement because, looking ahead, I am already concerned that this analysis will be complicated enough without multiple paths.
So.. what's the most efficient way to return a TRUE/FALSE, or 1/0, result depending on whether A1 is in B1?
EXTRA-CREDIT!
An extension of the above problem... arrays and SUMPRODUCTs welcome:
What's the most efficient way to return a TRUE/FALSE, or 1/0, result depending on whether (A1 or A2 or A3... or A99) is in B1?
View 13 Replies
View Related
Feb 17, 2014
I got a macro to copy and paste values onto another tab within my worksheet. I have a lot of data and currently takes about 30 seconds to calculate and paste. Not sure if its an issue with my macro or with my computer (Mac - Excel 2011).
Here is an example of my macro:
Sub SimulateWeek()
If Range("AdvanceWeek").Value = "Week 1" Then
Range("Week1B").Copy
Sheets("Schedule - Results").Range("C2").PasteSpecial Paste:=xlPasteValues
[Code]....
(this continues on until 'ElseIf Range("AdvanceWeek").Value = "Week 31"....etc). So you can see I have the same code repeated 31 times.
View 2 Replies
View Related
Aug 13, 2009
I own a Hair Salon which collates its daily takings using a spreadsheet. A section of this takings sheet asks staff to enter which Products have been sold and what they where sold for (RRP), so that once all products have been entered, totals at the end of the day allowing the end -of-day "till-up" process to reconcile purchases and services rendered aginst cash and cheques received.
Unfortunately there is over 600 products to choose from and this evergroes as new products are introduced by manufacturers. Names are long and often very similar, and product price manual listings are slow to reference, therefore the customer has to wait while the staff find the right product and select the right price.
All of this is subject to human error. What i would like is simply to enter part of the product name into a Cell and the right product to be visibly selectable and the price automatically added to an adjacent cell.
View 4 Replies
View Related
Dec 15, 2006
I have name 28 columns. The range in EACH columns is 6:14000, i.e. the first name is date and the range is from Row B6 to Row B14000.
I am using SumProduct instead of VlookUp to look up values. An example of one of my sumproduct formulas:
SUMPRODUCT(--(Date=$B$6),--(Book=$D$6),PnL___Book)
where the first columns has dates, the next column is bookname and the last column is the profit and loss for each book, i.e. the values that need to be addad.
Now, suddenly I do not get any numbers but just #VALUE! but if I change the range to a lot less, e.g. between Row B6 and B50, it works.
My questions is if this has to do with memory in Excel. Are there limits to how many formulas and how many names you can have before Excel crashes. I am aware that the 28 columns names are very large.
Are there any way of how to get around this, VBA-codes or other formulas that uses less power, not naming columns or what?
View 9 Replies
View Related
Oct 29, 2009
I have an excel sheet that has an onclose macro, and on certain computers i get an error message stating out of memory. can anyone explain why this is and how i could avoid it, by possibly clearing the memory first or something?
View 9 Replies
View Related
Aug 22, 2008
i have a spreadsheet on Excel 2000, which is 256 column's wide and 4000 long the only formulas are countif which start at row 1000-4000 and are copied to column 256. Just for the one sheet it is 14mb is this normal
View 11 Replies
View Related
Dec 23, 2013
I have a large amount of data on NBA teams and players that is extracted from external web pages. In order to have the data shown on separate sheets to do comparisons I have to use many array formulas and from what I have read that takes up a large amount of memory. I'm now at the point where I'm receiving a message that says "Excel cannot complete this task with available resources" when I open the workbook.
Is there any plausible way that I can resolve this situation? I have thought about converting the lookup formulas from arrays (index/match) to vlookups and hlookups.
I'm not as advanced with using Excel as most of you, and I realize I may be at a point to where I will need to use different software to analyze my data, but I would prefer to stick with Excel as it would be difficult to start from scratch and learn how to use a different program.
View 3 Replies
View Related
Nov 3, 2008
Using Excel 2007, Windows XP, 4gig ram,
I got this "Insufficient Memory .... close some application" message, once in a while.
I know that i got enough memory to run my Excel.
This is a random problem. Memory as been checked with mem86 and it work fine
View 9 Replies
View Related
Oct 4, 2006
Regarding the Memory Leak problem with ADO, does anyone know of a workaround?
The workaround mentioned on Microsoft's website (http://support.microsoft.com/default...;en-us;Q319998) pertains to using ADO to retrieve a recordset from an Excel worksheet, not from an Access Database.
1) Does anyone know if those workarounds would also address retrieving a recordset from an Access Database?
2) If so, is the "SELECT INTO" workaround effective?
Is this Memory Leak issue relatively minor or only affecting Excel 2000? I'm guessing it must be one of those 2 things since the concensus is ADO is superior to MSQuery..
View 2 Replies
View Related
Dec 6, 2006
I'm using a Dell P4, 1.6Ghz, 512Mhz of memory (Win2K & Excel 2003), and it's bogging on spreadsheets that aren't that big and complicated (compared to thing's I did years ago, in the days of 386's and Win98)...
It hits the 100% CPU usage and stays there for minutes (especially when saving) on files that are only around 2M in size, yet it's not using much of the memory...
Spreadsheets large or small seem to have little effect on how much memory is being used... is Win2K allocating a fixed % of total memory for Excel to use?... can this be changed, so Excel gets to use more of the available memory?
I've been cleaning up and redesigning my spreadsheets to make them more efficient, but there seems to be a disconnect between my hardware specs and Excel's performance with files that aren't that big ~:-/
Disabling my antivirus software (Nortons) prior to saving, seems to speed it up a little, but this is not something I should be having to do...
View 9 Replies
View Related
Apr 8, 2007
One of my apps I run is very large and memory intensive, and there are several subsequent procedures which tend to either bog down or errors out before the program finishes running.
Is there a way to clear the memory out?
And adding more physical memory is not an option, I have 4gigs already and Excel can only utilize 1gig anyway.
View 4 Replies
View Related
