Quick General Loop Optimization
Jul 31, 2012
Let me first quickly describe the workbook setup. I have a main workbook ("Fees") that acts as a master list for every employee's clients' account numbers. Every month, 30+ workbooks get downloaded from 3rd parties that have the client account numbers, as well as their current account value.
My macro- Loops through every employee's worksheet within the Fees workbook, and loops through every account number. It then compares the account to every account in every other open workbook. Upon a match, it pulls the account value back into the main workbook. After it finishes looping the Fees workbook, it starts to loop every open workbook, and checks every account value against every account in the Fees workbook. If it doesn't find a match, it prints the value on a Missed worksheet within the Fees workbook.
The situation- I know for a fact this a verrry slow way to go about what I need to accomplish. I am very new to writing code, and gladly can take the extra minutes to let the code execute to know 100% nothing was missed. Going forward, I would like to start trimming execution time without jeopardizing the 100% accuracy of my slow macro.
The question- Based on how I execute my loops, what is likely to trim the most time for the range lookups? I have no practical experience with Vlookup, but I understand that is a possibility I should look into. I'm vaguely familiar with Match, and arrays, and I believe they could also trim time. I also realize there are probably at least 15 other ways to go about it I am not even aware of.
View 9 Replies
ADVERTISEMENT
Aug 7, 2008
I'm trying to make an excel formula that generalizes product names based on their format. So the general format would be XYZ08/T13. I know a ? is equal to a general letter, but is their a symbo that specifically means letter or number. I'm running into problems using the ? because the formula ends up identifying additional items that are not products as products.
View 9 Replies
View Related
Oct 6, 2008
Optimize the code below to perform a search function for two variables. Right now it takes a while to execute as it goes through each cell row by row, column by column. It would be nice to optimize it to function a little faster (make that alot faster) . I realize my coding methods are not all best practice habits, so let me know your thoughts, workarounds or adjustments.
In its basic form the code pasted in below is being used to locate data at the intersection of a given row and column. And this data can lie anywhere within this array, unsorted.
Where the Row to search contains a team members name (Derek, John, etc.) D3:D50 and dates in columns Q3 to IU94.
My spreadsheet has a data page with a list of team member names in D3 to D50, and a listing of dates running accross Q3 to IU94. Each members name may appear muliple times through out D3:D50.
So for instance "Derek" (cell D6) worked 40 hours (cell Q6) the week of 9/1/08, it is the 40 that I want returned to a summary page and since the name Derek may show up more than once down the list I want to return all occurances where a date and name instersect on my summary sheet.
[/quote]
Below is my code which is currently used to lookup team member name "Lookup_Value1" for a given date "Lookup_Value2), it indexes the date column to search for data.
Function LOOKUP2(Lookup_Value1 As Variant, Lookup_Value2 As Variant, TABLE_ARRAY As Range)
Dim nRow As Long
Dim nCol As Long
nRow = 0
nCol = 0
With TABLE_ARRAY..............
View 23 Replies
View Related
Aug 13, 2008
Can anyone recommend a good source for tips on optimizing VBA code? I'm working on
an application that reads and interprets large text files (3500 lines) and starting to think
about the efficiency of the code inside my read loop.
For example I'm wondering how Excel actually implements access to cells internally. Is a
cell just a variable or part of an array?
If I do something like: range("A1").value = range("A1").value + 1 inside a loop is there a
performance penalty compared with saying: count = count + 1 and then writing this to a
cell outside the loop, eg: range("A1").value = count ?
View 9 Replies
View Related
Feb 16, 2010
I use a lot the function “Find” in Excel but the problem is that it takes a lot of time, so I’m searching for another function or code that can be faster than that, the Worksheets that I use in Excel contain thousands of sheets so it takes hours to execute the Macro.
View 10 Replies
View Related
Dec 19, 2009
Have an optimization question for you. I'm starting to try to optimize my macros and I've heard/read it's best to not activate or select anything. - I assumed that meant it would be more efficient to run code without it.
I have a loop I run through about 600 times that takes .75 to .85 seconds to run through with the following piece:
View 5 Replies
View Related
Apr 19, 2006
I have 4 Loans of various interest rates, balances, and minimum payments.
Assuming I have a certain amount of money to pay out each month, how can I minimize the total amount I pay over the lifetime of the loans?
Given:
Total Monthly payment: M
Interest Rate for each loan: R1, R2, R3, R4
Initial Principal for each Loan: P1, P2, P3, P4
Minimum Monthly Payment: Min1, Min2, Min3, Min4
Each month, how should I distribute M over the 4 loans?
View 9 Replies
View Related
Nov 7, 2009
I am running Win XP and Excel 2003.
I have a macro I found here on the boards written by Lenze to delete an entire row based on what is found in column A. I would like to delete any row where Col. B contains 10 or less characters and I have modified it to do so (or at least I think it does). My problem is that it takes about 12 minutes to run the macro (I have about 50k lines to run through). I was wondering if this is the fastest method or if it examines things other than just column B.
Sub Test()
Dim i As Long
LR = Cells(Rows.Count, "B").End(xlUp).Row
For i = LR To 2 Step -1
If Len(Cells(i, "B")) < 11 Then Cells(i, "B").EntireRow.Delete
Next i
End Sub
After this runs, I am left with Columns A to somewhere around AH. The columns are generally in the format of text followed by a numeric column. An individual text column has the same name through all of the rows. The numeric columns have varied values whether negative or positive.
Ideally what I would like: If a given cell (ie. C2) in Row 2 is numeric, then copy the cell to the left (ie. B2) into (ie. C1) and then delete Column B. I need this to work for multiple columns from B to C, skip D and E, and then from F to AG (and maybe beyond).
View 9 Replies
View Related
Mar 12, 2009
I have a group of cells in E2:E4 that are 144.00 in, 240.00 in, and 72.00 in and are formatted as general.
I get these values when I export data from an AutoCAD program to Excel and I want to sum these values to get the total length.
I want to sum the three values and have the result be the same (i.e., ###.## in). The number of values can go to 30, so I need a simple line of code, =SUM(???)
View 9 Replies
View Related
Feb 17, 2007
I need to be able to open or import a csv file and have the data be seen as text, not numbers. I can export programming from a phone system as a csv file, modify it and import it back into the phone system. some fields such as "seconds" are in the format 00. Excel sees it as a number and converts it to 0, which causes an error when I try to import again.
I have tried changing the csv to txt and copying it to a blank worksheet and then recording a macro using text to columns, but each export can be laid out differently, so a fixed array doesn't work. there can be over 100 columns so doing it manually and changing each column to text can be quite tedius.
View 5 Replies
View Related
Feb 5, 2009
if there is a quick way to add any number of tabs to a spreadsheet.
ie one particular spreadsheet i need to have 32 sheets (tabs) but am getting seriously bored to right click insert worksheet time after time. there must be a quicker way?
View 6 Replies
View Related
May 17, 2006
I use the first six numbers of a SA Identity number to calculate the age of a person as these six digits are actually the year (yy) followed by the month (mm) followed by the day (dd), I am born 01 September 1962, and therefore my first six digits are 620901.
Assume the figure 620901 is placed in Cell A1
Now, in another cell, say A4, I have a future date, but this value is formatted as dd/mm/yyyy. Assume this value to be 01/10/2017.
Firstly, I'd like to know How old I AM at that date and secondly, just because I am battling so, how old I will be on my next birthday, because adding the figure 1 to a total has never been so useless - it just doesn't work! I tried adding all sorts of numbers for months and days in a year but there was no consistency.
So here is my question: Simple - how do I get this to work?
On 1/10/2017 I will be 55, or turning 56 At Next Birthday. I have the following function that gives the answer of 55, but not 56 ANB even after 1 additional year is added to the function (the cell is formatted as yy):
Cell A6 Function = A4-DATE(LEFT(A2,2)+1900,MID(A2,3,2),MID(A2,5,2))
Also, ON my birthdate, 01/09/2017, it says that I am 54, and it has to be wrong because I would have turned 55, unless the function uses time and not just the actual date somewhere. On the next day it does, at least, see me as 55.
how to add calculate what my age will be AT MY NEXT BIRTHDATE for any given date in A4?
View 4 Replies
View Related
Jun 4, 2008
The goal of this code is to check between two sheets, if it finds a match between both sheets (somewhere in column A) then it should copy E,F,G columns from Sheet2 to Sheet1..
The Sub Test()
aLastRow = ActiveSheet.UsedRange.Rows.Count ' last row of active worksheet; assume master is active sheet .....
View 9 Replies
View Related
Jul 31, 2008
I've been having a play with sorting integers in A1:I1 (because that was the example in Wikipedia). I've got some code that kind of works, but the recursion is entirely manual.
(I know it's only a mickey mouse example, and no practical use!)
Sub quicksort()
Dim iPivotIndex As Integer, iPivotValue As Integer, iTempValue As Integer, iStoreIndex As Integer, i As Integer
Dim iStartNumber As Integer, iEndNumber As Integer
'how do I give it the original iStartNumber and iEndNumber
iStartNumber = InputBox("Start")
iEndNumber = InputBox("end")........................................
View 9 Replies
View Related
May 4, 2009
Auto-Numbering
just an example:-
56
57
58
59
60
The Column above is the first column on a selected sheet.
i will select 56 and from there (End-Shift+Down arrow) which selects all the values from 56-60...
My question is from here on if there is a shortcut key or 'vba macro' that can autonumber from 1.
Thus giving output result of..
1
2
3
4
5
i want to record the solution for above problem in a macro recorder for different numbers that is why i have to do (End-Shift+Down arrow)
View 9 Replies
View Related
Oct 13, 2009
I am doing a "Lost and found" spreadsheet project for a hotel.
Each row contains the lost items with all the necessary info in separate cells (Room nr, found by, reference nr, client name, etc)
Every item is being bagged and then a label is put on it. ( has the same information but its just arranged prperly. (Like a label )
What I would like to do is to have a column in each row that has a "print" button. What would happen is that the macro would create a label in sheet2, print it on the default printer and then erase everything on sheet2. (I am kind of new to macros so I dont even know if I need to use the sheet 2 for it).
I have already done a kind of a template for the label and set it up on sheet2. I would probably manage to get the print function working with the online tutorials(if not i'll be back) but can't figure out how to do the first bit of it.
View 9 Replies
View Related
Feb 16, 2010
I have two spreadsheets, each with about 50-60 separate worksheets in them, and each spreadsheet has simarly named tabs. I am trying to replace the values in column Q on spreadsheet 2, and move them to column O on spreadsheet 1, on each worksheet.
Is there a way to do this quickly, or will I have to copy-paste many times over?
View 8 Replies
View Related
Mar 13, 2013
Looking for a formula to convert time (00:00:00 AM) to a general 4 digit number (0000). Example 08:30:00 AM ---> 0830. Tried using format cells but no luck.
I was able to convert a standard number to time using the following formula: =TIME(INT(H2/100),MOD(H2,100),0)
View 2 Replies
View Related
Jun 30, 2013
Is there any excel-addin which offers function in which you could search any excel function (not just formula) so that you could access any function in a seconds, just like google desktop search for windows.
Right now you could use quick access for most common functions you use and shortcuts for functions in different group. But if there is universal search for all the functions, it is going to be faster for any access!
View 2 Replies
View Related
Oct 28, 2013
I just had an employee come to me and ask to take a look at his Excel spreadsheet. One of the columns auto filled the word General all the way down. I tried to do a find and replace but it did not work. Then I tried to just delete the cell and noticed that the text disappears when you click into the cell. This "ghost" text is frustrating me and I can not get it removed
View 1 Replies
View Related
Apr 28, 2012
Below is a sample. I was given a spreadsheet and it contains fractions formatted as General. I need to do a calculation based on the fractions. Example there is a number in A1 lets say 3 and a fraction formatted as general in a2 1/216 and I need to multiply A1 * A2 but obviously I get a value error. If I go to another sheet and format the cell as a fraction then the multiplication works. My spreadsheet is full of the general formatted fractions.
A1321/2163Â 4Â 5Â 60.0138888897Â 8#VALUE!Spreadsheet FormulasCellFormulaA6=(A1*1/216)A8=A1*A2
View 3 Replies
View Related
Sep 20, 2006
For each account i download there are hundreds of pages of journals and entries, in the header of each page is information such as currency, the entries do not have have the CUR code (ie AUD, USD,CAD etc)
What i need to do is;
1. copy the CUR code to each entry line so that when i sort the data i have the CUR code to each entry
2. Copy the data to a "master" spreadsheet in it's unique tab if Column A begins with N and M.
View 9 Replies
View Related
Jan 8, 2007
I have a cell that is constantly being changed from a date to a number. Excel automatically formats the cell as a date when a date is entered but if I re-enter a number it leaves the format as a date.
Is there anyway to automatically change the format back to general if a date is not entered.
I had this bit of code obviously it dosent work.
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("E8:E46")) Is Nothing Then
If IsDate(Target.Value) Then
Target.NumberFormat = "d/mm/yy;@"
Else
Target.NumberFormat = "General"
End If
End If
Cancel = True
View 4 Replies
View Related
Aug 27, 2009
I have a folder that contain a list of files in shortcut type. So that i can just click it without going to different drives and paths to open those files and it really save my time.
After updating those files, i need to zip it up. There are many files to zip up and it takes time by going to its path one by one. Do you guys know is there any method to zip up those files without going to its relevant drives and paths?
View 14 Replies
View Related
Feb 11, 2012
I wonder if there is a way to enter my formulas as an array formula using Control-Shift-Enter in a more time saving way than one line by one line
I have 600 rows of array formulas all in column E but at different intervals with some blank rows inbetween
The formulas are in place but just need the CSE to enable the array formula { }
I am not looking forward to line by line entry.
View 1 Replies
View Related
Sep 12, 2013
Is there a quick way to remove duplicate values from a ROW. The "Remove Duplicates" operation under the "Data" tab only works for a column selection and not a row selection.
One approach I am thinking about, is to transpose the row into a column, run "remove duplicates" and then transpose again into a row.
View 1 Replies
View Related
Feb 18, 2009
create a MACRO/module that will take teh above example rows 1 thru 6 to look like rows 9 thru 30. See below ....
View 9 Replies
View Related
Feb 5, 2014
I need to add some HH:MM values to give a total general value, its for a staff rota, i need total values for the hours worked per day/week.
eg. three 8 hour days in 08:00 format to give a total of 24
View 2 Replies
View Related
Aug 10, 2009
My excel recently the General number format so that when the general number fomat is used and I type a number in a blank cell with the general format, the number is always divided by 10.
E.g., I enter "102", the number is automatically improperly converted to 1.02.
However, if I enter "=102", the number is properly entered as 102.
How can I reset the General format to the original setting without this divide by 100 problem?
View 3 Replies
View Related
Feb 18, 2013
I have a spreadsheet with a column full of dates
I used the formatting wizard to make all dates the format of dd/mm/yyyy but, now, when I put a date in the column it throws up a number '41313' instead of a date, even if I manually input the date in the same format as above.
Also, I have tried to turn cells back into general number fields, but this doesn't appear to be changing it back to a date format..
There are HUNDREDS of dates I might have lost if I can't get them to start showing as dates again!
View 2 Replies
View Related
