Comparing the string in excel vba

[sarah2010]

Hey,
I have a bunch of data in two work sheets in an excel file. I like to see if the first 4 charecters match between the contents of sheet 1 & sheet 2 and like to show the mismatch in a different column. Attached a sample file. Refering to that, I want to see if "Mike" is existing in sheet 2. I f the logical test is "true" then ignore it. if not, print "mike" in column B. I wanna repeat this process for the contents in column A.
The contents can be anything & cant be hardcoded (not necessarily MIKE). Also the number of items can't be hardcoded. Hop, I explained gud :)

Thanks for your time,
Sarah

[mancubus]

hi Sarah2010.

try this with a backup of your file.


Sub CmprStrgs()

Dim wsToChk As Worksheet, wsToComp As Worksheet
Dim rngToChk As Range, rngToComp As Range
Dim cellToChk As Range, cellToComp As Range
Dim strToChk As String, strToComp As String
Dim found As Boolean

Set wsToChk = Worksheets("Sheet1")
Set wsToComp = Worksheets("Sheet2")

Set rngToChk = wsToChk.Range("A1:A" & wsToChk.Cells(Rows.Count, "A").End(xlUp).Row)
Set rngToComp = wsToComp.Range("A1:A" & wsToComp.Cells(Rows.Count, "A").End(xlUp).Row)

For Each cellToChk In rngToChk
For Each cellToComp In rngToComp
strToChk = Left(cellToChk.Value, 4)
strToComp = cellToComp.Value
found = InStr(1, strToChk, strToComp, vbTextCompare) > 0
If found = True Then
cellToChk.Offset(0, 1).Value = ""
Exit For
Else
cellToChk.Offset(0, 1).Value = strToChk
End If
Next
Next

End Sub


ps: what if you're going to compare 3 letter strings, such as Sam1999, in Sheet1 to names in Sheet2?

[sarah2010]

Thanks Mancubus!
It looks gud on the sample data. My requirement right now does not demand to compare 3 letter words :), but it's good for me to know how to make the code robust. Thanks again for your time.:friends:

[mancubus]

you're wellcome.

one correction.

change

found = InStr(1, strToChk, strToComp, vbTextCompare) > 0

to

found = InStr(1, strToComp, strToChk, vbTextCompare) > 0


this is for partial match.

[mancubus]

check the file attached that provides an option to choose between "exact match" or "partial match."

i used a UDF to remove numbers from strings.
see: http://www.vbaexpress.com/kb/getarticle.php?kb_id=816

a msgbox appears: "Do you want to perform EXACT match?"

if you click YES then only exact matches are flagged TRUE.
SAM = SAM

if you click NO then both exact and partial matches are flagged TRUE.
SAM = SAMMY