I have a spreadsheet which has "Employee: [agent 1 name]" in column A and it may or may not have the word "Break" in the same column before it mentions "Employee: [agent 2 name]". The amount of data between agent 1 and agent 2 varies and am needing code which will insert a row above "Employee: [agent 2 name]" if "Break" is not found, and add the word "Break" in column A on the inserted row. I would need this to loop through the spreadsheet until all 100+ agents have been searched.
I'm also needing this done for the word "Meeting" and would insert a row 2 rows above the next agent.
I am trying to lookup if a string like a name exists in another string which has a buch of values for example to see if andy exists in a string which has data like andy;sandy or sandy exists in andy;sandy
I have an formula if statement that returns "deletethisrow" if the test is true.
For every occurence of "deletethisrow" I want to delete the row. The number instances will be variable each time I run the file. So maybe it will find that string, maybe it will find 10 instances. I want to do some kind of loop that won't error out when it cannot find "deletethisrow", but will delete the rows for each instance where it does find this string.
I know it was verbose, but if I just do a loop for a fixed number of loops it will error out if it runs out of rows to delete.
Working with a scheduling report and trying ultimately to get it down to a line item report with the associate information and their earliest start and latest end for the week. Already made a lot of fixes to the formatting involving junking empty cells and trash data, but running into a road block on some of the trash data.
Here is the format of the report at this point. Names have been changed to protect the innocent.
Agent: 2366 Bacon, Kevin
Date Start End
So what I need is something that will iterate through the report...such as an while and find the cells in column A that start with "Agent:" It needs to compare that cell to a stored value to see if this "Agent:" is a repeat. If it is then it needs to delete that row and the next one and shift up. If it is not then it overwrites this "Agent:" with the previous one in the stored value and continues the while.
I know what I want to do...but not shure on the code cause I am not great at VBA.
Oh...if it matters probably need to allow for about 10k line items to iterate through...right now report is at 6k...but allow room to grow.
I have a column with text that can contain a number of words one of which is 'UK & Europe'. I want to have another cell which will contain the value EMEA if the the cell mentioned above contains the word UK. how to do this as I'm stumped... I get a #value in cells that dont contain the word when using the formula =IF(FIND("UK",H27),"EMEA","ROW")
I'm using the following code which is working well. It finds an instance of the string "A83". Once it finds it, it moves down x number of cells and populates it with a new string. It then moves down another x number of cells and populates it with another new string, etc.
The problem is, it is only looking for "A83" one time and performing the appropriate actions at that point. What I need to do is look for *every* instance of the string "A83" and perform the appropriate actions.
I have a table of data and i need to search the table for 8digit numbers, that are beginning with "2" and copy the numbers to the column on the right. A cell can contain multiple numbers with 8 digits.
I use the following code to search through a database and paste the results in a separate worksheet.
Private Sub TextBox8_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) sheets("Search Results").Unprotect Dim cell As Range Dim foundcell As Boolean If KeyCode = 13 Then Application. ScreenUpdating = False Application.enableevents = False If Not TextBox8.text = "" Then For Each cell In sheets("Detail").Range("e2:e" & sheets("Detail").Range("e65536").End(xlUp).Row) If cell.Value = TextBox8.text Then foundcell = True With sheets("Search Results").Range("e65536").End(xlUp).Offset(1) .Value = cell.Value .Offset(, -4) = cell.Offset(, -4).Value .Offset(, -3).Value = cell.Offset(, -3).Value .Offset(, -2).Value = cell.Offset(, -2).Value .Offset(, -1).Value = cell.Offset(, -1).Value .Offset(, 1).Value = cell.Offset(, 1).Value ................................
I'm getting an input string from the user in an excel sheet(e.g. input.xls) .I have to search for that string in a group of excel files present in the same folder in which the input.xls file also present.The range of that cell in which the string is found (e.g. A1 ) has to be stored in a variable.
I know that Vlookup is an option but the number of excel files to be searched is more that 50.
If I have a store number (say 1234) and I am trying to search for something which contains 1234 (i.e. the whole string of ABCD1234, ABCD1235, ABCD1236 etc) is it possible to do this in one VLOOKUP formula?
One way of doing it would be to VLOOKUP("ABCD"&"1234",....) but I would rather do it the other way around, to prevent the formula from not working.
1) search column A for a string 2) as that string is found, move the entire contents of that cell to the same row, column F 3) insert a blank row above the row where the text was found 4) continue to search and perform the same steps
I was able to come up with the code to find a single instance and perform the necessary steps, but I've tried 'til I'm blue in the face to incorporate the FindNext object to parse through the entire worksheet. I thought I was on the right track with the attached code (which could be completely wrong), but it returns my MsgBox "Value Could Not Be Found."
I have about 700 cells I want to interrogate. Within each cell the following text appears “Estimate – BOLB/02/1234 – Some more text here”. I want to search all the cells and delete the cell contents but leave the “BOLB/02/1234”.
The problem I have is that the string is different in each cell but the format is the same. It always starts with BOLB followed by /, followed by 2 numbers, followed by / followed by 4 numbers i.e. BOLB/**/****.
Column A contains an alphanumeric string up to 50 characters in length. I need a formula to search the string in Column A for the word "Appeal" and place that word in Column B when found. Likewise for the words "Grievance" and "Complaint". If none of the three words are found in Column A, the contents of Column B should be blank. Here's the formula I've played around with (it works on the first line but returns #VALUE! on following lines):
I manage to do a proper search and the return value is correct, but the problem is when I don't get the correct value excel gives me an error, what I can add to make the result just to give me MsgBox "Not found"?
Set Ran = Worksheets(2).Range("A:A").Find(CompName, lookat:=xlPart) If Not Ran Is Nothing Then MatchRow = Ran.Row MatchCol = Ran.Column End If
I have a spread sheet with a large amount of tabs and I want to search against a part number that would only ever appear once on each tab and return the number in the cell to the right of it using vlookup against each tab. is this possible?
I have 5 sheets in my workbook and I need to find out if a string exists in any of them. The string would always be in column L of the work sheet. I have searched many places but cant seem to get anything to work, is this possible?
I am busy building a spreadsheet that must do the following.
A B C D E
Above is a basic example of the data I am using What the spreadsheet must do is it must search cell B1 for the key word in in cell E1, if it does not find the keyword, it must search the same cell for the keyword in cell E2 and so on if it finds the key word it must return a consatination of cell A1 and the Keyword.
This is easy enough to do using the search function together with a couple of nested IF statements but where the problem comes in is that I am needing to run the search on about 12000 lines using over 10000 key words.
currently I have sheet with a formula in Column A containing 40 nested IFs searching for the first 40 key the same formula in Column B searching for the next 40 keywords and so on but to search 10 000 keywords(Client name or Client number) I am needing to apply this formula to 250 columns over 12000 line which makes the sheet very resource incentive.
How do I streamline this to make the spreadsheet more efficient
I need to find a formula which will search a list of cells looking for a particular text string. If it doesn't find this text string it then needs to search for the next one. For instance if I had a list of product codes: ABCD1234, BCD1234, ABCD2345, CDE23456, BCD2345 I want the formula to look up and see if the cell contains the text ABCD and if so return ABCD as the value, if not I want it to go on and see if the cell contains BCD and return the value BCD, if not then go on and search the value CDE etc. It seems like an easy job to do manually but I have a list of over 3000 codes to do this.
I have the below data to look at every day. The Data codes column A contains a part number and the Info Column B, the information about the part. I have in column C the serial numbers that change daily, I copy and paste this column in everyday.What I would like to do is search within the serial numbers for the data codes and return in the Result column D the info relating to the data code.
Currently I spend hours removing all of the text and symbols (there are normally 2500 to 3000 serial numbers) and then do a simple vlookup. Is there a formula that could search the serial number to find the data code? Or some code I could use in a macro to do the job for me?
The serial numbers change daily but the data codes and info in the first 2 columns remain contstant - there are nearly 250 data codes.
I have a spreadsheet with a number of columns containing comma delimited strings (years) which I need to check cell by cell and return false if anything apart from the years 2001 to 2008 is found.
For instance, a cell may contain the years (2001, 2005, 2006, 2007, 2008) or (2006, 2007) or (2001, 2004, 2008) or (2004) or any combination of those 8 years.
I thought of using the Split function on each cell and then looping through the resultant array to do a comparison against each of the 8 years but with a large number of delimited strings to check it could be a bit time consuming. Any idea how I could accomplish this more quickly and efficiently either with a formula or VBA?