I am trying to get the following equation to search for cells that contains the term 3R. The issue is I have several different 3R formations as follows and want it to count all of them.
3R
3R FIB
3R S Nasty
3R S Nasty FIB
This is what I am currently using but I want it to be more detailed. Where my search is for everything on 1st down with 10 yards to go in a 3R formation.
I have vba DATE variable 'FirstDate'; I assign it from a cell with say, 01/12/08; How do I then use that variable to search range of dates, without using the day and searching by only month and year?
Currently, I get the error: "Run-time error '91'; Object variable or With block variable not set" at the point of With Selection.Find(FirstDate).Select
I have been going in circles many times on this type problem: managing clumsily to resolve in the past using: Format(Firstdate,"dd-mm-yy"), text-to-column, NumberFormat etc
These cells contain a time reference either in '09:30 - 16:30' format or just an 'AM/PM' format.
What I need to do is to populate column B with the part of the string that comes AFTER the times in the text (either in hh:mm format or AM/PM format)
Example 336 Data 2012 Classroom Induction 2012/08/28 09:30 - 16:30 data about this ------> data about this 336 Information about this 2013/04/04 09:00 - 13:00 Information 2 ------> Information 2 336 info CHS Suffolk 2013/05/07 PM DRC, BSE ------> DRC, BSE 364 information 2013/02/12 AM DRC, BSE ------> DRC, BSE
I cannot get this to work and cannot use Text To column as there is no character I can use to split it.
Dim vCriteria3 As String vCriteria3 = ActiveCell.Text Sheets("CUSTOMER OVR REVIEW").Select Rows("25:25").Select Selection.Copy Rows("26:" & vCriteria3).Select ActiveSheet.Paste
it hits the
Rows("26:" & vCriteria3).Select
and crasses with a "incorrect type" mesage. am i storing this into memory incorrectly? how do i fix it? the function is intended to use a variable to paste over x amount of rows.
I have 2 sheets, Sheet1 and Sheet2. I want to populate Sheet2 Column B with a formula which will reference Column B of sheet1 so that on each iteration of the For..Next, the cell being referenced in the formula will change. So if i = 5, I'd like the cell to be populated with the formula =Sheet1!B5
Ive wrote some code that goes into a workbook. This code then opens up a "master" workbook which has 18 blank tabs in it and then proceeds to open up 18 other named workbooks one at a time and copy some date from these workbooks to the master (i.e workbook 1's data goes to the master workbook on tab "1".)
I have this working no problem but here is my snag.
The workbook name changes every week to correspond with the date (i.e 1_14DEC2011.xls then 1_21DEC2011.xls).
Ideally id like to be able to create a variable for the latter half of the filename so that i can apply this variable to each filepath but i cant quite get it to work.
A small sample of my code is below (and yes i know it is very blunt but so is my knowledge at this stage!)
Code: Sub collate() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual
I found a thread that is very much similiar to what I tried to achieve that is to save 110 files into CSV file format here Save Multiple Files In Folder As Text Format.
May I know what changes should I make to this code in order to save it into CSV file format?
1 I need to open a csv file in order to use data in it at location "C:BarkingEMCIN" to input into an xls spreadsheet. I then need to use the info from the xls spreadsheet along with other info input by users to produce another .csv and it be deposited at "C:BarkingEMCOUT"
The name of the file at "C:BarkingEMCIN" will change everyday or even multiple times a day and be in the format "AVA_DA_260208_BPL_EDF_001.CSV" "AVA DA " will remain the same "260208" will be todays date the next 2 will be interchangeable depending on requirements and use BPL EDF and SSE will be interchangeable depending on requirements and 001 will be the version whcih will be changable. _ underscores will be used as seperators
They will then be save in the format "NOM_DA_260208_BPL_EDF_001.CSV" using the same prinipals as before but automatically saving as the next available when needed with a prompt to tell the user what it will be
I have 800+ files the problem is that the file name ends in 80 different combination so I need to try all of those for each file.
eg: one of the 800 is "109 st no 103 av" the file could be called: "109 st no 103 av nb1_cleaned.xls" or "109 st no 103 av nb 1_cleaned.xls" or "109 st no 103 av nb 1._cleaned.xls" or "109 st no 103 av sb1_cleaned.xls" . . . .etc
I wrote a code to try all those combinations, the issue lies a space the code adds before _cleaned, how to remove it?.
So the name should be "109 st no 103 av nb1_cleaned.xls" but my code is letting it be 109 st no 103 av nb1 _cleaned.xls
A | B _______________________ WD |100 From Bangalore CR |Hyderabad 50 CR |Mysore 70 WD |900 From Kolkata _________________________ and so on in A and B columns. In the C Column depending on the cell value I want just the places like Bangalore, Hyderabad, Mysore, Kolkata etc only to be displayed like shown below.
A | B | C ___________________________________ WD | 100 From Bangalore | Bangalore CR | Hyderabad 50 | Hyderabad CR | Mysore 70 | Mysore WD | 900 From Kolkata | Kolkata ______________________________________ The Places in column B are not at finite position and that is the big problem i am facing when using Left or Right function. Could anyone help me with a proper Excel function to get this done. The thing is the whole cells should be searched and accordingly the output should be obtained.
I am trying to search three columns on a worksheet, that contain a range of customers, product names and the amount of that product sold to the to the customer. On a separate worksheet I have created a table, which I hope will show the customer, the product and the amount sold. So basically I need either a formula or piece of code that can match the customer and product, along with the amount sold and display it in one table. The data is by nature not kept in alpha or numerical order and my problem lies in being able to search through each row and extrapolate the necessary figure.
I have a column that is filled with text of varying lengths and I'd like to search through each cell in that column looking for a specific word or words. Unfortunately the length of the text varys greatly between each cell and there is very little uniformity so I need to be able to search through the entire entry in each cell and then highlight that row if a specific word or words are found.
I'd also like to be able to add a number "1" in another column on the same row if the search finds a word or words. Any help would be greatly appreciated.
If the cell only contains the exact word or string I'm looking for then it's easy but I can't figure out how to search through text in a cell that contains more than I'm looking for.
example: Lets say I'm loooking for "caught fire" column Q contains: 1 "The computer caught fire after several hours" 2 "A house on the hill caught fire"
If "caught fire" exists in the cell being checked, then highlight the row and put a 1 in a specific column, lets say J.
Would like to write an IF statement where it reads if any cells in a column are highlighted and gives the sum of those highlighted while ignoring the ones unhighlighted.
Is there any way to do this or an alternate method that would possibly work?
Concatenated cells. My issue is how do i search through a sheet with cell values that have been concatenated?
Lets take an example:
I would like to search for a string call sales in sheet1 which would display all the employees and schedules for that department (do note that employees can be part of not just one department). but my cell formatting is as follows:
Sheet 2, Column 1 Employee name
E.g. Row 2 Employee1
Sheet 2, Column 2 Department (cells concatenated)
E.g. Row 2 Sales, Marketing (Concatenated Cell)
Sheet 2, Column 3 Work schedule
E.g. Row 2 8 AM to 5 PM
So when you search for sales, it will display employee1 and work schedule.
The following macro searches for missing combinations. This macro will search the complete list and will return any missing combination from "1, 2, 3, 4" to "7, 8, 9, 10".
I need to make some changes in this macro, so that it will search for missing combinations only within a specified range of cells (and not the whole list). For example (see excel file attached), I would like to place a search within range("G23:J183"), from combination "1, 2, 6, 9" to combination "4, 6, 8, 10". In this case, it should return only 9 missing combinations.
Attached File: Example Find Missing Combinations.xlsm
The following code works fine to determine if a particular character occurs within the selected range of cells:
Code: Sub CheckIfCharacterIncluded() For Each MyCell In Selection If InStr(MyCell.Formula, "#") Then MsgBox ("The " & "#" & " character was found in cell: " & MyCell.Address & " at position " & InStr(MyCell.Formula, "#")) End If Next End Sub
However, I would like to extend this functionality to check for multiple characters, using some sort of array that contains all the characters I want to check for e.g. "#","*","£" and so on, without having to repeat the above code for each character for which I need to check.
The easiest way of explaining what I'm after is to say, I have letters of the alphabet, in their own cells, and I want to find them by way of a search. I don't mind how this is done, but it would be good if for example you entered A, C and E, any cells containing those letters changed, maybe became bold, or the cell filled with colour.
I have a workbook with a macro that searches through worksheets to find certain values and place the location of the value in a worksheet cell. The following line ontains the found cell.
I need to search column C for the text "X". If an "X" is found, I need to copy the text from the cell directly left of it and paste it into a seperate worksheet into cell B2, then go back to the original worksheet and delete the cell with "X" on it and the cell to the left.
I would like to select all cells in column A that begin with the text "SP". Some cells will be contiguous but others will not. For instance in one case, I would like the macro to select cells with the text "SP" which would result in cells A1, A2, A3, A10, A15 being selected. I am working on building a macro that will then do other things to these cells/rows so this is the first step.
The below code will select the first cell with "SP". How would I alter this code to only search Column A and select multiple cells? or totally different code.
Code: Sub test() Dim r As RangeSet r = Cells.Find(what:="SP", LookIn:=xlValues, lookat:=xlWhole) If Not r Is Nothing Then r.Select End Sub
I'm wondering if there is a way of linking to cells by looking up part of the link in another cell. E.g.In Sheet1, cell A1, i have some data that I want link to from Sheet2. Normally I'd do that by using "=Sheet1!A1" in the cell in Sheet2.
But I'd like to lookup the "A1" part of the link from two different cells in Sheet 2. Something like: =Sheet1!(Text from B1 in Sheet2 AND Text from A2 in Sheet2) or: =Sheet1!(Sheet2!B1&Sheet2!A2). So that if Sheet2!B1 would contain an "A", and Sheet2!A2 would contain a "1" the resulting link would be the correct "=Sheet1!A1"
Column A & B has a list of Supplier Part numbers and Buyer Part numbers as below.
Supplier P/N Buyer P/N
HGFYE/12 111111
HYEYDH/14 222222
[Code] ..........
Cell D2 is an open cell that any data can be entered into as a search term. What I am trying to do is search for a Supplier P/N that have the characters "H", "G" or "E" in it, so entering "HGE" into cell D2 would display the results into columns F & G as below.
Supplier P/N Buyer P/N
HGFYE/12 111111
HDGTEY/56 333333
I can easily do a formula for 1 character or a string of characters.
To complicate it further, if the search term has in this example has "YFF", I would like the same formula/code to workout that the result in F & G should show this time
so this gives a part number depending on what number is placed in O6. What I need to do know is look at 2 different cells and for each combination of numbers give a different part number. so if A1 is 2 and B1 is 3 give a certain result.
Can I use part of the data in a column of cells to sum data in a column.
E.g. I want to sum valves in Column V that match the Data in Column T by using the following cells CB 1A1x, CB 1A2x, CB 1a3x CB 1A5, etc etc CB 1B3x...... to obtain the totals
