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 need to conduct keyword searches in excel and color the keywords a different color without changing the font color of the entire cell. now, i found some code from another thread that does this, but i cant seem to get it to work with more than 2 keywords. i need fine-tuning the provided code to do what i need it to do.
the VBA code i used was this:
I was playing around with the example, the Cat_Mouse.xlsm, and when i modified the 'myList' and 'myColor' arrays, it does not work.
For example, I modified the code to add the word bat:
When i run the macros, the word "Bat" does not become colored red. interestingly enough, when i substitute the word "bat" for the word "hat" in the "myLIST array, the word "hat" does change to the red font.
I am looking to use this code to address keywords in my excel file by coloring all key words red and i have more than 10 key words.
Need macro to search approx 1000+ xls files in folder/directory for common text string "see reference" and then output the file number which is located in cell A1 to new spreadsheet for each file the text "see reference" is found.
Typical user OS will be Windows XP Pro / Win 2K Excel version : 97 / 2002 / 2003
1. Corporate network security settings will only allow directory/subdirectory creation in the “My Documents” section of customers individual computers.
2. Per customer request, VBA application needs to save extracted files for future use.
3. I can specify an initial “My Documents” subdirectory be made and the VBA application file be loaded/copied into that location – i.e. – “My DocumentsCat”.
4. When VBA application is opened from that specified directory, (first time), the application needs to make an additional subdirectory tree to save future files. I can read the opened from location via VBA with the following:
Dim filepath As String filepath = ThisWorkbook.Path As an example – this code would produce a string definition of “filepath” – such as the following:............................
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.
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.
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 over 200 worksheets within 1 workbook, is there a way to search or find a specific worksheet by its name? I've tried the find option, clicked search in workbook but it only searched cells within the different worksheets of the workbook and not the title (tabs) of the worksheets.
I've got an Excel document with over 323 entries and I need to search the data to find where ever a certain keyword is mentioned and copy the entire row into a separate sheet within the same workbook. However, I have a list of around 323 keywords and there is AND condition as well.so doing this using Ctrl+F would take some time.
The data is in Sheet "Training Dataset" and the particular column that needs to be searched is Column "A" . "Training Dataset" is the sheet where I would like the found results to be copied to in the column B and C. The list of Keywords that need to be searched for are located in Sheet "Keywords" , starting from Cell C and D.
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: ......
So basically I have an Excel sheet which has keywords that need to be entered in Google search. I need the URL of the first page of the search result that appears after that keyword is entered. IS there a macro for the same?
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've been tackling this data capture/paste issue for a week or so. I found the string below which does provide a good foundation for my challenge. But, my basic level of understanding macros limits my modifications to meet my needs.
I have 20 worksheets in my master file corresponding to Excel files individual associates will update weekly. After the associates have updated their individual files for the week, I want to capture the data entered and paste values into a master file containing a worksheet for each associate (sharing the same name as the individual associate file). All of these files are housed on team SharePoint sites.
I need a macro to perform several steps after clicking a "Run Update" macro button in the master file: Open individual associate fileIn master file, search for each Initiative listed in column B (starting cell B3) in the individual associate file (in column B starting at cell B11)If Initiative is found in individual associate file, copy adjacent data in columns D:J for the respective rowIn master file, paste values to the corresponding Initiative row for the corresponding week's worth of dataIf Initiative is not found in the individual associate file, move to the next Initiative listed in the master fileRepeat these steps for each individual associate file
Linking would be the easiest way to accomplish this if I wanted to have a multitude of weekly individual files for the associates. However, I'd rather each associate have one file for them to update (basically overwriting their previous week's entries).
I need to ensure the paste values corresponds to the appropriate day of the week. In simpler terms, if the date in the individual associate file in cell D9 reads Oct 1, 2012, the data captured from that row needs to be pasted to the corresponding row/column in the master file that reads the same date.
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.
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 _.........................
1st sheet "sample", first, the marco look for column c "F No." if it search the content start with "F" and follow by 8 digit numberic,
2nd sheet "pop up", then pop up userform to ask user to key in the textbox there. if it search for as many as "F" with 8 digit numberic, it will show at the userform and ask user to key in the data in the textbox there..
3rd sheet "key in", user can key in any data they want
4th sheet "output", after the user click the "OK" button all the data put in the textbox there can automatic go to the column "a" can refer by the sheet output..
VB: Sub openfiles()Dim Path As String Dim ExcelFile As String ' Path = GetFolder("C:UsersKinteshDesktop") Path = "C:UsersKinteshDesktopVBA programmingMaps" ExcelFile = Dir(Path & "*.xls")
NextCode: GetFolder = sitem Set fldr = Nothing End Function
My problem is that the code all actually works (including the function and when I use the commented part), but pointing to this one specific directory (the one I'm using right now), literally nothing happens.
I have managed to get my workbook to save with a filename based on two textboxes. I am not sure, however, how to define the directory. I have tried adding a ("mydirectory") on the saveas line but it does not work.
This is my Private Sub CommandButton2_Click() Dim wb As Workbook Set wb = ActiveWorkbook Dim strText As String strText = txtLMSRef.Text & "," & txtPartial.Text wb.SaveAs Filename:=strText wb.Close Set wb = Nothing
Using Dropbox, a lot of people will fill in formulas in personal workbooks from using data from another, central workbook. Since I cannot control where they install Dropbox or where they keep their personal workbooks, I must account for that in functions.
Now, my idea is to make them define the directory of the source in their personal workbook in a specific cell and then using that as a basis for the rest of the functions.
Cell A1: C:Usersuser1Dropboxfolder (copy paste from path in Windows Explorer)
Cell B1: A1&[CentralWorkbook.xlsx]SheetName'!$H$1
Basically I want the unknowing user to copy-paste the path of the central workbook into a specific cell in their own workbook and then build my functions bsed on that. How can this be done?