1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
| Option Explicit
Option Compare Text
Private Sub CommandButton1_Click()
Dim i As Long, tablo, NOM As String, PRENOM As String, pos As Integer
Dim dico As Object
Set dico = CreateObject("Scripting.Dictionary")
NOM = Cells(3, 1).Value
tablo = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value
For i = 1 To UBound(tablo, 1)
NOM = Trim(Replace(tablo(i, 1), Chr(160), " "))
pos = InStr(NOM, " ")
PRENOM = IIf(pos > 0, Mid(NOM, pos + 1, 1) & ".", "")
If pos Then
NOM = Left(NOM, pos - 1)
End If
If Not dico.exists(NOM & " " & PRENOM) Then dico.Add NOM & " " & PRENOM, i
Next
Erase tablo
tablo = Range("B1:B" & Cells(Rows.Count, 1).End(xlUp).Row).Value
For i = 1 To UBound(tablo, 1)
NOM = Trim(Replace(tablo(i, 1), Chr(160), " "))
pos = InStr(NOM, " ")
PRENOM = IIf(pos > 0, Mid(NOM, pos + 1, 1) & ".", "")
If pos Then
NOM = Left(NOM, pos - 1)
End If
If dico.exists(NOM & " " & PRENOM) Then
MsgBox " j'ai en colonne B " & tablo(i, 1) & " qui correspond à un individu en colonne A"
End If
Next
End Sub |
Partager