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 43 44 45 46
| Sub recherch()
Dim Plage As Range
Dim Tableau(), Resultat() As String
Dim i As Integer, j As Integer, m As Integer
Dim Un As Collection
Dim Doublons As String
Set Un = New Collection
If UserForm2.Désignation.Text = "" Then
MsgBox "Vous devez saisir une Recherche", vbCritical
End If
'La plage de cellules à tester
Set Plage = Range("A1:A" & Range("A65536").End(xlUp).Row)
Tableau = Plage.Value
ReDim Preserve Resultat(2, 1)
On Error Resume Next
'boucle sur la plage à tester
For i = 4 To Plage.Count
'Utilise une collection pour rechercher les doublons
'(les collections n'acceptent que des données uniques)
Un.Add Tableau(i, 1), Tableau(i, 1) 'S'il y a une erreur (donc presence d'un doublon)
If Err <> 0 Then
If UCase(Range("A" & i)) Like "*" & UCase(UserForm2.Désignation.Value) & "*" Then
UserForm2.Désignation.Value = ActiveSheet.Cells(i, "A").Value
UserForm2.Entrée.Value = ActiveSheet.Cells(i, "D").Value
UserForm2.Puht.Value = ActiveSheet.Cells(i, "E").Value
UserForm2.Pvte.Value = ActiveSheet.Cells(i, "G").Value
UserForm2.Dlc.Value = ActiveSheet.Cells(i, "J").Value
UserForm2.TextBox_Stock.Value = ActiveSheet.Cells(i, "F").Value
UserForm2.TextBox_Etat.Value = ActiveSheet.Cells(i, "I").Value
UserForm2.TextBox_Sortie.Value = ActiveSheet.Cells(i, "H").Value
End If
Next i
End Sub |
Partager