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 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79
| Public Sub CommandButton1_Click()
'*****************************************************************
Dim Critère1 As Range, Critère2 As Range, Ctr1 As Integer, Ctr2 As Integer, Crit As Range
Dim i As Integer, Ligne As Long
Dim j As Integer, Tabl1() As Variant, Tabl2() As Variant
With Sheets("Feuil2")
On Error Resume Next
.ShowAllData
If Err.Number <> 0 Then Err.Clear
On Error GoTo 0
.[X:Y].ClearContents 'effacement de la zone de critères
Set Critère1 = .[X1] '"Critère1" est la cellule X1 deFeuil2
Critère1 = "equipements" 'nom de l'entête (doit être strictement le même que A1)
End With
Ctr1 = -1
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) = True Then
Ctr1 = Ctr1 + 1
ReDim Preserve Tabl1(Ctr1)
Tabl1(Ctr1) = .List(i) 'remplissage de la zone critères
End If
Next i
End With
With Sheets("Feuil2")
Set Critère2 = .[Y1]
Critère2 = "REF VOIE"
End With
Ctr2 = -1
With Me.ListBox2
For j = 0 To .ListCount - 1
If .Selected(j) = True Then
Ctr2 = Ctr2 + 1
ReDim Preserve Tabl2(Ctr2)
Tabl2(Ctr2) = .List(j)
End If
Next j
End With
With Sheets("Feuil2")
For i = 0 To UBound(Tabl1)
For j = 0 To UBound(Tabl2)
.Cells(.Rows.Count, "X").End(xlUp).Offset(1).Value = Tabl1(i)
.Cells(.Rows.Count, "X").End(xlUp).Offset(, 1).Value = Tabl2(j)
Next j
Next i
' Ligne = .[X:Y].Find("*", , , , xlByRows, xlPrevious).Row 'recherche de la dernière ligne de la zone de critères
Set Crit = .[X1:Y1].Resize(i * j + 1) 'définition de la zone de critères
'application du filtre :
.Range("A1", .Cells(.Rows.Count, 6).End(xlUp)).AdvancedFilter xlFilterInPlace, CriteriaRange:=Crit
End With
' If Indice1 = 0 Then
' Range("$B$4:$O$30").AutoFilter Field:=5
' Else
' Range("$B$4:$O$30").AutoFilter Field:=5, Criteria1:=Tablo1, Operator:=xlAnd
' End If
'
' Range("$B$4:$O$30").AutoFilter Field:=4, Criteria1:="V", Operator:=xlAnd
'****************************************************************
' Dim Tablo2()
'Dim K As Integer, Indice2 As Integer
'
'With Me.ComboBox2
' For K = 0 To .Listindex - 1
'If .selected(K) = True Then
' ReDim Preserve Tablo2(Indice2)
' ' Tablo2(Indice2) = .List(K)
' End If
' Next K
' End With
' Range("$B$4:$O$30").AutoFilter Field:=4, Criteria1:=Tablo2, Operator:=xlAnd
'End If
' If .selected(P) = True Then
' ActiveSheet.Range("$B$4:$O$30").AutoFilter Field:=4, Criteria1:="Tablo2", Operator:=xlAnd
'End If
'End With
End Sub |
Partager