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")
Dim Country() As String..........
View 9 Replies
ADVERTISEMENT
Jun 4, 2013
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".
VB: tool.Worksheets("TRB Database").Cells(row1, "D").Value = Chr(Asc(tool.Worksheets("TRB Database").Cells(A, "D").Value) + 1)
This is where I increase the value.
View 2 Replies
View Related
Oct 30, 2013
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"
View 8 Replies
View Related
Mar 13, 2014
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?
View 3 Replies
View Related
Jul 5, 2007
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.
View 9 Replies
View Related
Jul 25, 2012
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?
View 1 Replies
View Related
Jun 12, 2014
I have a spreadsheet with note in it. How can I do a search to see what words or phrases keep coming up in the note. with out doing a sort.
View 4 Replies
View Related
Jun 5, 2009
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.
Here's an example of the data:
View 2 Replies
View Related
Jun 25, 2008
I am attempting to take a very large list of keywords, and find the most common words and phrases within them. For example, if I had a list that said:
excel formulas
excel spreadsheet formulas
excel help
excel formulas help form
formulas for excel
I would like to come away knowing that "excel" and "formulas" are common words within the list.
Currently, I believe this can be accomplished by doing the following:
1. Break down each line into all of its possible combinations.This would mean that the line with "excel spreadsheet formulas" would return:
excel spreadsheet formulas
excel spreadsheet
spreadsheet formulas
excel
spreadsheet
formulas
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
View 3 Replies
View Related
Mar 22, 2007
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
View 2 Replies
View Related
May 15, 2009
Looking for VBA that can change the font size of "•" char(0149) within any string in selected cells.
Also looking to delete the last "•" char(0149) within any string of selected cells.
What would the syntax for these two functions be?
View 9 Replies
View Related
Oct 10, 2009
-Problem on all workbook tabs
-Problem persists when port of worksheet data is copied to new workbook.
-New workbook with newly typed data will allow sort.
-Can't get my existing workbook sort to work.
View 3 Replies
View Related
Apr 22, 2006
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.
View 5 Replies
View Related
Oct 13, 2009
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.
View 5 Replies
View Related
Apr 28, 2009
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?
View 14 Replies
View Related
Jun 22, 2006
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).
A B C D
3 6 9 A
View 9 Replies
View Related
Oct 22, 2008
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
View 2 Replies
View Related
Jan 6, 2014
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
[Code] ........
View 9 Replies
View Related
Jan 5, 2009
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
View 4 Replies
View Related
Jan 26, 2007
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
View 8 Replies
View Related
Feb 4, 2012
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.
Column A
A1:Dog likes to run
A2: Cat Red-tummy-01
[Code]....
View 9 Replies
View Related
Feb 26, 2014
I'm trying to create a formula to return the number of cells where the last 8 characters of a cell end with the string "landline"
My (failed) attempt at creating this is as follows:
=COUNTIF RIGHT(A2:A322,8)="Landline"
View 8 Replies
View Related
Oct 19, 2009
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.
View 7 Replies
View Related
Mar 7, 2014
I would like to express an empty string with the expression .
[Code] .....
=if(D6='';0;1)"
[Code] ....
but I have an error message.
I don't know what is the error?
View 10 Replies
View Related
Jun 10, 2014
I try to merge two sting (one from function). the output of the following sub should be "ttgood". but instead, i got "good" only.
Code:
Function cs()
Dim sv As String
sv = "tt"
End Function
Sub ts()
Dim s As String
s = sv & "good"
MsgBox s
End Sub
View 2 Replies
View Related
May 3, 2007
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.....
View 11 Replies
View Related
Dec 18, 2008
I know I have to use FIND and MID but I can't work out the right syntax: how do I extract Michael or Dave from the following strings:
F - Michael 8735
M - Dave 093
View 3 Replies
View Related
Feb 21, 2009
Cell A1 contains 012345678 ** Customer of 987654321 **
Cell B2 contains 087654321
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!?
View 3 Replies
View Related
Feb 8, 2007
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.
='Input DATA'!C2&" "&'Input DATA'!B2&" "&'Input DATA'!F2
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.
View 9 Replies
View Related
Apr 11, 2007
I do not know if this is possible in VBA, but I am hoping it is as it will make my life much easier.
Is it possible to say have a TextBox and a CommandButton, that does the following?
Public Sub Execute()
Dim ObjectName As String
Dim Object As Object
ObjectName = txtObject.Text
Set Object = ObjectName
Object.Execute
End Sub
Or to take it a step further:
Public Sub Execute()
Dim ObjectName As String
Dim FunctionName As String
Dim Object As Object
ObjectName = txtObject.Text
FunctionName = txtFunction.Text
Set Object = ObjectName
Object.FunctionName
End Sub
Are there any inbuilt functions that will create/reference an object based on a string containing the name of the object or function?
View 9 Replies
View Related
