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
| Public FL2 As Worksheet
Public FL4 As Worksheet
Public FL7 As Worksheet
Public plage_FL2 As Range
Public plage_FL7 As Range
Sub declarations()
Set FL2 = ThisWorkbook.Worksheets("FL2")
Set FL4 = ThisWorkbook.Worksheets("FL4")
Set FL7 = ThisWorkbook.Worksheets("FL7")
Set plage_FL2 = FL2.Range("F1:F" & FL2.Range("F" & Rows.Count).End(xlUp).Row)
Set plage_FL7 = FL7.Range("A1:Z" & FL7.Range("A" & Rows.Count).End(xlUp).Row)
End Sub
************************************************************************************************************
Function derligne(ByVal Feuille As Worksheet) As Long
derligne = Feuille.Cells(Rows.Count, 1).End(xlUp).Row
End Function
************************************************************************************************************
Sub lancement()
UserForm1.Show
End Sub
************************************************************************************************************
Private Sub UserForm_Initialize()
Call declarations
With Me.Listbox1
.ColumnHeads = False
.ColumnCount = 8
.ColumnWidths = "0;135;0;0;0;0;0;0"
End With
FL4.Cells.Clear
i = 1
For j = 1 To derligne(FL7)
If FL7.Cells(j, 1) <> "" And FL7.Cells(j, 22) = 0 And FL7.Cells(j, 24) = 1 Then
With FL4
.Cells(i, 1).Value = FL7.Cells(j, 13)
.Cells(i, 2).Value = FL7.Cells(j, 1)
.Cells(i, 3).Value = FL7.Cells(j, 14)
.Cells(i, 4).Value = FL7.Cells(j, 15)
.Cells(i, 5).Value = FL7.Cells(j, 16)
.Cells(i, 6).Value = FL7.Cells(j, 17)
.Cells(i, 7).Value = FL7.Cells(j, 18)
.Cells(i, 8).Value = FL7.Cells(j, 19)
End With
i = i + 1
End If
Next j
End Sub
************************************************************************************************************
Private Sub CommandButton1_Click()
Dim valeur As String
Dim trouve As Range
For i = 1 To derligne(FL4)
valeur = FL4.Cells(i, 3).Value
If Application.WorksheetFunction.CountIf(plage_FL2, valeur) = 1 Then
Set trouve = plage_FL2.Cells.find(valeur, lookin:=xlValues, lookat:=xlWhole)
If Not trouve Is Nothing Then
If FL2.Cells(trouve.Row, 19).Value = 9999 Then
Me.Listbox1.AddItem
Me.Listbox1.List(Me.Listbox1.ListCount - 1, 0) = FL4.Cells(i, 2).Value
Me.Listbox1.List(Me.Listbox1.ListCount - 1, 1) = valeur
Me.Listbox1.List(Me.Listbox1.ListCount - 1, 2) = FL4.Cells(i, 3).Value
Me.Listbox1.List(Me.Listbox1.ListCount - 1, 3) = FL4.Cells(i, 4).Value
Me.Listbox1.List(Me.Listbox1.ListCount - 1, 4) = FL4.Cells(i, 5).Value
Me.Listbox1.List(Me.Listbox1.ListCount - 1, 5) = FL4.Cells(i, 6).Value
Me.Listbox1.List(Me.Listbox1.ListCount - 1, 6) = FL4.Cells(i, 7).Value
Me.Listbox1.List(Me.Listbox1.ListCount - 1, 7) = FL4.Cells(i, 8).Value
End If
End If
End If
Next i
End Sub |
Partager