Private Sub CommandButton2_Click()Manière autre numéro 2 :
Dim Cel As Range, Depart As String, ref As String
ref = Me.TextBox1.Text
With Me.ListBox1
.Clear: If ref = "" Then Exit Sub
.Visible = False
End With
ref = Me.TextBox1.Text
With Sheets("Ma Cave")
Set Cel = .Columns("I").Cells.Find(What:=ref, LookIn:=xlValues, lookat:=xlPart)
If Not Cel Is Nothing Then
ajout_liste ListBox1, .Range("A" & Cel.Row).Value
Depart = Cel.Address
Do
Set Cel = .Columns("I").Cells.FindNext(Cel)
If Not Cel Is Nothing Then ajout_liste ListBox1, .Range("A" & Cel.Row).Value
Loop While Depart <> Cel.Address
Else
MsgBox "Pas trouvé de vin en accord avec " & ref & "", vbCritical
End If
End With
ListBox1.Visible = True
End Sub
Private Sub ajout_liste(LB As Object, c As String)
LB.ListIndex = -1
On Error Resume Next
LB.Text = c
On Error GoTo 0
If LB.ListIndex = -1 Then LB.AddItem c
End Sub
Private Sub CommandButton3_Click()On pourrait en inventer d'autres encore, toutes aussi rapides ...
Dim plage As Range, ref As String, c As String, derlig As Long, i As Long, colonne As Integer, tablo
ref = Me.TextBox1.Text
With Me.ListBox1
.Clear:
If ref = "" Then Exit Sub
.Visible = False
End With
With Sheets("ma cave")
derlig = .Range("A" & Rows.Count).End(xlUp).Row
colonne = .Columns("I").Column
tablo = .Range("A1:I" & derlig)
For i = 1 To UBound(tablo, 1)
If tablo(i, colonne) = ref Then
c = tablo(i, 1)
With ListBox1
.ListIndex = -1
On Error Resume Next
.Text = c
On Error GoTo 0
If .ListIndex = -1 Then .AddItem c
End With
End If
Next
End With
Me.ListBox1.Visible = True
End Sub
Dim ref As String, i As Long, colonne As Long, c As String, n As Long, titiEt cette fois-ci : ni dico, ni collection, ni "jeu" avec listindex.
ref = TextBox1.Text
With Sheets("Ma cave")
colonne = .Columns.Count
titi = .Range("A1:I" & .Range("A" & Rows.Count).End(xlUp).Row)
n = 1
For i = 1 To UBound(titi, 1)
If titi(i, Columns("I").Column) = ref Then
c = titi(i, 1)
If WorksheetFunction.CountIf(.Columns(colonne), c) = 0 Then
.Cells(n, colonne) = c
n = n + 1
End If
End If
Next
titi = .Columns(colonne).Cells.SpecialCells(xlCellTypeConstants)
Me.ListBox1.List = titi
.Columns(colonne).Delete
End With
If titi(i, Columns("I").Column) = ref Thenpar
If titi(i, Columns("I").Column) Like "*" & ref & "*" Thenc'est tout.
Private Sub CommandButton2_Click() Dim Lig As Long, drLig As Long, Temp As Long, ref As String ref = Me.TextBox1.Text With Me.ListBox1 .Clear: If ref = "" Then Exit Sub .Visible = False End With ref = Me.TextBox1.Text Lig = 0 With Sheets("Feuil1") drLig = .Range("I" & Rows.Count).End(xlUp).Row On Error GoTo TraitementErreur Lig = .Columns("I").Cells.Find(What:=ref, LookIn:=xlValues, lookat:=xlPart).Row On Error GoTo 0 If Lig <> 0 Then ajout_liste ListBox1, .Range("A" & Lig).Value Do On Error GoTo TraitementFin Temp = .Range("I" & Lig & ":I" & drLig).Cells.Find(ref).Row If Temp = Lig Then Exit Do Else ajout_liste ListBox1, .Range("A" & Temp).Value: Lig = Temp Loop End If End With TraitementFin: ListBox1.Visible = True Exit Sub TraitementErreur: MsgBox "Pas trouvé de vin en accord avec " & ref & "", vbCritical End Sub
Dim ref As String, i As Long, colonne As Long, c As String, n As Long, titi ref = TextBox1.Text With Sheets("Ma cave") colonne = .Columns.Count titi = .Range("A1:I" & .Range("A" & Rows.Count).End(xlUp).Row) n = 1 For i = 1 To UBound(titi, 1) If titi(i, Columns("I").Column) = ref Then c = titi(i, 1) If WorksheetFunction.CountIf(.Columns(colonne), c) = 0 Then .Cells(n, colonne) = c n = n + 1 End If End If Next .Columns(colonne).Sort '<== TRI titi = .Columns(colonne).Cells.SpecialCells(xlCellTypeConstants) Me.ListBox1.List = titi .Columns(colonne).Delete End With
Private Sub CommandButton3_Click() Dim plage As Range, ref As String, c As String, derlig As Long, i As Long, colonne As Integer, tablo, tabloAtrier() ref = Me.TextBox1.Text With Me.ListBox1 .Clear: If ref = "" Then Exit Sub .Visible = False End With With Sheets("ma cave") derlig = .Range("A" & Rows.Count).End(xlUp).Row colonne = .Columns("I").Column tablo = .Range("A1:I" & derlig) For i = 1 To UBound(tablo, 1) If tablo(i, colonne) = ref Then c = tablo(i, 1) With ListBox1 .ListIndex = -1 On Error Resume Next .Text = c On Error GoTo 0 If .ListIndex = -1 Then .AddItem c End With End If Next End With '========== TRI ============= ReDim tabloAtrier(ListBox1.ListCount - 1) For i = 0 To ListBox1.ListCount - 1 tabloAtrier(i) = ListBox1.List(i) Next i Call triQuickSort(tabloAtrier, LBound(tabloAtrier), UBound(tabloAtrier)) '======== FIN TRI ============= Me.ListBox1.Visible = True End Sub Sub triQuickSort 'blabla End Sub
ref = Me.TextBox1.Text(lignes 3 & 8 du code). Réelle utilité ou simple oubli de ta part?
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questiontiti = .Columns(colonne).Cells.SpecialCells(xlCellTypeConstants) Me.ListBox1.List = titi .Columns(colonne).Delete
Private Sub CommandButtonRechercheVin_Click() Dim Lig As Long, drLig As Long, Temp As Long, ref As String Sheets("Ma Cave").Activate ref = Me.TextBox1.Text With Me.ListBox1 .Clear: If ref = "" Then Exit Sub End With With Sheets("Ma Cave") drLig = .Range("I" & Rows.Count).End(xlUp).Row On Error GoTo TraitementErreur Lig = .Columns("I").Cells.Find(What:=ref, LookIn:=xlValues, lookat:=xlPart).Row On Error GoTo 0 If Lig <> 0 Then ajout_liste ListBox1, .Range("A" & Lig).Value Do On Error GoTo TraitementFin Temp = .Range("I" & Lig & ":I" & drLig).Cells.Find(ref).Row If Temp = Lig Then Exit Do Else ajout_liste ListBox1, .Range("A" & Temp).Value: Lig = Temp Loop End If End With TraitementFin: ListBox1.Visible = True Exit Sub TraitementErreur: MsgBox "Pas trouvé de vin en accord avec " & ref & "", vbCritical End Sub Private Sub ajout_liste(LB As Object, c As String) LB.ListIndex = -1 On Error Resume Next LB.Text = c On Error GoTo 0 If LB.ListIndex = -1 Then LB.AddItem c End Sub
drLig = .Range("I" & Rows.Count).End(xlUp).Row
drLig = .Range("I" & Rows.Count).End(xlUp).Rowne saurait souffrir la MOINDRE faille