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 34 35 36 37 38 39 40 41 42
| Sub Recherche()
Dim MaRecherche
Dim Ws As Worksheet
Dim c As Range
Dim Message As String, firstAddress As String, trouve As String
MaRecherche = InputBox("Saisir la valeur à rechercher", "Recherche") 'Je suppose que la recherche se fait que sur le NOM uniquement !!
Sheets("Recherche").Cells(1, 1) = UCase(MaRecherche)
Message = "La valeur " & MaRecherche & " a été trouvée :" & Chr(10) & Chr(10)
Sheets("Recherche").Range("A3:G1000").ClearContents
For Each Ws In Worksheets
If Not Ws.Name Like "Recherche" Then
With Ws
Set c = .Columns("A:A").Find(What:=MaRecherche, LookIn:=xlValues, LookAt:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Message = Message & "- dans la feuille " & Ws.Name & ", cellule " & c.Address & Chr(10)
With Sheets("Recherche")
.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = Ws.Name
.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0) = c.Value 'nom
.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0) = c.Offset(0, 1).Value 'prénom
.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0) = c.Offset(0, 2).Value 'Tel
.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0) = c.Offset(0, 3).Value 'eMail
.Cells(Rows.Count, 6).End(xlUp).Offset(1, 0) = c.Offset(0, 4).Value 'Dt naissance
.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0) = c.Address
End With
Set c = .Columns("A:A").FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End If
Next Ws
MsgBox Message & vbLf & vbLf & "Voir plus de détails sur la feuille 'Recherche'", vbInformation + vbOKOnly, "Résultat de la recherche"
DoEvents
Sheets("Recherche").Select: Range("A1").Select
End Sub |
Partager