comparing text

[bensonsearch]

Hi,
I have the following code that will look up the excel sheet to see if customer name exsists. however this way must be an exact match ; ie joe citizen must be joe citizen but if its joeseph citizen it will let it through. i want it to find that.

any way to fix this?

Dim count As Integer
Dim vlook As String
Dim value As Variant
Dim address As Variant
count = Range("A2").CurrentRegion.Rows.count
count = count + 1
vlook = TextBox1.Text
value = Application.VLookup(vlook, Worksheets("Sheet1").Range("A2:G" & count), 1, False)
If IsError(value) Then

ThisWorkbook.Sheets("Sheet1").Range("A" & count).value = TextBox1.Text
ThisWorkbook.Sheets("Sheet1").Range("B" & count).value = TextBox2.Text
ThisWorkbook.Sheets("Sheet1").Range("C" & count).value = TextBox3.Text
ThisWorkbook.Sheets("Sheet1").Range("D" & count).value = TextBox4.Text
ThisWorkbook.Sheets("Sheet1").Range("E" & count).value = TextBox5.Text
ThisWorkbook.Sheets("Sheet1").Range("F" & count).value = TextBox6.Text
ThisWorkbook.Sheets("Sheet1").Range("G" & count).value = TextBox7.Text
Else
address = Application.VLookup(vlook, Worksheets("Sheet1").Range("A2:G" & count), 2, False)
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Customer, " & value & " " & address & " " & "already exsists, do you wish to add?"    ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2    ' Define buttons.
Title = "Error"    ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then    ' User chose Yes.
     ' Perform some action.
 
ThisWorkbook.Sheets("Sheet1").Range("A" & count).value = TextBox1.Text
ThisWorkbook.Sheets("Sheet1").Range("B" & count).value = TextBox2.Text
ThisWorkbook.Sheets("Sheet1").Range("C" & count).value = TextBox3.Text
ThisWorkbook.Sheets("Sheet1").Range("D" & count).value = TextBox4.Text
ThisWorkbook.Sheets("Sheet1").Range("E" & count).value = TextBox5.Text
ThisWorkbook.Sheets("Sheet1").Range("F" & count).value = TextBox6.Text
ThisWorkbook.Sheets("Sheet1").Range("G" & count).value = TextBox7.Text
Else    ' User chose No.
   'Perform some action.
Exit Sub
End If
End If

 

[Derek Brown]

See if the following helps:
http://www.mrexcel.com/forum/showthread.php?t=69649