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
| Private Sub produitfiltre()
Dim Plage As Range, Cel As Range
Dim produit
Dim ERREUR
With Sheets("references")
If .AutoFilterMode Then
.AutoFilterMode = False
End If
produit = TextBoxproduitcherche
ERREUR = .Range("a2").End(xlDown).Offset(0, 0).Row
.Range("c2").AutoFilter
.Range("c2").AutoFilter Field:=3, Criteria1:="*" & produit & "*"
Set Plage = .Range("g2", .Range("g2").End(xlDown))
Set Plage = Plage.Cells.SpecialCells(xlCellTypeVisible)
If Plage.Count > ERREUR Then
If .AutoFilterMode Then
.AutoFilterMode = False
Exit Sub
End If
ListBoxPRODUITS.Clear
For Each Cel In Plage
With ListBoxPRODUITS
.AddItem Cel(1, -3)
.Column(1, .ListCount - 1) = Format(Cel(1, -2), "#,##0.00 ")
.Column(2, .ListCount - 1) = Format(Cel(1, 0), "#,##0.00 ")
.Column(3, .ListCount - 1) = Format(Cel(1, 4), "#,##0.00 ")
.Column(4, .ListCount - 1) = Format(Cel(1, 5), "###0")
.Column(5, .ListCount - 1) = Cel(1, 1)
End With
Next Cel
Selection.AutoFilter
End With
End Sub |
Partager