Function: Selected String Contained Certain Phrases
May 13, 2008
I wrote this function to see if a user selected string contained certain phrases. It keeps returning united states so i am guessing its not cycling through the entire list.
Function region2(searchString As String)
Dim result As String
result = "None"
Dim lng() As String
lng = Array("arabic", "belg", "bul", "czech", "dan", "dut", "dutch", "euro", "finnish", "french", "ger", "greek", "greenland", "hebrew", "hung", "iceland", "international", "ital", "nor", "pol", "portu", "russ", "slov", "spanish", "swe", "swi", "turk", "UK", "united kingdom", "states")
I need to store a known index, unknown value in a variable so I can increase the value and use it in a database. It is used as a version number for a part and the versions go like: "00" -> "AA" -> "AB" -> .... -> "AZ" -> "BA"... etc.
VB: If tool.Worksheets("TRB Database").Cells(A, "R").Value <> ThisWorkbook.Worksheets("Design Calculator, Q").Cells(7, "C").Value Or tool.Worksheets("TRB Database").Cells(A, "AA").Value <> ThisWorkbook.Worksheets("Design Calculator, Q").Cells(5, "K").Value Or tool.Worksheets("TRB Database")
[Code] ....
That is a part of the increase and when I try to increase "AA" by 1 it goes to "B" and not "AB".
I am trying to identify the row(s) where a match occurs when there can be multiple occurrences of the match.
A B C
1 john brown Brown True
2 Cathy Smith
3 Brown excavating company
4 XYZ Corp
5 Brown Advisors Inc.
The first test I ran in cell C1 with the information above was to determine if "Brown" was located in the range of A1:A5 using formula: =IF(ISNUMBER(MATCH("*"&B1&"*",A1:A5,0))=TRUE,"True","False")
In the case of the word "Brown" it occurs in the range of A1:A5 three times, so the result of the above formula would be "True"
Now what I would like to do is return the locations, in this case the row number(s), where the word brown is contained range of A1:A5 because in the case of the word Brown, it occurs three separate times in the range of A1:A5. I would like the resulting value of the formula in this case to be "1, 3, 5" indicating the word Brown occurs in rows 1, 3, and 5. The formula also needs to work in case there is only one match as well.
I tried using the below array formula: ={MATCH(FALSE,ISERROR(SEARCH(B1,'Working List of Vendors'!$A$1:$A$5)),0)} But it would only return the first occurrence of the match which in this case would be row 1, or "1"
I am unsure if I would even need VBA to do this. I am trying to find a way to highlight all cells in column AO that have duplicate phrases and transfer just the phrase over to column CF. Ex: A cell containing "She didn't listen" and a cell containing "They didn't listen" would highlight and "didn't listen" would move over to Column CF. Would it be possible to do this without specifying the exact phrase?
I am doing a project where I have two lists, one list is much larger than the other but has more info. I need some of that info for my other list, so I have used vlookup, but it hasn't helped.
The formula that I am using is =VLOOKUP(B2,[Report.htm]report1183662451726!$A:$B,2,FALSE)
now the value I am searching for is "ANOS" which I know is in the search column, but the answer when I search is #NA. When I take out false the right value will fill in fro "ANOS", but if two words are similar such as "ALLN" and "ALEX" then it will give the same values for both which isn't right.
I've found a vb script via Google that displays the most common words in a list followed by a number.
Code: Sub HTH() Dim vArray As Variant Dim lLoop As Long Dim rCell As Range With CreateObject("Scripting.Dictionary") For Each rCell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
[Code] .......
At the moment this results in 1 word followed by a count.
How do I alter this so it shows 2 or 3 words, ultimately showing the most common phrases?
I have a listing of schools, some records selected with a "Y". When all of the desired schools have been selected, I want to run a macro that will scan through those cells and, for each one that is selected, put the school name into a string. I want that resultant string to be written to a cell.
2. Once the entire list is broken down into its many parts, use the pivot table feature of excel to determine how common each of the parts is within the entire data set.
So, my questions are these:
1. Do you believe this is the best way to solve my problem? If not, what would be the preferred method? 2. If this is the best method, what function or script would I use to accomplish the first step of breaking down the lines into their individual parts?
Mike Auto Merged Post Until 24 Hrs Passes;It appears I put too many characters in the title of my post. It should read: Common Words - Decomposing Text Phrases
I am trying to take some action based on the string values in selected cells. Here is my code to iterate through selected cells and count the occurances of certain string values:
Private Sub TakeAction() Dim cel As Range Dim sel As Range Dim complete As Integer 'a counter for completed Dim pending As Integer ' a counter for pending Dim cancelled As Integer ' a counter for cancelled Set sel = selection For Each cel In sel If cel.Text = "Some text" Then complete = Complete +1 If cel. Text = "Some other text" Then pending = pending +1 If cel.Test = "Yet some other text" cancelled = cancelled +1 End If End If End If Next cel
MsgBox complete MsgBox pending MsgBox cancelled End Sub
I would like to create a UDF similar to the SUMIF function but the UDF would be dynamic in that it would sum based on a dynamic range that would change based on the row the user is in.
background: I have a very large input template for 12-18 months for multiple cost categories. I would like to have the UDF in a specific cell above each category that would show the user what the sum of the range in the current row they are inputing data. Each row represents a specific project/task (along with 12-15 descriptive columns) that makes the freeze pane option unusable.
On sheet 1 I have a list of 1000 firstnames On sheet 2 I have a list of 1000 emails,
I need a function that states If a cell in the email column contains a string or value from the names column, it will result in a true statement so that I can separate out the emails that have these peoples first names.
I am looking for a macro or function (VBA) that will modifiy the value of the selected cell or cells. the code should support selection of one cell, a range or multiple ranges.
I envision the user making his range selection(s). Activating a function or clicking a button that would pop open a modal window. The user would have the option to either adjust the values by a % change (i.e. up or down 7%), or incremental change (i.e. up olr down 100 units). The function would overite the value in the cells.
Has anyone ever done something similar? Is it hard to program?
For the sample data below, columns A – C contain survey response data: 3, 6 and 9. I'm looking for advice on writing a function for column D that would look at columns A – C and return the # of the column containing the min value (e.g. A in this example).
I have a spreadsheet which shows a matrix in pounds. But I would like to have a reference cell somewhere near the top which will automatically show the conversion to kilos of any selected cell. Is anyone aware of a function which will do this?
For example, one pound equals 0.45359237 kilos. Cell A1 contains the reference function to the active of selected cell. If I click on B1 which contains 12, cell A1 would automatically show 5.45. If I click on cell C1 which contains 20, cell A1 would automatically show 9.09.
So I’m thinking it would be something like =SelectedCell/0.45359237 or maybe =ActiveCell/0.45359237
I am trying to code a macro that will search through a selected range of cells for key letters, for instance this cell may contain any combination of B, C, Te, Tc, RH, or LH. I would preferably like to search with capitalization being a factor but it is not a deal breaker. Below is a sample of what i have if the cell has a B, C it works for B but ignores the C i need it t o recognize both.
Code: If InStr(1, ActiveCell.Text, "B") Then Range("O" + CStr(ActiveCell.Row)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0
i have a question regarding the right function. can you use a string within the function. i know that the function is written =right(A3,2). is it possible to write the function as =right(a3:z3,2). i keep geting an error with this function
I have a Sum function in a cell on a spreadsheet and a bit of code which checks it using an If statement. If the Sum equals 1 (100%) it should do one thing, if not do another. However, if I put in the If statement that the Sum cell value should equal 1, it doesn't work. If I put it should equal "1", it does work. Why?
Example that works:
If Range("LocalBirthTypes").Value <> "1" Then MsgBox "To use your own data the split between the birth types must total 100%", vbExclamation, "Maternity service planning tool" Goto Exiting End If
Example that doesn't work:
If Range("LocalBirthTypes").Value <> 1 Then MsgBox "To use your own data the split between the birth types must total 100%", vbExclamation, "Maternity service planning tool" Goto Exiting End If
I want to search all the terms Column B within all the text phrases in Column A. I am ok if the words do not match the case sensitivity as I can address that myself.
If the exact phrase match is found, return the value True or False.
I am trying to make a function to take 3 Cells and using the initial Cell(say A1 with value of 100) as the inital value of a sum. Take Cell two(say A2 with value of 10) and add the value of A1 to Value of A2 in a constant increase. In otherwords A1+A2*A3= next increase. A3 is the value I want to change. But I want to add these values together as many times as the value of A3. so with current numbers will be 100+110+120+130+140 with A3 being a value of 5.
I have a problem when using the function of text(). I set "=TEXT(SUM(A1:A5), 0) + 5" in Cell A6. Assume the answer of SUM(A1:A5) is 10, the result in A6 will be 15.
My question is how to prohibit a string value [Text(SUM(A1:A5),0)] add a number value [5] to form the result 15.....
My formula =MID(A1,FIND("of",A1)+2,10) works perfect if it has a "customer of...". How to revise my formula that when it gets to cell B2 and if there is no "** Customer of ..." it would leave it blank or zero rather than #Value!?
The following concatenate function was working, then mysteriously stopped. When working it returned the data that I requested, now if I click on any concatenated cell and hit enter I get the function string, see below.
InputData is my tab that is being referenced. All other functions/formulas are working as designed. The data in the referenced cells is still there and I changed from text to number to general, etc but I still get the same result.
