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
| Private Sub TextBox8_Change()
Dim Tb, Cible, N As Integer, nb As Integer, L As Long, Present As Integer, x, Data As Range
'Feuil1.Range("A1:H100").Formula = "=ROW()*COLUMN()&""ème Test"""
nb = Feuil1.Columns(1).SpecialCells(11).Row
ReDim Lx(1 To 1)
Cible = TextBox1
L = 8
ReDim Tb(1 To 1) 'un tableau vide au départ
With Feuil1
'6,10,13,15,18,21: 6 éléms
Do
Present = WorksheetFunction.CountIf(.Range(Cells(L, 3), Cells(L, 21)), "*" & Cible & "*")
If Present Then
N = N + 1
ReDim Preserve Tb(1 To N)
x = .Range(Cells(L, 1), Cells(L, 21))
Tb(N) = Array(x(1, 6), x(1, 10), x(1, 13), x(1, 15), x(1, 18), x(1, 21))
End If
Present = 0
L = L + 1
Loop Until L = nb
End With
ListBox1.Clear
If N Then
x = WorksheetFunction.Transpose(Tb)
ListBox1.List = x
ListBox1.Column = ListBox1.List
End If
End Sub |
Partager