Option Explicit Option Base 1 Private Sub ComboBox1_Change() Dim i&, fin&, y&, a& ComboBox2.Clear If ComboBox1.ListIndex = -1 Then ListBox1.Clear: Exit Sub With Feuil3 fin = .Range("A" & Rows.Count).End(xlUp).Row aa = .Range("A2:K" & fin) y = 1 For i = 1 To UBound(aa) aa(i, 11) = "" Next i For i = 1 To UBound(aa) If aa(i, 2) ComboBox1 Then aa(i, 11) "oui": y = y + 1 Next i If y = 1 Then Exit Sub ReDim bb(y - 1, 10) y = 1 For i = 1 To UBound(aa) If aa(i, 11) = "oui" Then ComboBox2.AddItem aa(i, 7) For a = 1 To 10 bb(y, a) = aa(i, a) Next a y = y + 1 End If Next i ListBox1.ColumnCount = 10 ListBox1.ColumnWidths = "20;80;30;80;60;60;60;60;60;80" ListBox1.List = bb End With End Sub Private Sub ComboBox2_Change() Dim i&, y&, a& y = 1 For i = 1 To UBound(aa) aa(i, 11) = "" Next i For i = 1 To UBound(aa) If aa(i, 2) ComboBox1 And aa(i, 7) ComboBox2 Then aa(i, 11) = "oui": y = y + 1 Next i If y = 1 Then Exit Sub ReDim bb(y - 1, 10) y = 1 For i = 1 To UBound(aa) If aa(i, 11) = "oui" Then For a = 1 To 10 bb(y, a) = aa(i, a) Next a y = y + 1 End If Next i ListBox1.List = bb End Sub Private Sub CommandButton1_Click() Dim i&, a& Feuil2.Range("A4:H2000").ClearContents If ListBox1.ListCount = 0 Then Exit Sub Feuil2.Range("D1") = ComboBox1 ReDim cc(UBound(bb), UBound(bb, 2) - 2) For i = 1 To UBound(bb) For a = 3 To 10 cc(i, a - 2) = bb(i, a) Next a Next i Feuil2.Range("A4").Resize(UBound(cc), UBound(cc, 2)) = cc Unload Me Feuil2.Select End Sub Private Sub TextBox1_Change() Dim i&, a&, y&, fin& ListBox1.Clear If TextBox1 = "" Then Exit Sub If TextBox1 <> "" Then ComboBox1 "": ComboBox2 "" With Feuil3 fin = .Range("A" & Rows.Count).End(xlUp).Row aa = .Range("A2:K" & fin) For i = 1 To UBound(aa) aa(i, 11) = "" Next i y = 1 For i = 1 To UBound(aa) For a = 1 To 10 If aa(i, a) Like "*" & TextBox1 & "*" Then aa(i, 11) "oui": y y + 1: GoTo 1 Next a 1 Next i If y = 1 Then Exit Sub ReDim bb(y - 1, 10) y = 1 For i = 1 To UBound(aa) If aa(i, 11) = "oui" Then For a = 1 To 10 bb(y, a) = aa(i, a) Next a y = y + 1 End If Next i ListBox1.ColumnCount = 10 ListBox1.ColumnWidths = "20;80;30;80;60;60;60;60;60;80" ListBox1.List = bb End With End Sub Private Sub UserForm_Initialize() Dim aa As Variant aa = Feuil4.Range("C3:C" & Feuil4.Range("C" & Rows.Count).End(xlUp).Row) ComboBox1.List = aa End Sub
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionOption Explicit Option Base 1 Private Sub ComboBox1_Change() Dim i&, fin&, y&, a& ComboBox2.Clear If ComboBox1.ListIndex = -1 Then ListBox1.Clear: Exit Sub With Feuil3 fin = .Range("A" & Rows.Count).End(xlUp).Row aa = .Range("A2:K" & fin) y = 1 For i = 1 To UBound(aa) aa(i, 11) = "" Next i For i = 1 To UBound(aa) If aa(i, 2) ComboBox1 Then aa(i, 11) "oui": y = y + 1 Next i If y = 1 Then Exit Sub ReDim bb(y - 1, 10) y = 1 For i = 1 To UBound(aa) If aa(i, 11) = "oui" Then ComboBox2.AddItem aa(i, 7) For a = 1 To 10 bb(y, a) = aa(i, a) Next a y = y + 1 End If Next i ListBox1.ColumnCount = 10 ListBox1.ColumnWidths = "20;80;30;80;60;60;60;60;60;80" ListBox1.List = bb End With End Sub Private Sub ComboBox2_Change() Dim i&, y&, a& y = 1 For i = 1 To UBound(aa) aa(i, 11) = "" Next i For i = 1 To UBound(aa) If aa(i, 2) ComboBox1 And aa(i, 7) ComboBox2 Then aa(i, 11) = "oui": y = y + 1 Next i If y = 1 Then Exit Sub ReDim bb(y - 1, 10) y = 1 For i = 1 To UBound(aa) If aa(i, 11) = "oui" Then For a = 1 To 10 bb(y, a) = aa(i, a) Next a y = y + 1 End If Next i ListBox1.List = bb End Sub Private Sub CommandButton1_Click() Dim i&, a& Feuil2.Range("A4:H2000").ClearContents If ListBox1.ListCount = 0 Then Exit Sub Feuil2.Range("D1") = ComboBox1 ReDim cc(UBound(bb), UBound(bb, 2) - 2) For i = 1 To UBound(bb) For a = 3 To 10 cc(i, a - 2) = bb(i, a) Next a Next i Feuil2.Range("A4").Resize(UBound(cc), UBound(cc, 2)) = cc Unload Me Feuil2.Select End Sub Private Sub TextBox1_Change() Dim i&, a&, y&, fin& ListBox1.Clear If TextBox1 = "" Then Exit Sub If TextBox1 <> "" Then ComboBox1 "": ComboBox2 "" With Feuil3 fin = .Range("A" & Rows.Count).End(xlUp).Row aa = .Range("A2:K" & fin) For i = 1 To UBound(aa) aa(i, 11) = "" Next i y = 1 For i = 1 To UBound(aa) For a = 1 To 10 If aa(i, a) Like "*" & TextBox1 & "*" Then aa(i, 11) "oui": y y + 1: GoTo 1 Next a 1 Next i If y = 1 Then Exit Sub ReDim bb(y - 1, 10) y = 1 For i = 1 To UBound(aa) If aa(i, 11) = "oui" Then For a = 1 To 10 bb(y, a) = aa(i, a) Next a y = y + 1 End If Next i ListBox1.ColumnCount = 10 ListBox1.ColumnWidths = "20;80;30;80;60;60;60;60;60;80" ListBox1.List = bb End With End Sub Private Sub UserForm_Initialize() Dim aa As Variant aa = Feuil4.Range("C3:C" & Feuil4.Range("C" & Rows.Count).End(xlUp).Row) ComboBox1.List = aa End Sub
fin = .Range("A" & Rows.Count).End(xlUp).Row aa = .Range("A2:K" & fin) y = 1 For i = 1 To UBound(aa) aa(i, 11) = "" Next i For i = 1 To UBound(aa) If aa(i, 2) ComboBox1 Then aa(i, 11) "oui": y = y + 1 Next i If y = 1 Then Exit Sub ReDim bb(y - 1, 10)
ComboBox2("plage").Selection = "tout" fin = .Range("A" & Rows.Count).End(xlUp).Row aa = .Range("A2:K" & fin) y = 1 For i = 1 To UBound(aa) aa(i, 11) = "" Next i For i = 1 To UBound(aa) If aa(i, 2) ComboBox1 Then aa(i, 11) "oui": y = y + 1 Next i If y = 1 Then Exit Sub ReDim bb(y - 1, 10) y = 1 For i = 1 To UBound(aa) If aa(i, 11) = "oui" Then ComboBox2.AddItem aa(i, 7) For a = 1 To 10 bb(y, a) = aa(i, a) Next a y = y + 1 End If Next i ListBox1.ColumnCount = 10 ListBox1.ColumnWidths = "20;80;30;80;60;60;60;60;60;80" ListBox1.List = bb
If TextBox1 = "" Then Exit Sub If TextBox1 <> "" Then ComboBox1 "": ComboBox2 ""
Sub Export1() ' ' Export1 Macro ' ' Sheets("Affichage fiche").Select Range("C1").Select ActiveCell.FormulaR1C1 = "='Affichage Liste'!R[3]C[-2]" Range("B3").Select ActiveCell.FormulaR1C1 = "='Affichage Liste'!R[1]C" Range("B4").Select ActiveCell.FormulaR1C1 = "='Affichage Liste'!RC[1]" Range("B5").Select ActiveCell.FormulaR1C1 = "='Affichage Liste'!R[-1]C[2]" Range("B6").Select ActiveCell.FormulaR1C1 = "='Affichage Liste'!R[-2]C[4]" Range("B7").Select ActiveCell.FormulaR1C1 = "='Affichage Liste'!R[-3]C[5]" Range("B8").Select ActiveCell.FormulaR1C1 = "='Affichage Liste'!R[-4]C[6]" Range("E1").Select ActiveCell.FormulaR1C1 = "='Affichage Liste'!R[3]C" Range("E2").Select End Sub
With Sheets("Affichage fiche") .Range("C1").FormulaR1C1 = "='Affichage Liste'!R[3]C[-2]" .Range("B3").FormulaR1C1 = "='Affichage Liste'!R[1]C" .Range("B4").FormulaR1C1 = "='Affichage Liste'!RC[1]" .Range("B5").FormulaR1C1 = "='Affichage Liste'!R[-1]C[2]" .Range("B6").FormulaR1C1 = "='Affichage Liste'!R[-2]C[4]" .Range("B7").FormulaR1C1 = "='Affichage Liste'!R[-3]C[5]" .Range("B8").FormulaR1C1 = "='Affichage Liste'!R[-4]C[6]" .Range("E1").FormulaR1C1 = "='Affichage Liste'!R[3]C" End With