Chercher mot dans une feuille, afficher résultat sur listBox
n
Bonjour;
Je tiens vous remercie d’avance de votre soutien habituel
La problématique est que: mon CommandButton, cherche que la première colonne A, je voudrais que ma recherche soit dans toute la feuille BDD, merci.
Private Sub CommandButton4_Click()
i = 0
Me.ListBox1.Clear
Me.ListBox1.ColumnCount = 2
Me.ListBox1.BoundColumn = 2
Me.ListBox1.ColumnWidths = "0;150"
With Sheets("BDD")
.AutoFilterMode = False
Set plage = .Range(.[A2], .Cells(.Rows.Count, 1).End(xlUp)).Resize(, 1)
plage.AutoFilter 1, "*" & Me.TextBox14 & "*"
Set plage = plage.Offset(1).Resize(plage.Rows.Count - 1)
If Application.Subtotal(103, plage) > 0 Then
Set plage = plage.Resize(, 1).SpecialCells(xlCellTypeVisible)
For Each C In plage
Me.ListBox1.AddItem C.Row
Me.ListBox1.List(i, 1) = C.Value
i = i + 1
Next C
End If
.AutoFilterMode = False
End With
Me.TextBox12 = ""
Me.TextBox14 = ""
Me.TextBox15 = ""
Me.TextBox16 = ""
Me.TextBox8 = ""
Me.OptionButton9.Value = False
Me.OptionButton10.Value = False
Me.OptionButton8.Value = False
Me.OptionButton7.Value = False
Me.OptionButton1.Value = False
Me.OptionButton2.Value = False
Me.OptionButton4.Value = False
For i = 0 To N - 1
If ListBox.Selected(i) Then
MsgBox "la valeur suivante est sélectionnée : " & ListBox.List(i)
End If
Next i
If ListBox1.ListCount = 0 Then
MsgBox "Ce matériel n'existe pas dans la base de données"
End If
End Sub
salutations.
n
la repense:
Private Sub CommandButton4_Click()
Dim Prem As String
Dim c As Range
With Me.ListBox1
.Clear
.ColumnCount = 2
.BoundColumn = 2
.ColumnWidths = "0;150"
End With
With Worksheets("BDD").UsedRange
Set c = .Find(Me.TextBox14, LookIn:=xlValues, Lookat:=xlPart)
If Not c Is Nothing Then
Prem = c.Address
Do
With Me.ListBox1
.AddItem c.Row
.List(.ListCount - 1, 1) = c.Offset(, 1 - c.Column)
End With
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> Prem
End If
End With
Me.TextBox12 = ""
Me.TextBox14 = ""
Me.TextBox15 = ""
Me.TextBox16 = ""
Me.TextBox8 = ""
Me.OptionButton9.Value = False
Me.OptionButton10.Value = False
Me.OptionButton8.Value = False
Me.OptionButton7.Value = False
Me.OptionButton1.Value = False
Me.OptionButton2.Value = False
Me.OptionButton4.Value = False
Me.OptionButton11.Value = False
If ListBox1.ListCount = 0 Then
MsgBox "Ce matériel n'existe pas dans la base de données"
End If
End Sub
Salutations