AW: Teilbegriff suchen, Beispiel
18.12.2007 16:12:00
Rudi
Hallo,
mal ein Bsp.:
Sub tt()
Dim vntQ, vntZ, vntMatch()
Dim iQ, iZ, i As Integer
With Sheets("Quelle")
vntQ = .Range(.Cells(1, 1), .Cells(65536, 1).End(xlUp))
End With
With Sheets("Ziel")
vntZ = .Range(.Cells(1, 1), .Cells(65536, 1).End(xlUp))
End With
ReDim vntMatch(1 To UBound(vntZ))
For iQ = 1 To UBound(vntQ)
For iZ = 1 To UBound(vntZ)
For i = Len(vntZ(iZ, 1)) To 3 Step -1
If InStr(1, vntQ(iQ, 1), Left(vntZ(iZ, 1), i), vbTextCompare) > 0 Then
vntMatch(iZ) = vntQ(iQ, 1)
Exit For
End If
Next
Next
Next
With Sheets("Ziel")
.Columns(2).ClearContents
.Range(.Cells(1, 2), .Cells(UBound(vntZ), 2)) = WorksheetFunction.Transpose(vntMatch)
End With
End Sub
Gruß
Rudi
Eine Kuh macht Muh, viele Kühe machen Mühe