I would like to write a macro which checks if phrase from column B (lets say 13845) can be found in column A cells.
For example DDH113845G389.
I think that it should be some kind of loop but I'm afraid I can't handle it.
I try to search for only one word/phrase in range of cells. I would like formula to return the cell value that contains work/phrase I asked to look for.
For example: A1 contains "Apple", C1:3 contain "Cherry juice", "Apple pie", "Orange bonbon". I want D1 to check which cell from range C1:3 contains word/phrase from A1 and show it to me - "Apple pie".
I would like to search for a keyword or a phrase in a website using excel.
Generally we copy the keyword or a phrase and paste it in google and go for search. but i wanted to do this using excel as i have to search same pharse in multiple websites.
I am trying to search for a phrase in a particular column but the excel search function is returning !Value as an error...
Both fields are text so nit sure what is wrong here.
I have a list of cities that i want to cross reference across a cloumn of notes on another sheet. So, I really want to search for the city (Sheet1 Cell A1) in Notes Sheet2 Cell A1) and I want to return it in Sheet1 Cell B1 Whether it exisits in the notes. I then want to copy this down my list of cities in Sheet1 Column a.
I am very comfortable with SUMIF, but need to search a range any any cell containing words or phrase "20%" then need to sum those corresponding cells only.
I.e., something like: SUMIF(A1:A10,..."20%",C1:C10) .... only sum all C cells where an A cell contains any combination of words with "20%" in them.
I need a macro that searches my spreadsheet for a keyword in Column B. If it finds the keywords (or an array of keywords would be even better), it then deletes the entire row above the keyword, the row with the keyword, and the row below the keyword.
So for example,
(Column B)
1 - Row Above
2 - Keyword Row
3 - Row Below
If the macro finds the text in column 2, it would delete 1, 2 and 3.
I need to have a second macro that performs a very similar function.
it copies the 3 rows (similar to the example below) to a different sheet based on the keyword.
I have a large keyword list in a sheet called "AllKWs" In ColA from A3 downwards).
What I'd like to be able to do is this, which I'm sure will be complicated, but I will explain.
Say keyword phrase sheet has 25,000 rows of data (could be more/could be less).
I click an assigned macro button.
A pop up box appears.
I type in a word or words I'd like some info on, so for example I type in a word or words like "car rent"
It then returns for me on a new sheet called "Multi Keywords" a lot of data on this sheet, which would hopefully be as follows:
All Row 1 will contain Column headings
All row 2 will contain Total Counts (I'll explain in a minute this row)
So, all data to be returned from Row 3 downwards.
OK, as to the data to be returned.
All returned data In all Columns to show data in descending order by No of occurrances/appearances
Col A (From A 3 downwards) = The actual number of 2 word appearances (In this example that contain the words "car rent"
In Col B = All 2 word Phrases Containing ("In this example "Car Rent")
(As a note, In this example, ColA (CellA3) could only show the number"1" and ColB (B3)could only show the phrase "Car Rent") once. (As there isn't no other possible combination).
In Col C =The actual number of 3 word appearances listed in descending order That
contain the word "Car rent"
In Col D =All 3 word Phrases Containing "Car Rent"
In Col E =The actual number of 4 word appearances containing "Car Rent" listed in descending order
In Col F =All 4 word Phrases Containing "Car Rent"
In Col G =The actual number of 5 word appearances listed in descending order
Actually if anyone can crack this I really do take my hat of to them.
OK,A few more points,
Cells B2,D2,F2,H2,J2,L2,N2,P2,R2, All contain the word "Total:" and if the macro can fill in the number as appropriate.
So for example Cell L2 (For 7 word phrases) would say something like "Total:42" (If in Col L From L3 downwards the macro found 42 7 word phrases that contained the words "Car rent"
Ok, Cells A2,C2,E2,G2,I2,K2,M2,O2,Q2 All these cells will contain the word "Total". So these cells would list the combined total number of occurrances of all the phrases.
So for example cell K2 might say "Occur:324" as the total number of occurances of 7 word phrases that had the words "Car rent"in.
OK. as an example, I will post a code that Jindon wrote for me sometime ago. I'm posting this now, as it is very similar in what I would like this macro to be able to do, and might help as I'm sure this 1 will be complicated. This 1 looks for a phrase, returns by No of occurrances etc, but for all the combinations (Word lengths) within the Keyword phrase list, rather than what I'm asking for now, which splits them into Number of words columns.
Here it is anyway:
Sub NicheKeywordFinder()
Dim a, dic As Object, X, myTxt As String, b(), c(), n As Long, i As Long, e, s, myTotal As Long
myTxt = InputBox("HuaHinCarRental - Niche Keyword Finder") 'change to suit
If Len(myTxt) = 0 Then Exit Sub
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
ReDim b(1 To Rows.Count, 1 To 1): ReDim c(1 To Rows.Count, 1 To 3)
With Sheets("All KWs") 'change to suit
a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value
End With
Im am trying to create a search marco button that allows me to search in multiple worksheets in one work book. I came across this CODE the first part of it works. It pops open user input box and ask for the word that i would like to search but the this error message pops up Runtime error1004 Method 'range" of object'_Global'failed and i dont know what to do
Private Sub SearchButton_Click()
SearchString = InputBox("Enter Search String", "Search")
If SearchString = "" Then Exit Sub
For Each c In Range(myRange)
If InStr(LCase(CStr(c)), LCase(SearchString)) Then
[Code]....
I have a spreadsheet with about 20,000 lines. (The number of rows fluctuate) I need a macro to remove (delete) rows that contains the phrase "Off Peak" and "Weekend"
I was setting it up in a loop but I don't know how to make the loop end after ALL the "Off Peak" and "Weekend" info is removed.
My macro just does a find then delete the row and then loop to the find again. Is there an easier way and can the "Off Peak" and "Weekend" statements be combined into one search
Do While
Cells.Find(What:=" Off Peak", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Selection.EntireRow.Delete
Loop
I'm doing a macro to get the last word in a phrase
View 7 Replies View RelatedI have a phrase in a listbox formated as such: "Company Name / Company ID # / City, State"
I need to be able to seperate the values out into seperate categories:
Company Name
Company ID #
City
State
Is there any way to do this within the code?
I have three columns.
Column 1: A list of items with a very long text description in each cell
Column 2: The Key phrase found within the long description in the cells of Column 1.
(The key phrase is different for each cell).
Column 3: Custom shortened description in text format.
Column 4: Formula to return the Custom shortened description, equivalent to the item in Column 1
How do I create a formula in Column 4 so that Excel searches for the Key phrase found within the string of Column 1 and returns the custom description in Column 4?
i have a list of information, and from that i want to extract a certain piece from that information and out it in a new cell...
eg. 'KLM00506', 'KLM00409-10', 'KLM00821-9' etc
as you can see, the info is not in the same length
what i need is to take out the FIRST 6 characters and have the remaining to be in a new cell
anyone has an excel formula that i can use ? not really interested in code, cause in this particular worksheet, everything is at its' place, no code required
In the attachment, on the totals sheet I am doing a count of the results on Sheet2. Under "Alcohol as it Applies to Me" on Totals I am trying to count the 5 different categories, but the original question is a pick all that apply so at times there are multiple answers. I can't figure out the formula to count each phrase when there is multiple answers.
HRA Results.xlsx
I have a cell with a long list of phrases all separated by commas.
So it would appear, chicken, beef, steak, soup, fish
I want to create a rule in a cell to identify if one of those phrases comes up - so if chicken comes up then a 1 would appear.
To make it interesting is it possible to grab two words from the cell, so grab the words chicken and beef from a mass of words in one cell and bring it across.
I have these cells which contain huge amount of text and preferences and I am looking to identify these entities on if they contain these words.
Let's say this is my data. It's already been sorted so that like entries are all grouped together.
Ex1
Ex1
Ex1
Ex2
Ex2
I need to make a macro that numbers my data like below.
1
Ex1
2
Ex1
3
Ex1
1
Ex2
2
Ex2
Essentially, my data is many columns and sorted on a bunch of different levels. I need a quick way to assign a rank to every row in the same group, to see at a glance which performed the best.
I am trying to write a code for VBA code for find and replace, I want to find a particular phrase (i.e. 1. Value Added Processing) which is all in one cell and replace it a range of cells of other cells which is contained on a different sheet.
So basically the original 1 cell would be replaced for anywhere between 1 to 20cells. Depending upon what I type in.
Worksheets(1).Range("B2:B50").Replace What:="1.ValueAddedProcessing", Replacement:= _
"Sheet1!A1:A11", LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Text configuration - how can I tell a cell to pick up a phrase from one cell, but if that phrase is equal to one particular phrase, then produce a blank.
View 3 Replies View RelatedHow I can count the number of times each unique phrase in row "A" is repeated?
For example if my data set was
Blue
Green
Black
Green
Red
Red
Red
Red Hat
how can I get excel to count the number of times and return data like
Blue 1
Green 2
Black 1
Green 2
Red 3
Red 3
Red 3
Red Hat 1
Each cell in Range("A1:A2000") contains a remark, each phrase or remark is
Between 5 & 70 characters all written without line feeds (carriage return,i mean Alt Enter) Just spaces between words. What I would like to do in every cell is to force a line feed (Alt Enter) every 10 characters, and if the 10th character happens to be in the middle of a word I want the line feed to be inserted at the end of this word. Note that the phrases are not necessarily multiples of 10.
I am trying to come up with a macro that will find rows that contain the words �as % of Revenue� and then calculate and apply the appropriate formula to the actual and budget columns. I have attached a sample of what the source data look like.
The formula divides the category�s total expense by the revenue in row 4. For a complete macro, I would want to also subtract the budget result from the actual result and put its result in the �better (worse)� column. I need to use this on 12 or so different workbooks which is why I�m wanting a macro.
Here is my attempt to accomplish this task; however, I quickly realized it is woefully inadequate because I neither understand how to apply it to the different columns or how to find the next instance of �as % of revenue�).
VB:
Sub Macro1()
'
' Macro1 Macro
'
Range("B10").Select
Cells.Find(What:="as % of Revenue").Activate
Range("B20").Select
ActiveCell.FormulaR1C1 = "=R[-1]C/R10C"
Range("B21").Select
End Sub
i have a spreadsheet where I need to count up how many times a particular phrase within a text string appears. The text string will be duplicated many times throughout the spreadsheet.
For example :
Miss X was at work on Saturday
Mr XX was at work on Saturday but not Tuesday
Miss Y was at work on Tuesday
So I would like to count up how many times "work on Saturday" appears in my spreadsheet, and then as a seperate query, how many times "work on Tuesday" appears.
what's the fastest way to add a word or phrase in front of the data already in a column of cells?
Example:
cell contains product description and I need it to show
company name product description
I have the table, as shown on left in the appended image, and the final result should be the right one.
The steps are written as 1, 2, 3 and 4.
Untitled.png
I have a macro that I found somewhere on the net to look within a folder and list all the files of a certain file extension.
The macro to do this is in the attached example and is called 'Get_File_Names_Within_Dir_ext'.
I have created a basic userform outline, 'UF1' for the user to define:
Select File Extension
Select Folder to Search
Destination Sheet
I just don't have any idea how to sync the two.
If you type 'exe' into 'TB1_File_Extension' of 'UF1' the macro should search for '*.exe' files within the specified folder.
The search folder 'RefEdit1' box should open a windows explorer box (or some such) so that the user can select the directory in which to search for the previously specified file extension.
'TB2_Destination_Sheet' is a text box for the user to type the sheet within the workbook in which to list the files found within the specified directory.
'CB1_Find_Files' should activate the macro to find any files for the specified criteria.
There is also a Button 'Find File Types' in Sheet1 of the file which should activate the userform 'UF1'.
to write a Macro which will be able to read a numeric value in a cell and find the equivalent of the row number in the sheet.
For instance, if I type "12" in cell A1, the marco will be able read cell A1, goto and highlight Row 12 of the same work sheet.
I have taken over this spreadsheet for my work, and it is basically a statement in excel. What I want to do is find a list of invoice numbers in column B populated from a remittance, and then replace column F to say a specific thing depending on check number and date paying for that invoice. So if a check printed today I would have it replace column f to say paid 1/31/13 check # xxxxx. Currently I am searching for each invoice indivudually and then replacing with check number and date. There are about 200 invoices per month that I deal with, and it is a big waste of time!
For example from this
invoice #1 / reconciled (DATE)
invoice #2 / reconciled (DATE)
To this:
invoice #1 / paid (DATE) check # (xxxxx)
invoice #2 / paid (DATE) check # (xxxxx)
I am not very well versed in macro writing (I specialize in hardware). I am trying to help one of my users create a search macro that will parse an open workbook for a string value and insert the row(s) associated with that value into a new sheet.
View 9 Replies View RelatedI'm trying to simplify a macro so that I don't have to manually find the column to change each time. Basically, I want the macro to search for a column that contains [alt enter], and then add that column name to the existing macro below.
I tried various "find" or "match" comands in VBA, but am unsure how to execute the command so that the result of the find/match affects the highlighted line below.
VB:
Sub Seperatefromlinebreaks()
'working for active sheet
'copy to the end of sheets collection
ActiveSheet.Copy after:=Sheets(Sheets.Count)
Dim tmpArr As Variant
Dim Cell As Range
[Code]....
I've had a look at various coding to create a macro to search excel.
The issue I have is several work sheets within the work book but they're always inactive unless opened. From the 1st work sheet I've got menu buttons that open a specific worksheet, then there's a button to click to take user back to 1st worksheet and close the 1 they were looking at and making it inactive.
Following code is what I use to open and close sheets:
[Code] .........
The above works fine for opening and closing.
But if I use Excel's standard CTRL + F search facility and change options to search workbook, it doesn't work because every other sheet other than 1st sheet is inactive.