Den lørdag den 4. februar 2017 kl. 00.14.19 UTC+1 skrev Claus Busch:
Hi,

Am Thu, 2 Feb 2017 00:28:55 -0800 (PST) schrieb :

In Sheet1 I have peoples names and addresses. Names in column A, and addresses in B-column like this:

Mystreet 23, 2.th.
Your Street 5, aptm. 345
Carl Bark Street, 4.th.
Hollowgate 2

and so on.

In sheet 2, Column A I have list of street names like

Carl Barks Street
Hollowgate
Mystreet
Your Street

and so on.

What I need to do is compare the street name part of the address (Sheet 1, column B) with the list of street names (sheet2, column A) and mark all the adresses where the street name doesn't have a match in the list of street names. In the above example the list of adresses contains the name Carl Bark Street, which is incorrect as the name in ther list is Carl BarkS Street. So it has to marked. Sio does streets with extra blanks, or street that do not exist at all.

another suggestion:

Sub Test()
Dim LRow As Long, i As Long, j As Long
Dim myRng As Range, c As Range
Dim varData As Variant
Dim myStr As String

With Sheets("Sheet2")
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = .Range("A1:A" & LRow)
End With

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, "B").End(xlUp).Row
varData = .Range("B1:B" & LRow)
For i = LBound(varData) To UBound(varData)
For j = 1 To Len(varData(i, 1))
If IsNumeric(Mid(varData(i, 1), j, 1)) Then
myStr = Application.Trim(Mid(varData(i, 1), 1, j - 1))
Exit For
End If
Next
Set c = myRng.Find(myStr)
If c Is Nothing Then .Cells(i, 2).Interior.Color = vbRed
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Thank you Claus. That did it.