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 RepliesOptimize 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..............
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 ?
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:
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.
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?
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).
I have a range of cells, for this example I will use 2.
Cell E17 = 77/170
Cell E18 = 8/9
Using the following formula: =SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)))
This bring back an #VALUE! Error as the second part of the formula keeps picking up "/9" however the first part works fine, displaying "170"
Now if I use:
=SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)-1))
It all works. The problem is that I need this to be automatic using the above way means having to add a "-1" to every formula for a cell with only 1 char to be added.
Using the formula:
=SUM(RIGHT(E17,FIND("/",E17)-1))+SUM(RIGHT(E18,FIND("/",E18)-1)).....
So I have 2 worksheets. One has a list of Player Names. The other has a list of Plays that any of these players could have been involved in (or they may not have been involved in).
I am trying to use the find function to find anyone of those player names from sheet 1 in each of the plays. My functions currently stands at
=FIND(('Dinas Roster'!$A$2:$A$21),I6,1)
Where Dinas Roster is the list of players and I6 is a particular play and would go to I7, I8....which are the plays,
This isn't working as I am getting all #Value. If I just do it for 1 Player (i.e. Dinas Roster'!$A$2) it works and returns a number on each line that player shows up in...
I need a find anywhere function.
Example
text to find is in ZZ1
area to find the text is A1:BB500
It could be anywhere in that area.
I don't want to do the find command 1000+ times for all the data i need to search for.
Vlookup is just column A, i need column A:BB
I have no idea what column or row it would be in.
basically, look for text from sheet2A1 anywhere in sheet1
I'm looking for something simple that will let me type in a 5 digit numeric value and then when it finds the associated value, will add +1 to the associated col/row.
Such as, type 11111 in the box, click the button...When it finds the data on say row 8, col A it will then add +1 to row 8, col G.
I'm not sure of the best way to do this...I'm not exactly new to excel, but it has been a VERY long time (10 years!) since I've used it in this aspect...Just trying to help a friend out.
I have this source data that has different types of currencies in it. It will be processed by a marco. But Before i do the processing, i would like excel to check if all currencies in the source data have had their rates determined by the user in another worksheet. This is the code i am using. However, when the marco trys to find rates that has not been determined (cannot be found in remarks sheets), it will just skip the msgbox code.
Find = Cells.Find(What:=local_currency, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select
If Find = True And ActiveCell.Next.Next.Value <> "" Then
Find = Empty
Else
MsgBox "The currency " & local_currency & " does not exist in the currency list"
Sheets("Remarks").Select
End If
I want the create the following summation function (probably with the use of FIND).
You have 3 columns:
- Column A containing values; a, b, c, d, e
- Column B containing values: 1, 2, 3
- Column C containing ad randum values f.e. lengths
I want the achieve a summation of the lenghts with the following conditions 'a' in column A AND '1'in column B.
Apart of this a summation of the lengths with the conditions 'a' in A AND '2' in B.
[Code]....
In Sheet 2 i have a 1000 of data contains the birth date of following customers
The result i want in Sheet 1 is
particular on todays (Say on 27-11-2008) date how many customer are having birthday, supoose there 10, or 8 wahtever should show me the list.
I tried this formula
=INDEX(Sheet2!$B:B,MATCH($B$3,Sheet2!$H:$H,0))
but by this formula it only show one customers birthdate what i want if there 10 differenrt customer those same birthdate it should display all the 10 date and name in diff rows
Attached is a sample in which Column"B" contains Total IDs and Column"D" contains worked labour IDs. Now I want the IDs of labour who have not worked in Column"F".
Need a function that returns the days from the last week of a month?
View 3 Replies View RelatedI need to try to cobble together a function nest or whatever I have to do, which will tell me if the number 9 exists anywhere within
a range of cells. The 9 could be just one character of a larger number (i.e. 1496), or it could be just a nine. I know that I could
use filtering or use the find feature with the dialog box, but I need a formula instead. Also, I just need to know a true or false;
I don't need to know where the nine is or any other info. I just need to know if it is there.
I have a column of values (text) to choose from. I need to randomly assign them to names.
There are more values then names.
Each name has to have a value.
Each value has to be used only one time.
Not every value has to be used.
Column B contains names, column Q contains values.
Now. So far I've come up with below:
Code:
Sub randomize()
Dim Random As Integer[code].....
"Random" returns exactly the amount of values in column Q, so this works ok.
"i" counts iterations correctly, meaning as many times as names in column B.
I tried useing Match to rule out already used value but this code returns "unable to get Match property of the WorksheetFunction class" error.
I have a worksheet with dates on and have user form to display dates within a range. I have created some code, but the find function errors and says it cannot find this value on the sheet, but it is definatly there. This is my code: I have added an asterix to where it errors and says it cannot find the value
Code:
Private Sub SearchButton_Click()
Dim lbtarget As MSForms.ListBox
Dim rngSource As Range
Dim FoundFrom As Range
Dim FoundTo As Range
With Worksheets("Calender")
[code]....
In a database, i need to search and select the cell that contain a given string. If found, from that cell range, i want to grab some info using the offset command.
I'm trying to use the Range.Find function. I got an error on my formula
what: "IP_"
From cell ("T1)
Look in Whole cell
Look by Row
XlNext
[CODESub GetInfosFromData()
Dim st As Range
st = WorksheetFunction.Range.Find("IP_", Range("t1"), , xlWhole, xlByRows, xlNext, True)
If SG IsNothing then ' A match occured
' do the process
End Sub][/CODE]
how do i tell the subroutine to look for any subsequent match (Next)
I have two spreadsheets, which have over 10,000 entries and I am trying to compare them both for duplicates. I would like to know whether or not there is any VBA coding which will allow me to copy a cell from one spreadsheet and look for it in the other spreadsheet.
The major problem which I am facing is the information which I am trying to look for is constantly changing and therefore is more or less unknown to me. I am hoping for some VBA coding which will allow me to copy whatever is in cell A1 and find it in the other spreadsheet which I have, and then do the same for A2,A3 and so forth.
how to make the << and >> buttons as well as the find button work on this form. Here is the code for the >> (next Record) button. I think if I can understand how one of them is supposed to work I can do the rest. I have been trying for days to figure it out, and even with RoyUK's help I still can not get it to work....
View 9 Replies View RelatedI am having trouble with the following code. When I put in a value in C8 that is on the list being searched (A2:A27), the Answer is still coming up as false (ie, the find function isn't finding the variable in the list, though it is there). I'm guessing I'm using incorrect syntax somewhere.
Dim Answer As Boolean
Private Sub CalcBi_Click()
Dim Pledge, Edate, PR, PPA, EEA As Double
Dim Due, Chdate As Date
If Range("C6").Value = "" Or Range("C8").Value = "" Then
Exit Sub
Else
Pledge = Range("C6").Value
Edate = DateValue(Range("C8").Value)
Set rngschedule = Worksheets("Bi Weekly Schedule").Range("A2").Offset(Application.WorksheetFunction.Match(Range("C8"), Worksheets("Bi Weekly Schedule").Range("A2:A27"), 1), 0).....................
What I'm doing in this script is taking a whole bunch of variables and pasting them into a seperate sheet "Checks" which will construct a cashiers check. The problem is where I've highlighted the code in red. At that point I have copied the Vendor name to the clipboard - I then go to the "Vendor Info" page and search for that Vendor name. If that name exists on the page it works perfectly...moving one column to the right, grabbing the Address Line 1 and dropping it into the check, then going back and grabbing the Address Line 2 and dropping it into the check. The problem is when that Vendor name doesn't exist in the "Vendor Info" page...I want it to just paste two blank cells into the check (because I obviously don't have the address info for that Vendor)...but instead it gives me an error:
Run-Time error '91':
Object variable or With block variable not set
Sub Checks()
'
' Checks Macro
' Macro recorded 2/14/2007 by Derek Minner
'
' Keyboard Shortcut: Ctrl+Shift+P
'
Selection.Copy
Sheets("Checks").Select
Range("I4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
I am comparing two series of data, a daily series and a weekly series, to make calculations. The "F" column is daily data and the "I" is weekly.
The formula I am using is:
=IF(AND( SUM(I58:I62)<F62,I63>F63),B64,0)
The data I need to use in the "F" column in place of "F62" is always on a Friday.
I had taken "F62" which is the preceding working day's data. In fact I need to take the preceding Fridays data in column "F" as I need to compare the week to week trends. Due to bank holidays I cannot count back form F62 to get the data. There is always end of week data recorded on Fridays even though it may be a bank holiday.
I have the dates in the "H" column. Can a date reference be used in the formula to find the Friday data in column "F"? Or is there another solution to this problem?
I am writing VBA code that dissects a cell which always contains some alphanumeric characters a colon ":", some more characters, another colon ":" and some more characters.
For Example this is what is in my cell "Deposit:93121:Southern California"
what I need is to cut and paste everything that is located after the 2nd colon ":"
Say that my cell is D433 and in non-vba world I can chop off the first part of the cell to the left of the first colon ":" by using the following formula:
=MID(D433, FIND(":",D433)+1,50)
assuming there are less than 50 characters in my cell, which is a safe number, then I cut and pastespecial this cell as values into the same cell and repeat that same formula and voila, what remains in cell D433 is what I am looking for "Southern California"
how can I replicate this surgical process in VBA, is there a way to combine this process in one command on any given target cell?
I just want to replace the offset with find function. I have attached the sample file with the code. Just need a little change.
View 5 Replies View RelatedI'm in a workbook, and I want to look up a name. CTRLF or clicking on the binoculars both bring up the expected dialog box, but when I populate "Find What" and either hit enter, click Find All or Find Next, nothing happens.
The only thing I can think of is that I created a macro to function in one sheet only, then saved this workbook as .xlsm. But that doesn't seem right.
I know I have to use FIND and MID but I can't work out the right syntax: how do I extract Michael or Dave from the following strings:
F - Michael 8735
M - Dave 093
I'd like an if function to display "Yes" when a postcode is found within a range of postcodes.
For example if I have BT23 4RE in cell b1 and a range of postcodes from say cell a1:a76 which has BT23 4RE in it, it will display yes.