I would like to have a macro to loop through all the worksheet except for "Summary" worksheet.
The macro will look for the keyword in the worksheet starting from row 2 from every sheet.
if the keyword is found on the row, the entire row will be deleted and shift row up.
Keyword for example is found in H13 in the Summary worksheet
Summary
GH13KeywordLondon
Excel tables to the web >> Excel Jeanie HTML 4
I'm looking to identify a keyword in a string and then replace that string with just the keyword.
The string is a product description. The keyword is a product group. The keyword can showup in any position in the string.
I can't post the actual data do to confidentiality requirements. but here's an example.
description
qty
price
keywords
code
green grapes bunch
1
2.5
[Code] .......
So I have data that I'm importing in rows 1,2 and 3. The first row has the product type, then rows 2 and 3 have additional info pertaining to that product. I was wondering how I would write a macro that could identify entries in the first row that equal a particular product, and remove those cells, along with its respective info in rows 2 and 3. Ideally, I could just push a button, and a particular product along with all it's info would disappear from my imported table.
View 4 Replies View RelatedI run a match formula which returns the row numbers of items i need to delete - at the moment, when a match is found, it will say "Match found, delete row 4" for example. Since multiple matches, potentially into the hundreds could be found - is there a way that i could run a script to delete the rows automatically when a match is found? So instead of putting the above "match found, please delete row 4" into an adjacent cell, could we just delete row 4? & then move onto the next row where a match is found?
View 9 Replies View RelatedI have a file with over 20,000 rows that contain a date (Mon~Sun), What I need to do is remove every day that contains Mon~Sat and only keep Sun, this is the code that I have come up with and is working.
Code:
Dim c As Range
Dim SrchRng
Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A" & Lastrow).End(xlUp))3
[Code]....
I'm quite newbie for VBA for excel, my friend asked me a favor to filter the data on his excel, so when he enter some keyword, the table would sorted (only show the row that have that keyword), and if possible that he enter it few keyword.
keyword example VB, oracle
table example ....
I'm trying to extract the number of times specific keywords are used in Sheet1 and display that number on Sheet2.
For example:
Keywords to be used
ni = not interested
appt = appointment
na = not available
If ni is used 10 times, appt is uesd 2 times and na used 6 times.
I want to be able to see those numbers on Sheet2 next to the keywords.
e.g;
ni = 10
appt = 2
na = 6
Code / macro that would automatically search a word doc for keywords and export every instance of that keyword into column A of a spreadsheet, the first three or four words prior to that keywords into column B, and the entire sentence where the keywords is found into column C?
I often pull hundreds of pages of text from a program that exports into Word and have to look through the pages to find every instance where a point is referenced by geolocation. Then I have to filter through that data to determine which of the points I found are truly important before I save it all to a csv or xls and feed it into mapping software.
If I had a code / macro that did the search and export automatically, it would save hours of work.
I have a work sheet called 'Cross Ref data' which I want to put a keyword in column A (row 1,2,3,4,5,6,7,8 etc)
I then want to be able to run a macro that will grab the contents of A1 in WS called 'Cross Ref data' and check if it appears in column D and E in WS 'Results'
If there is a match then for the entire line within WS 'Results' be copied to a WS that already exists called "Cross Ref Results" (please see the below heading '88888***********8888' for the layout) .........
I am similarly interested in using the logic in this thread http://www.excelforum.com/excel-prog...uplicates.html
Basically I want to be able to type in a keyword and then bring up a number of matching results along with their associated code.
Using the spreadsheet in the above thread, I tried to add an extra entry as a test, as my database contains roughly ~1500 entries, but excel doesn't seem to like me adding 1 more entry and adjusting the dimensions of the code?
I have a large keyword list
lets say it's 25,676 keyword phrases all in ColA (Always from A3 downwards).
I delete rows as I'm sorting so the number does very.
The sheet tab is always called "All KWs" (For All Keywords Not surprisingly)
What I would like is a macro that does the following.
Can it look at all the keyword phrases on sheet "All KWs" in ColA (From A3 downwards and make a list on a new sheet of just unique keywords from the list.
This sheet will be called "Unique keywords"
So, can it return on this sheet called "Unique keywords" all the unique keywords from the large list in ColA (From A3 downwards) on the sheet "All KWs".
If it can return only unique words and numbers etc, removing all duplicates.
If it can do this From A3 downwards.....
I have 4 macros which inserts a row in workbook (at specific interval of time). These macros run in a endless loop. The problem arises if any other workbook is open and active at the same time. The macro tries to update this active workbook.
Can I ensure that macro runs for only specific workbook (whether the workbook is active or not)
Option Explicit
Public RunWhen As Double
Public RunWhat As String
Dim wCount
Sub Macro3()
'Initialize counter
wCount = 100
Call Macro4
End Sub
Sub Macro4()
If wCount > 0 Then
' If counter greater than zero then update excel with counter and
' call macro to decrease the counter
Cells(1, 2).Value = wCount
RunWhat = "Macro5"...................
I probably just am just missing a Library but when I type "Selection." I would normally expect Autocomplete to pop up telling me that I am able to choose from Cut, Paste, ect. However its it almost as of my VBA editor does not recognize Selection as a proper object.
I have the following Reference Library's loaded:
Visual Basic For Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft Outlook 11.0 Object Library
I am looking to write a macro that will search for keywords on a sheet, on a workbook at a specific path, and would like some help getting this macro started...
It would get run from "WorkbookA", and would look at "WorkbookB" (wherever it is) and find the cell containing the keyword, where the cell value would be the output. I would really prefer that it did not open Workbook B while this macro is run, if thats possible.
I am looking to automatically assign products to a relevant categories on my website and I am working on a spreadsheet. Each category is has a four digit number assigned to it and categories are limited to 3 (Primary, Secondary & Tipperary)
For example:
Computers - 1000 - Desktops - 1100 - Dual Core PC - 1101
Computers - 1000 - Desktops - 1100 - Quad Core PC - 1102
So as you can see you can tell a Dual Core PC is in the Computer & Desktop categories just by looking at the first two digits. The last two digits are the category number. 01, 02, 03 etc.
What I wish to do is associate keywords to the category determine which category that product should be assigned.
Product ID
Product Title
Product Description
Product Spec1
Product Spec2
1
HP Desktop PC
HP Desktop PC's are computers
Intel Dual Core Processor
500GB HDD
2
HP Desktop PC
HP Desktop PC's are computers
Intel Quad Core Processor
1TB HDD
Keywords assigned to category 1101 are Computers, Computer, PC, Desktop, Dual Core.
Keywords assigned to category 1102 are Computers, Computer, PC, Desktop, Quad Core.
And the spreadsheet looks like this:
The script/sum would look at all the cells and match the highest count of keywords to that particular category and populate additional fields called Cat1, Cat2 & Cat3. Therefore the final spreadsheet would look like this:
Product ID
Product Title
Product Description
Product Spec1
Product Spec2
Cat1
Cat2
Cat3
1
HP Desktop PC
HP Desktop PC's are computers
Intel Dual Core Processor
500GB HDD
1000
1100
1101
2
HP Desktop PC
HP Desktop PC's are computers
Intel Quad Core Processor
1TB HDD
1000
1100
1102
How this can be implemented? possibility with the use of VLOOKUP or MATCH commands?
I believe a vlookup would be fine but there's was only 1 value to match but as it's multiples it would need to prioritise based on the number of keywords matched and not just the first match found.
[URL] .......
Attached is a spreadsheet describing my problem in greater detail, but essentially i want to extract rows of data depending on quoter name. I'm fairly competent with excel formulae, but cannot get the desired results. I know nothing of VBA other than copying and pasting other's VBA projects in and fiddling with them until something good happens!!
I wish to extract the data and deposit into another worksheet or better still another workbook entirely but without any breaks in the rows of data.
Havent had much luck with advanced filter either.
Am I right to say that "Call" is not mandatory and should be used only to invoke subroutines that do not return a value?
If you use the keyword "Call" to invoke a subroutine that does return something, the returned variable will be discarded.
Currently I am using excel 2007. In that I have two sheets (sheet1 as Dashboard & Sheet2 as Database). On sheet1 I need to develop a functionality of searching as per key word (Keyword will be typed on cell B2) and i need to display search data below cell B2 till whatever cells depending as per database.
View 3 Replies View RelatedWhat I'm trying to achieve is the following;
I have a big keyword list saved in Excel.
Something like the following,
These are all keyword phrases;
car rent
car hire
cars for rent uk
etc etc etc
All listed in Column A
All phrases in Column A, and in separate rows.(1 phrase per row.)about 2000 lines (Rows) in total.
What I want to know is there any way of selecting the whole list and exporting it (To save it as another list. A list of just unique keywords?
So, It would create a list like;
car
rent
uk
hire
for
etc etc etc.
So basically I want excell to look at all the words and export them to another list showing just unique keywords, 1 per line.
This is so I can see from a huge list what all the unique keywords are.
Is there a way of doing this within Excel Now?? or has someone made a plugin ( Macro) to achieve this?
I've looked at the sort & filter options, but it doesn't appear to have this function?
i'm writing a few VB subs to help manage a database of mishaps, solutions and lessons learned, there are 11 columns and each databade entry has a separate row, there are approximately 1,000 rows.
Columns are:
ID- a unique iderntifier for each entry- integer
Date- date the entry was made- date
Project Number- a reference to an internal project file- string
Operator- company we were working for- string
Installation- where the work took place- string
Category- type of problem encountered- string
Application- field of problem encountered- string
Issue- string
Background- string
Problems- string
Lessons Learned- string...........
I have a sheet with 3 ranges of data in Sheet4, being partcode (B20:B20000), description (C20:C20000) and price (D20:D20000).
What I'd like is a macro to take a keyword entered in Sheet4!C17, search the whole range (B20:D20000), and hide the rows that don't contain the keyword.
Autofilter would be the logical method to my way of thinking, but can that work with a keyword entered in a cell, and can it all be hidden in a macro behind a control button?
I am trying to set up a keyword search where someone can type a word in D8 and the number of books with that word in its title will be returned.
I have managed to get the following sum to work if I put the exact title in the search however I would like it to be possible for a partial search.
=SUMPRODUCT(--('Books List'!G2:G1166=D8))
I've had a look in the forums and can't seem to find this covered. Here's what I'm trying to do, I'd like to have a cell where you'd put a word in and then click a button that would search for those files under that keyword, then maybe list the files as hyperlinks or as buttons that you can select from. It's for a recipe manager type, so if you'd put in pasta, it would search the recipes folder for any files with the name "pasta" then put them on the screen so you could click one of them to read it.
View 9 Replies View RelatedI have a macro that works fine in one workbook. I copied it to use in another one, but then I get this error message: "Invalid Use of Me Keyword".
Dim wSheet As Worksheet, strMsg As String
For Each wSheet In Worksheets
If wSheet.ProtectContents = True Then
strMsg = "All sheets protected."
Else
strMsg = "All sheets unprotected."
End If
Next wSheet
MsgBox strMsg
Unload Me
End Sub
I am using the following code to insert pagebreaks based on changes in column D. I would like to change it so that page breaks are inserted after the appearance of the word " Total" in column D. Note that the word total will typically be preceeded by other words. Example "Sales Total" or "G. Smith Total". If possible, I would like to suppress this logic when the words "Grand Total" appear as I would end up with a grand total page with nothing else on it.
Sub set_page_breaks()
Dim NumRows As Long
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
ActiveWindow.View = xlPageBreakPreview
ActiveWindow.Zoom = 85
NumRows = Range("A65536").End(xlUp).Row 'get the row count
ActiveSheet.ResetAllPageBreaks
With ActiveSheet
FirstRow = 2
LastRow = NumRows
For iRow = FirstRow To LastRow
'SET BREAK ON CHANGE IN COLUMN "D"
If ActiveSheet.Cells(iRow, "D").Value _.........................
I have a sheet with only one colomn, and about 200 rows, containing alot of 'garbage'.
Each few rows, I have a keyword ("REFDES=") and after this keyword I have a value ("R3254"). I need to extract only these values and to put them in one row, few colomns.
For example, if the sheet looks like this: ......
Query form using text box value. I have database and a search form and i need to display only the results based on queries (text box value).
VB:
Private Sub CommandButton1_Click() FindKeywords Me.txtNo.Value & Me.txtName.Value & Me.txtParts.Value
End Sub[COLOR=#333333][/COLOR]
MOdule
VB:
Public DSO As ObjectPublic DstRow As Long Public DstWks As Worksheet
Private Sub FindKeyword(ByVal Keyword As String, ByRef SrcWks As Worksheet)
Dim LastRow As Long
Dim Result As Range
Dim Rng As Range
Dim StartRow As Long
[Code] .....
I wanna extract anyt text in a call that starts with LQ1 to a length of 10 characters to an empty cell, coma delimited.
View 14 Replies View RelatedI have a list of drugs which possibly could increase to thousands in rows. I will need a macro that can scroll down to a particular row when the text in that row has been entered.
Assuming I have LOTEMP in A5000 , I would want a macro to scroll to row 5000 when the word , " LOTEMP" is typed in cell , say B2.
I am faced with a challenge of identifying all the probable duplicate issue of Id cards for a list of some 50000 students.
There is a list of student names in Column A with associated fields in subsequent columns.
I would like to use either first name or last name as keyword and pull all the data(all the columns) that either partially or fully match the keyword in second sheet.
I have attached a sample workbook for this : STUDENT MASTER.xlsx
I have tried vlookup but it pulls only one data set.