How To Compare Two String Variables
Mar 27, 2014
I am trying to to work out how to compare two string variables that I can see with my eyes are clearly a match but getting the right syntax to convince VBA they are the same is my issue.The first example is:
Direct Credit 158824 BRANDON DONNA C/ Lak30
the second example is:
Direct Credit 158824 BRANDON DONNA C/ 30 Lake Vue Parade
but that still would not get a match that I can find code for.
i am chasing the "holy grail" in VBA and I will leave the records that do not exactly match to manual processing although i hate to give up that easily
View 2 Replies
ADVERTISEMENT
Aug 23, 2007
I have a problems here. The problems is attached in the file. I wanna extract alpha/char from a string. Example: I wanna extract the words "(M)" with the bracket from the string "Toothbrush (M)" in column A. After extracting the (M) out, I wanna do a validation to compare the (M) in column B with another data in column C, if the (M) is same as the data called "Medium" in column c, the validation will return "Match" in the column d!
View 9 Replies
View Related
May 22, 2012
I have 2 array variables in a block of code which I need to compare to check if the items in the arrays are exactly the same. The arrays are exactly the same size and I can see the contents of the arrays in the watch window.
When I try to run and IF statement as in: IF VARIABLE1 = VARIABLE2 THEN - I get a compile error saying "type mismatch" on the equal sign.
View 3 Replies
View Related
Dec 27, 2006
I have tried lots of alternative for making Excel Dates & Times calculation work in vba. I can't find the place its creating problem. I have attached spreadsheet to have better look at it. When I am using time in one column (Calender!C) in VBA code, it works fine and update values in R1,R2... columns. But, when I am using time in another column (Calender!D), its not working properly and not updating appropriate columns. I will appreciate if someone can look at vba code in attached file. It looks like I am missing some kind of setting in excel/vba.
View 3 Replies
View Related
Aug 2, 2006
I have recently come across a VBA macro, which reads from a text box within an excel input sheet, copy the text content, and send it via email. However, when the text content started to go beyond a certain number of characters, the text that comes out of the email, got truncated. I presumed this is something to do with the limitation to the string variables where the string " messagebody$ " which is used to read the text can only take up a certain bytes.
I was looking for VBA help, and somehow, it mentioned something called a variable-length string. Would that be a solution to my problem, or how is that used ?
Sheets("main").Select
ActiveSheet.Shapes("Rectangle 1").Select
messagebody = Selection.Characters.Text
....
email.Body = messagebody
View 6 Replies
View Related
Mar 1, 2009
On to what brought me here; I have a spreadsheet with a column containing an address, eg '12 SMITH ST SOMESUBURB NSW 2001'. I'm looking at splitting this cell in half after the street and moving the second half (suburb state postcode) into the next cell, which i've managed to do with a series on InStr statements.
Now the fun part is that not all these addresses are streets, some are avenues (AV or AVE), roads (RD), place (PL), drive (DR or DRV) etc. Initially i just did this:
View 7 Replies
View Related
Apr 15, 2009
I made the formula work great, however I want to cut it down and simplify it and make it easier to manipulate... as opposed to having the filename written every so many times.. replace with a loop instead.... so:
Private Sub Weeklytestt_Click()
Dim varCurDate As String
If DateYesterday.Value = True Then Answer = "1"
If DateToday.Value = True Then Answer = "2"
If DateTyped.Value = True Then Answer = "3"
Select Case Answer
Case Is = "1"
varCurDate = Format(Date, "yyyymmdd")
varCurDate = varCurDate - 1
Case Is = "2"
varCurDate = Format(Date, "yyyymmdd")..........
View 9 Replies
View Related
Aug 30, 2006
I work with data that varies in row numbers but is consisten in column width. I am trying to write code that will create a named range for the data but be flexible to expand or contract based on the amount of data that is pulled in. Below is the
Const lngLastPossRow As Long = 65536
Dim strDataRng As String
strDataRng = ActiveSheet.Name & "!R4C1:R" & Range("a" & lngLastPossRow).End(xlUp).Row & "C17"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.Names.Add Name:=("Data"), RefersToR1C1:= _
strDataRng
While the result creates a named range called "Data" , it does not allow me to reference and data in any formulas ( sumif's, etc). Can someone tell me what I am missing. It appears to be returning the wrong data type (string as opposed to range values).
View 6 Replies
View Related
Apr 17, 2007
I am trying to automate a report writing spreadsheet in which the text never changes in the cell (up to and over 50 words long).
Is there any way in which the figures within that text could be linked to another cell and change accordingly.
For example: a particular cell might contain the following which never changes apart from the figures:
"the performance of the fund was (B5) followed by a change in the index of (B6)...........the performance of the irish stocks was (A19) etc etc."
I know something can be done in formatting but for very long text would this be impracticle?
View 5 Replies
View Related
Feb 9, 2009
I've a big problem getting the Constraints in excel solver to use variables.
It seems that the only accepted variable type in the contraints are STRING variables. THe following simple example works AS LONG AS the variable apa is a STRING value and as long as it doesn't contain decimals....
So the question is. How to use varialbes in the FormulaText argement other than string values as integers?
View 14 Replies
View Related
Aug 4, 2014
I want to compare a string in a different workbook. and find the relevant cell value of that.
e.x. in one worksheet the value displayed like "DEBIT/ATM CARD TRANSACTION FEE" But in another worksheet the value displayed as "018 - Debit/Atm Card Transaction Fee".
Now I want to compare this string and the value displayed in the column E for this row of workbook 2 should be displayed in workbook 1 in the column D.
View 6 Replies
View Related
Jan 24, 2014
I am working on a project where I need to remove records based on certain criteria.
The key pieces of information here are the Company ID, the Contact ID, and the Product Codes. Each Company ID can have multiple Contact IDs, and each Contact ID and have multiple Product Codes. (To be more clear, imagine one company with a Tax department and an Audit department - with each department having a separate contact person. And, each contact person purchasing multiple products.)
I have already summarized the data by identifying the unique Contact IDs and combining the corresponding Product Codes into a string.
Now, I need to determine if any Product Code within the summarized string matches a Product Code in a separate list (a Range of cells). If so, each instance of the Company ID needs to be moved into a different tab. Also, to understand the magnitude of my records, I am working with 25,000+ unique records in the first tab, 8,000+ summarized records in the second tab, and 50+ product codes in the separate range in the third tab.
My first thought was to use a For loop to determine if any cell in the Range is found in each string and, if so, add the related Company ID to a dictionary object. Then, run another For loop against the Company IDs in the summarized tab (8,000+ records) to determine if the ID exists in the dictionary and, if so, moving the row to another tab.
My second thought was using the original data (unique records) - I would load all the Product Codes in the separate Range into one dictionary and run that dictionary against the individual Product Codes in the unique records tab (25,000+ records). If the Product Code in the unique records is found in the dictionary, I would add the related Company ID to another dictionary. Then, I would run a For loop comparing the second dictionary to the Contacts IDs in the summarized tab and move the ones that exist.
In summary, the two ways I have come up with to accomplish what I need includes:
1) a For loop, a dictionary, and another For loop
2) two dictionaries and a For loop
View 6 Replies
View Related
Apr 18, 2013
Some data
In Cell A1 This is a bag
In Cell B1 This is a bag with books
now in Cell C1 i need the mismatch words With books
C1 should show with books
I am now manually finding the words of cell A1 in Cell B1 and then writing the mismatched words C1 .
View 3 Replies
View Related
May 28, 2009
I was trying to compare string from excel spread sheet to a value I am not getting any output. It is not working
View 4 Replies
View Related
Mar 28, 2014
What I am trying to do isn't very complicated. I started on some code, but it's just not good enough to do all of what needs to happen.
A
B
C
1
Cupcake Monster
Aisle 5
Shelf H
2
The Cupcake Ghoul
Aisle 2
Shelf P
3
Fred Baxter's Diary
Aisle 1
Shelf X
4
Angry Cupcake Beast
Aisle 3
Shelf A
5
Everyone Loves Cake
Aisle 4
Shelf R
So I have a list of titles that I want to search for a particular string. The title in this list is the "proper" title,whereas the rest of my workbook typically uses a shortened version of the title. Sometimes the two will match 100%, but usually, they will be close.
If the string in the table above has more than 2 words, I want to use the first 2 words to check for a match. If the string has 2 words (will never have fewer), I want to match the first word. I want to do this IF there is no 100% match (if possible or reasonable). I will settle for just matching 1 or 2 words. I want to copy the values to another sheet and then delete the row that I originally got the data from.
What I want to do is search the list for "Angry Cupcake" and return the Aisle and Shelf location of Angry Cupcake Beast. There will never be any other "Angry Cupcake", so I am not worried about mismatches there.
Code:
Dim SearchRow As Integer
Dim StoryTitle As String[code]....
how to do and can get to work without issues is an exact match. This still leaves out all of the shortened titles though.
View 3 Replies
View Related
Aug 25, 2008
I have a string compare function that compares two strings and return 1 if matches. Each string has multiple words separated by ",", and all these words need to be matched.
This function is done, but I want to add a little feature to it. For each string, if one word cannot be found in the second string, change that word's size to 14, fontstyle to bold.
Function StringCompare(value1 As String, value2 As String) As Integer
''this function compare the words from 2 strings
''each word is seperated by "," and the order of these words does not matter
''return 1 if matches, 0 if not match
Dim v As Variant, u As Variant
Dim i As Integer
Dim answer As Integer, answer1 As Integer, answer2 As Integer
answer1 = 1
answer2 = 1
View 9 Replies
View Related
Dec 19, 2013
Basically I have a string of dates, I want to get the max of the values that are before a specific date and return the header. Look at the file I provide for better understanding.
View 2 Replies
View Related
Jan 31, 2008
I need to compare a list of keywords (stored in a string variable) with the content of a cell in order to see if any of the keywords do appear in the cell. I have to work with many such lists which can grow over time. What I'm doing is using InStr(cell with content, keyword) for each keyword. My goal is to have a macro which can automatically create the long formula from any list. My actual problem is that the formula I've build and stored acts more like a string. I'd like it to say either true or false. Anyway, here's a sample of what I came up with so far.
Sub build_formula()
Dim i As Integer, j As Integer, m(20) As Integer, n As Integer, z As Integer
Dim List As String, f(2) As String, s(20) As String, sp As String, Source As String
Dim Formula As Variant
List = "black, blue, green, red, yellow, white, z"
Source = Cells(3, 1).Value
z = InStr(1, List, "z")
i = 1
n = 1
Do
i = InStr(i + 1, List, ",")
m(n) = i ..........................
View 5 Replies
View Related
Oct 22, 2012
I need to compare a string from say A1 to a range of cells in another workbook and if found return the value of a cell on the same row.
Something like - look up string value in A1 in other workbook.sheet1.A1:A65535 if matched return the value of matched row column b.
OR
IF Otherworkbook.sheet1.A20 = A1 return the value of B20
View 6 Replies
View Related
Jan 7, 2009
I have another problem with this damn address file. Column H and I have data in them that is often mixed. As shown below, I have used A and B below, but its normaly in Column H and I. I would be greatul if some could write a macro to split the data into the two columns.
Rows 2-8 is what Im presented with. I would like them to look like 11-17
Note that the number in row 8 does not match, so is left for manual intervention....
View 9 Replies
View Related
May 12, 2012
I have a dataset of shifts and want to compare each shift that needs to be filled to a list of requests for time off (vacation, etc).
For one cell the code is: =IF(ISERROR(SEARCH(B1, A1), 0,1)
Where A1 is the column of requests in comma delimited forme (ie: "AB,CD,EF").
Where B1 is the column containing the person assigned to shift 1 (ie: "AB")
In this case, would return a "1" as no error was returned, as AB was found in the list. Here "1" would represent a schedule conflict.
Without creating many cells for each shift- there are 20 shifts- can I create an array or string together this type of "SEARCH" function?
View 1 Replies
View Related
Feb 5, 2009
Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...
I have the
View 2 Replies
View Related
Mar 25, 2014
As per title, I am trying to compare a column of text cells which contain "Yes" or are empty and a columns of numbers. If they are "Yes" and "1" on the same row, I want to output an "OK" message. Excel seems happy with the following code but it does not work and returns an empty cell if the two conditions are true.
[Code] .....
View 6 Replies
View Related
Feb 21, 2014
Basically I have two sets of data. One will be new each week. I'd like to use the non-changing data as a base to compare new data to. The formula would need to match multiple values, including a 'time between', and then return whether a minimum rate has been met.
SampleRateExamine.xlsx‎
View 3 Replies
View Related
Jul 27, 2006
Can a Function give two or more output variables. e.g.
Sub a()
x = 5
result = Y(x)
End Sub
Function Y (x As Integer) As Integer
Dim B
B = ... * x
Y = ... * B
this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that?
I need this because function works with large matrix and I want to extract some values appeared in between.
View 2 Replies
View Related
Apr 27, 2006
I'm trying to loop through a range in excel from access, checking where the titles (in Excel row 1) match with the fields (in a recordset in Access that is passed to the function) - and where they do, I want to dimension a variable to hold the column number - I'm not sure it's possible, but I'd be interested to know either way. The line I'm asking about is at the bottom of the code - the rest of the code is just to give context...
Sub ImportGeneric(rsImported As ADODB.Recordset, rsConfirmed As ADODB.Recordset)
Dim fd As FileDialog
Dim xl As New Excel.Application
Dim wb As Excel.Workbook
Dim ws As Worksheet
Dim iFilePicked As Integer
Dim strFilePath As String
fd.Filters.clear
fd.Filters.Add "Excel files", "*.xls"
fd.ButtonName = "Select"
iFilePicked = fd.Show
If iFilePicked = -1 Then
strFilePath = fd.SelectedItems(1)
Else ..................
View 3 Replies
View Related
Mar 10, 2009
I have two ranges of numbers stored as text on two different sheets.
The first one is from product code and the second is from product category.
The problem is both ranges are of different lengths and I have to find out if a product from the right is part of a particular product category. Even if the length is different the first digits are indicative of the belonging of a code. For example 1234 and 12345 are “family”-their first 4 digits match.
Just to give you an example of what is desired:
Category____ Code
2200 ________22002
2323________ 232347
So, the loop should do the following:Compare the first string from the “Category” column to each and every entry on the right, if a match exists (we have no match here for 2200) write “ok” next to it.
Next trim one digit from the right from every string in the “Code” column.
Compare same first string from the “Category” column to each trimmed string from “Code” column (here we should have a match 2200=2200)
Write “ok” next to it
Now the loop goes to the second string from “Category” column and for this one we will have to trim 2 digits from the right of each string in “Code” column to achieve the result (2323=2323) and so on.
View 9 Replies
View Related
Sep 19, 2012
I have a udf that returns a string to the cell. The string is made up of multiple string "objects". What I am wondering is if I can set the font color of certain objects so that when the final string is built and returned, the font of those portions is set.
Ex. of simple idea (this is not actually my code, just a way to illustrate. I realize there is no point to this UDF):
VB:
Function StringReturn (Str1 As String, Str2 As String, Str3 As String) As String
StringReturn = Str1 & Str2 & Str3
End Function
Now what if I wanted Str1 and Str3 to be blue, and Str2 to be red for example. So that when the UDF calculates it would return: Str1Str2Str3
View 2 Replies
View Related
Aug 14, 2014
Wondering if it is possible to generate a random 4 digit number based off an alphanumeric string?
Example;
Cell A1 has 123XVF1234
Cell A2 has 321AFW4321
In B1 I would like to have a 4-6 digit number that is generated based on the alphanumeric data in Cell A1 (and so on down the list). If that is possible, I would also need to be able to convert back the 4-6 digit number back to its original alphanumeric value
Example;
If B1 returns 643562 it would need to be able to be converted back to 123XVF1234
View 7 Replies
View Related
Apr 7, 2014
In sheet1 I have a simple database consisting of 5 columns of data
Column A : Name ie James Jones
Column B : payroll number ie 123456
Column C : shift times ie 1245-2124
Column D : job title ie floor
Column E : comments ie A/L or 0600-1500
what I would like is some code that will go down Column E and if a 'time string' ie 1300-2130 is found then copy this string and paste into corresponding value in column C. If a text string is found ie A/L or Sick or anything like this then ignore and move onto next cell, loop this until all cells in column E have been checked.
View 4 Replies
View Related
