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 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94
| If ComboBox1.Value = "PORTAIL BATTANT" Then
'Traitement du besoin
With Worksheets("Relations")
If .FilterMode = True Then .ShowAllData
.Range("A1").AutoFilter Field:=2, Criteria1:=type_f
.Range("A1").AutoFilter Field:=6, Criteria1:=largeur_f
.Range("A1").AutoFilter Field:=7, Criteria1:=forme_f
.Range("A1").AutoFilter Field:=8, Criteria1:=materiau_f
Set tmp = ActiveCell.CurrentRegion.SpecialCells(xlCellTypeVisible)
tmp.Copy Feuil7.Range("A1")
End With
ElseIf ComboBox1.Value = "PORTAIL COULISSANT" Then
With Worksheets("Relations")
If .FilterMode = True Then .ShowAllData
.Range("A1").AutoFilter Field:=2, Criteria1:=type_f
.Range("A1").AutoFilter Field:=6, Criteria1:=largeur_f
.Range("A1").AutoFilter Field:=7, Criteria1:=forme_f
.Range("A1").AutoFilter Field:=8, Criteria1:=materiau_f
Set tmp = ActiveCell.CurrentRegion.SpecialCells(xlCellTypeVisible)
tmp.Copy Feuil7.Range("A1")
End With
ElseIf ComboBox1.Value = "PORTE GARAGE" Then
With Worksheets("Relations")
If .FilterMode = True Then .ShowAllData
.Range("A1").AutoFilter Field:=2, Criteria1:=type_f
.Range("A1").AutoFilter Field:=3, Criteria1:=type_porte_f
.Range("A1").AutoFilter Field:=6, Criteria1:=hauteur_porte_f
Set tmp = ActiveCell.CurrentRegion.SpecialCells(xlCellTypeVisible)
tmp.Copy Feuil7.Range("A1")
End With
End If
Worksheets("Relations").AutoFilterMode = False
With Worksheets("tmp")
poids_max_min_k = .Range("L2")
poids_max_max_k = .Range("M2")
traction_max_min_k = .Range("N2")
traction_max_max_k = .Range("O2")
largeur_max_min_k = .Range("P2")
largeur_max_max_k = .Range("Q2")
hauteur_max_min_k = .Range("R2")
hauteur_max_max_k = .Range("S2")
End With
If ComboBox1.Value = "PORTAIL BATTANT" Then
'Traitement des résultats
With Worksheets("BASE")
If .FilterMode = True Then .ShowAllData
.Range("A1").AutoFilter Field:=9, Criteria1:="PORTAIL BATTANT"
.Range("A1").AutoFilter Field:=20, Criteria1:=">largeur_max_min_k", Operator:=xlAnd, Criteria2:="<largeur_max_max_k"
.Range("A1").AutoFilter Field:=21, Criteria1:=">poids_max_min_k", Operator:=xlAnd, Criteria2:="<poids_max_max_k"
Set result = ActiveCell.CurrentRegion.SpecialCells(xlCellTypeVisible)
result.Copy Feuil9.Range("A1")
End With
ElseIf ComboBox1.Value = "PORTAIL COULISSANT" Then
With Worksheets("BASE")
If .FilterMode = True Then .ShowAllData
.Range("A1").AutoFilter Field:=2, Criteria1:="PORTAIL COULISSANT"
.Range("A1").AutoFilter Field:=20, Criteria1:=">largeur_max_min_k", Operator:=xlAnd, Criteria2:="<largeur_max_max_k"
.Range("A1").AutoFilter Field:=21, Criteria1:=">poids_max_min_k", Operator:=xlAnd, Criteria2:="<poids_max_max_k"
Set result = ActiveCell.CurrentRegion.SpecialCells(xlCellTypeVisible)
result.Copy Feuil9.Range("A1")
End With
ElseIf ComboBox1.Value = "PORTE GARAGE" Then
With Worksheets("BASE")
If .FilterMode = True Then .ShowAllData
.Range("A1").AutoFilter Field:=2, Criteria1:="PORTE GARAGE"
.Range("A1").AutoFilter Field:=15, Criteria1:=">hauteur_max_min_k", Operator:=xlAnd, Criteria2:="<hauteur_max_max_k"
.Range("A1").AutoFilter Field:=22, Criteria1:=">traction_max_min_k", Operator:=xlAnd, Criteria2:="<traction_max_max_k"
Set result = ActiveCell.CurrentRegion.SpecialCells(xlCellTypeVisible)
result.Copy Feuil9.Range("A1")
End With
End If |