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
| Private Sub Search_Click()
Dim Plage As Range, c As Range
Dim LastLig As Integer
Dim TbCrit, TbCol
Dim i As Byte
Application.ScreenUpdating = False
TbCrit = Array(Me.Title, Me.Date_publication, Me.Type_Document, Me.Author, Me.Subject, Me.Keywords)
TbCol = Array(1, 3, 2, 4, 5, 6)
With Sheets("Gestion des documents")
.AutoFilterMode = False
LastLig = .Cells(.Rows.Count, "A").End(xlUp).Row
Set Plage = .Range("B1:G" & LastLig)
For i = 0 To 5
If TbCrit(i) <> "" Then
If i = 1 Then
If TbCrit(1) <> "N/A" Then Plage.AutoFilter field:=3, Criteria1:=">=" & CLng(DateValue("1/1/" & TbCrit(1))), Criteria2:="<=" & CLng(DateValue("31/12/" & TbCrit(1)))
Else
Plage.AutoFilter field:=TbCol(i), Criteria1:=IIf(i = 5, "*" & TbCrit(i) & "*", TbCrit(i))
End If
End If
Next i
With Me.ListBox1
.ColumnCount = 6
.Clear
End With
If .Range("B1:B" & LastLig).SpecialCells(xlCellTypeVisible).Count > 1 Then
Set Plage = .Range("B2:B" & LastLig).SpecialCells(xlCellTypeVisible)
For Each c In Plage
Me.ListBox1.AddItem c
For i = 1 To 5
Me.ListBox1.List(Me.ListBox1.ListCount - 1, i) = c.Offset(0, i)
Next i
Next c
Set Plage = Nothing
End If
.AutoFilterMode = False
End With
End Sub |
Partager