I have a list of Products in my Excel file, I am trying to put them into categories easily. So what I am asking is there a way to select all rows with a certain key word in them so I can easily paste them into my other excel file?
View 1 RepliesI have attached the data in an excel file here. I'd like to select (or highlight) the rows (based on names) in the table on sheet1 based on the list provided in sheet 2. I've been struggling with using a formula for this one. The list on sheet 2 may be longer than the table on sheet 1(since there are entries on the list which might not be present in the table column 2), and the reverse may also be true. I would like to select "entire rows" of names in the table that are there on the list on sheet 2, not just the names column in the table. See the desired result on sheet 3.
View 9 Replies View RelatedI would like to develop a macro that can be run against an excel doc that will highlight keywords from a list I have established.
For example: If my excel doc contains the words, "sales, selling, sell, sale, school," make those words bold, red and 2 font sizes larger.
I need to find 100 or so keywords in a column, I have the list but how would I search multiple keywords as there are 10k columns and it would take forver to search for them manually?
View 4 Replies View RelatedI need to setup a macro that will search for a keyword in a list with about 21000 rows. When the macro finds the keyword it cuts the row out and moves it to another sheet. I'm having a few problems
1. I don't know how to make the function search until it can't find the keyword anymore, then move on to the next word
2. I can't get it to select what it finds it finds the cell but I don't know how to select that cell once its found.
Sub Search()
'
' Search Macro
'
Dim keyword As String
Dim x, y, z As Long
For x = 1 To 53
Sheets("Test1").Select
Range("A" & x).Select
keyword = Selection.........
I have rows of 10 keywords. I want the macros to compare the first row against all the rows except itself, then compare the second row against all the rows except itself, the third row against all the rows except itself, basically looking for duplicates...It will then record in a column for each respective row, the max # of duplicates found with another row..
For instance lets say I was looking at the 15th row, and it looked for duplicates elsewhere. It only found duplicates in the 4th row and the 5th row. In the fourth row, there were 5 keywords common, and in the 5th row there were 7 keywords common. Therefore the max # of common words is 7 for the 15th row, and that is what is recorded.
In addition the number of rows will probably vary it could range from 1 to 500. I included an excel spreadsheet that has visually speaking how this could work, maybe using COUNTIF. Basically it counts the # of common keywords for one row with all the other rows, pastes the count #s to the right, then finds the max of those count #s, copies the max into another column, then clears the count #s, and looks up the next row, repeats the same process. It is easier probably if you look at the attached spreadsheet to get an idea of what i am talking about..
Say I have a list:
VB:
Something something apple
Something something orange
Something something banana
Something apple something
Something banana something
Apples woo
Apples And bananas
Something orange something
Something something apple pie
Something something vegetables
And I want to separate the list into separate columns/lists with certain keywords (e.g. apple, orange, banana) like so:
Apple
Something something apple
Something apple something
Apples woo
Something something apple pie
[Code] .....
The "multiple words found" part isn't a big deal (I'm not sure how I'd deal with it anyways). The "Unsorted" part is there assuming creating the separate lists won't remove the entries from the original list (which would then leave all the unsorted text).
I have two Spreadsheets, lets call it Spreadsheet 1 and Spreadsheet 2.
Spreadsheet 1 has a list of partial employee names in column A.
Spreadsheet 2 has a list of 500,0000 full employee names in column A and their phone number in column B and address in column C through E.
Basically what I want to do is do a wildcard search for the partial employee name, so like lookup Spreadsheet 1' Column A's *name* in column A of Spreadsheet 2. Based on the number of matches. I want to insert that many blank rows underneath the partial employee name, and paste those matched rows there, then continue to the second partial employee name lookup and repeat the process.
This loop has to repeat 1000 times.
I want to delete entire rows between two keywords.
Example:
Row 1 Connecting
Row 2 aaa
Row 3 bbb
Row 4 Unavailable
I want to delete rows 1 through to 3 and loop it until the worksheet is completed.
I have a workbook with part data. Each worksheet is an individual part with its data on that sheet. I'm trying to move certain rows between two key words to a separate wb/ws for the top key word. Data looks something like this:
Supplier 1
row
row
row
Supplier 4
row
row
Supplier 15
row
row
row
As I said, each sheet could have only one supplier or a dozen. The number of rows between each supplier also vary. I was trying something like this to do what I need to do:
Code:
DistiStr = "Supplier 1, Supplier 2, Supplier 3, etc etc etc"
DistiArr = Split(DistiStr, ",")
intDistiMax = UBound(DistiArr)
Set VendorWB = Workbooks.Add(xlWBATWorksheet)
VendorWB.SaveAs Filename:="Vendors"
[Code] .......
I see where my logic problem is: It's looping through a3 - lastrow, but I'm going one row extra each time, so when it goes to the top of the loop again, I'm one row to far starting off. I originally did this because if there's only 1 supplier on the sheet, there won't be another supplier name below it to give me my range.
I'm trying to use data validation to restrict the user to only selecting values in a list which I create. Right now, the list is a named range. I'd like to get rid of the range and just use a named list. I create a name using the following as my list.
Insert > Name > Create
Name: Fruit
Refers to:
banana,apple,orange
When I try to use the name Fruit in my data validation, I get the message "The List Source must be a delimited list, or a reference to single row or column." I thought my name "fruit" was a delimited list.
i want to do is, have a list that picks from a color and then beside it, it will show the possible fruit that is that color for example
Red
apple
cherry
strawberry
OR
Green
watermelon
grapes
The number of fruit will differ by the color so i know i need a counter there, but i am not sure how to list the fruit after i've picked the color
if it is possible to have a selection from a list change the list for another cell?
For example, if I have a drop down list in A1 which is "Apples, bananas, pears", and I select "Apples", is it possible for A2 to have a list of "Green, Red, Yellow", and if I select "Bananas" in A1, for A2 to have a list of "Yellow, Black, Blue"?
I have a table of adressess and clients with contacts ie
a,b,c,d
client,contact,address,select
in the list there are about 300 entries.
what i want is to put an S in the select column on those clients i am due to visit and then these records to be exported into say a word doc to print off so i have a list of visits i have to do with company contact and addresses
I am trying to select dropdown list from webpage dropdown button
Below is the html code I am looking at:
In the above code, i want to get the dropdown value into excel sheet
below is the code i m trying to use but not working.
Set ElementCol = objIE.Document.getElementsByTagName("Select")
ElementCol.getElementsByTagName("option")(1)
Is it possible to restrict the values of cells in a particular column to entries defined in a list BUT to allow each cell in that column to display multiple items from the list (seperated by a comma for example). I've attached an example of what I would like to do -
In Sheet "2010 Data" I want to be able to select multiple values in column F....(the values are defined within the list named "Platforms" on the worksheet called "Lookups"
I am attaching a workbook with the VBA code for sending email.
I found the code on this forum and changed a little bit to make it work for my application.
I am not sure if this is the most effective way, but it works.
The user would select from a list of names on sheet1 and click "send_mail" button. What I need done is to send sheet2 as an attachment.
The problem: The code is looking for address in cell "H7" on sheet2. I want to keep the address on sheet1, but send out sheet2.
If I copy the address from sheet1 to sheet2 in cell "H7", everything works fine. So I need soemone to help me change the code, so it will send out sheet2, but read address on sheet1.
My wife is a teacher and she has asked me if there was a way of randomly selecting a child's name from a pre-defind list and for that child not to be selected again during that session.
Some criteria:
Up to 35 children in the class (selected from a pre-defind list)A child can only by picked on once during the lessonA method needs to be in place for seelcting the child's name (either a button that can be pressed, or another option).
I'm trying to clean-up a huge bulk of data. What I want to do is automatically select all rows, where column K contains text value "Closedloss", and have all these rows cut/pasted to Worksheet "ClosedCases". The second is similar to the first, but I want to select all rows where column J contains a value of 60-80, and have all these rows cut/pasted to Worksheet "MidRange".
View 2 Replies View RelatedI am trying to select non consecutive row in a 700 row spreadsheet in order to change the conditional formatting of those to grey. The change of colour is no problem but I cannot find a way of making the vba instruction to use the user selected rows.
An example would be if the user selected rows 10, 34, 56 and 89 to 105. how could I use this information as the basis for the next instruction.
I currently have a huge data sheet with multiple columns of information. It contains a list of projects organized by columns with information pertaining to each project. I've named this worksheet data.
On another sheet I've named Present, I'm trying to find a way to reference a single project at a time using a drop down list. The Present Sheet has a list of characteristics fields that need to be filled with information from "data" worksheet. I want the characteristics to change whenever I choose another project name from the drop down list.
I have a worksheet with a Drop Down list (Set up with Data Validation).
All I want to do is a call a procedure when a particular option is selected from the list, without have to run the macro manually.
I've made a drop down list and when I select multiple cells and choose from the drop down list, it only fills one. Here's pictures:
So in that first picture, I've selected multiple cells. But when I drop down the menu and click my selection, this happens:
Why is it that all the cells I've selected does not fill with the option that I chose from the drop down. I highly doubt that I have to manually click all the ones I want to fill with what I choose from the drop down list.
I am trying a little program to see if I could select full rows for copying. the code i use is as follows
View 5 Replies View RelatedI want to select a range of rows 11, 14 and 23 to 24 in column j, plus the 2 rows to the right of column J and run a sum formula on the range. My procedure is selecting rows 11/14/23/24 and doing the sum for only column J. How do I make it so it runs the sum formula on all 3 columns. My code is copied below. I am fairly new to VBA.
Code:
With ActiveSheet.Range(Cells(27, j), Cells(27, j)).Select
Set sumRng = Intersect(Range("15:21, 25:25"), Columns(j)).Offset(0, 2)
ActiveCell.Value = WorksheetFunction.Sum(sumRng)
End With
I have a macro that filters a column with varying criteria.
Row 1 has all my column headings.
What I would like to do is copy the 5 rows in view below Row 1 after the filter is applied.
I need to create a line of VBA code to have the macro select several rows to be cut and moved. The problem is, at this stage of the macro,the number of rows vary. Could be 1, could be twenty. I have established DD as the Dim for the number of rows, but I am not sure how to write the command so that 1 through DD are selected, cut, and moved. I may be having a senior moment, but I can't think of how to couch the term.
View 9 Replies View Relatedhow i can select the most recent 10 cells from a list of data to be included in a formula. I have selected it manually, for example F122:F131. This is fine but i don't know how i can put it so that it will update to the 10 most recent results when i input another line of data. So when i put in more data it stays on the previously selected. I want it to move shift down to include the new data but only the 10 most recent.
View 5 Replies View RelatedBasically on a summary page, on sheet 1 say I have a formula which picks up the unit cost (I have this as an array across 5 years)
What I then want to do is multiply this by a volume driver. I have a list of these drivers on a seperate page, so for convenience lets say DRIVER_01,DRIVER_02 etc. These are also 5 years arrays.
So what I want to do it be able to select which driver I want to use for volume on the summary, from a drop down box I have set up, and then the formula pick up that named range, and not just recognise it as a text cell.
I need the ability to change the driver in the future, hence the drop down box. I can also get the unit array part to work fine, I'm just struggling to get this driver array to work..............
I am trying to select rows in excel using a macro and then hide the rows, but i want to be able to add rows within this selection and still be able to hide the complete selection of rows.
The macro i am currently using is shown below: This hides rows 131 - 205, but if i was to add in another row in between so the range i now want to hide is 131-206 can i get the macro to update to this new range?
[Code] ......