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
| Sub recherche()
Dim Plage As Range, Cellule As Range
Dim wsBD As Worksheet, Trouve As Boolean
Dim Y As Integer
On Error GoTo a
Set wsBD = Sheets(UserForm1.TextBox7.Value)
Set Plage = wsBD.Range("J2:J" & wsBD.Range("J65536").End(xlUp).Row)
wsBD.Activate
Trouve = False
For Each Cellule In Plage
If (UserForm1.TextBox7.Value) And (Cellule.Offset(0, 4).Value = UserForm1.TextBox7.Value) Then
With UserForm1
.TextBox7.Value = Cellule.Offset(0, 7).Value ' tel du contact
.CheckBox1.Visible = True
End With
Trouve = True
Cellule.Select
Exit For
End If
Next Cellule
If Not Trouve Then MsgBox "Aucun éléments trouvés pour la date du" & " " & UserForm1.TextBox7.Value & Chr(10) & "concernant le fournisseur" & " " & UserForm1.ComboBox1.Value, vbInformation
'protection_feuilles
a: If Err.Number = 9 Then: Exit Sub
End Sub |