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
| Private Sub TextBox1_Change()
On Error Resume Next
Application.ScreenUpdating = False
t = Range("a2:l" & Range("a65536").End(xlUp).Row)
ListBox1.Clear
X = 1
For i = 1 To UBound(t)
If t(i, 1) Like "*" & TextBox1.Value & "*" Then
ReDim Preserve ta(1 To 12, 1 To X)
For k = 1 To 12
ta(k, X) = t(i, k)
Next k: X = X + 1: End If: Next i
If X - 1 = 1 Then
ListBox1.List = Application.Transpose(ta)
For t = 1 To Range("a65536").End(xlUp).Row
If Cells(t, 1) = (ListBox1.List(ListBox1.ListIndex + 1, 0)) Then
Cells(t, 2).Activate: End If: Next t
Range(ActiveCell(1, 0), ActiveCell(1, 11)).Select
ListBox1.Clear: t = Selection: ListBox1.List = t: [a1].Select
Else
ListBox1.List = Application.Transpose(ta)
End If: Erase t, ta: Label6 = X - 1: If TextBox1 = "" Then ListBox1.Clear: Label6 = ""
Label6.Caption = "nb... " & ListBox1.ListCount: Beep
End Sub |
Partager