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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
| Private Sub Rechercher()
' Rechercher les données en fonction des critères
Dim rCel As Range
Dim lgLig As Long
Dim lgLigDeb As Long
Dim i As Integer
Dim Critere1 As String
Dim Critere2 As String
Dim Critere3 As String
Dim Critere4 As String
Dim Critere5 As String
Dim Critere6 As String
Dim Critere7 As String
Dim Critere8 As String
Dim Critere9 As String
Dim Critere10 As String
Critere1 = "*"
If RechercheC1.Value <> "" Then Critere1 = RechercheC1.Value
Critere2 = "*"
If RechercheC2.Value <> "" Then Critere2 = RechercheC2.Value
Critere3 = "*"
If RechercheC3.Value <> "" Then Critere3 = RechercheC3.Value
Critere4 = "*"
If RechercheC4.Value <> "" Then Critere4 = RechercheC4.Value
Critere5 = "*"
If RechercheC5.Value <> "" Then Critere5 = RechercheC5.Value
Critere6 = "*"
If RechercheC6.Value <> "" Then Critere6 = RechercheC6.Value
Critere7 = "*"
If RechercheC7.Value <> "" Then Critere7 = RechercheC7.Value
Critere8 = "*"
If RechercheC8.Value <> "" Then Critere8 = RechercheC8.Value
Critere9 = "*"
If RechercheC9.Value <> "" Then Critere9 = RechercheC9.Value
Critere10 = "*"
If RechercheC10.Value <> "" Then Critere10 = RechercheC10.Value
ListBoxParquet.Clear
ListBoxParquet.ColumnWidths = "100;100;100;100;100;100;100;100;100;100;100;100;0"
Dim Tableau() As Variant
' Boucle de la 2me à la dernière ligne de la feuille
For lgLigDeb = 2 To Range("A" & Cells.Rows.Count).End(xlUp).Row
If Range("A" & lgLigDeb).Value Like Critere1 And Range("B" & lgLigDeb).Value Like Critere2 And Range("D" & lgLigDeb).Value Like Critere3 And _
Range("E" & lgLigDeb).Value Like Critere4 And Range("F" & lgLigDeb).Value Like Critere5 And Range("G" & lgLigDeb).Value Like Critere6 And _
Range("H" & lgLigDeb).Value Like Critere7 And Range("I" & lgLigDeb).Value Like Critere8 And Range("J" & lgLigDeb).Value Like Critere9 And _
Range("M" & lgLigDeb).Value Like Critere10 Then
i = i + 1
ReDim Preserve Tableau(1 To 13, 1 To i)
If RechercheC1.Value = "" Then
Tableau(1, i) = Range("A" & lgLigDeb).Value
Else
ListBoxParquet.ColumnWidths = "0;;;;;;;;;;;;0"
End If
If RechercheC2.Value = "" Then
Tableau(2, i) = Range("B" & lgLigDeb).Value
Else
ListBoxParquet.ColumnWidths = ";0;;;;;;;;;;;0"
End If
Tableau(3, i) = Range("C" & lgLigDeb).Value
If RechercheC3.Value = "" Then
Tableau(4, i) = Range("D" & lgLigDeb).Value
Else
ListBoxParquet.ColumnWidths = ";;;0;;;;;;;;;0"
End If
If RechercheC4.Value = "" Then
Tableau(5, i) = Range("E" & lgLigDeb).Value
Else
ListBoxParquet.ColumnWidths = ";;;;0;;;;;;;;0"
End If
If RechercheC5.Value = "" Then
Tableau(6, i) = Range("F" & lgLigDeb).Value
Else
ListBoxParquet.ColumnWidths = ";;;;;0;;;;;;;0"
End If
If RechercheC6.Value = "" Then
Tableau(7, i) = Range("G" & lgLigDeb).Value
Else
ListBoxParquet.ColumnWidths = ";;;;;;0;;;;;;0"
End If
If RechercheC7.Value = "" Then
Tableau(8, i) = Range("H" & lgLigDeb).Value
Else
ListBoxParquet.ColumnWidths = ";;;;;;;0;;;;;0"
End If
If RechercheC8.Value = "" Then
Tableau(9, i) = Range("I" & lgLigDeb).Value
Else
ListBoxParquet.ColumnWidths = ";;;;;;;;0;;;;"
End If
If RechercheC9.Value = "" Then
Tableau(10, i) = Range("J" & lgLigDeb).Value
Else
ListBoxParquet.ColumnWidths = ";;;;;;;;;0;;;"
End If
Tableau(11, i) = Range("K" & lgLigDeb).Value
If parametres.bouton_pv Then
Tableau(12, i) = Range("L" & lgLigDeb).Value * Val(parametres.Coeff.Value)
Else
Tableau(12, i) = Range("L" & lgLigDeb).Value
End If
Tableau(13, i) = Range("N" & lgLigDeb).Value
End If
Next lgLigDeb
ListBoxParquet.List() = Application.Transpose(Tableau)
End Sub |
Répondre avec citation
Envoyé par ouskel'n'or
. Voici le code de ma fonction:
Partager