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
| Private Sub cbtOK_Click()
Dim wsBD As Worksheet
Dim derLig As Long
Dim Lig As Long
Dim Plage As Range
Dim CritRente As String
Dim CritDateDeb As String
Dim CritDateFin As String
Dim LigList As Long
Dim Cumul As Currency
Dim nc As Integer, s As String
Set wsBD = Worksheets("TPor")
' Dernière ligne dans la feuille BD
derLig = wsBD.Range("A" & Cells.Rows.Count).End(xlUp).Row
If derLig < 2 Then Exit Sub
' Définition de la plage en colonne A
Set Plage = wsBD.Range("A2:A" & derLig)
' Date Début
CritDateDeb = tbxStart.Value
If tbxStart.Value = "" Or Not IsDate(tbxStart.Value) Then
CritDateDeb = Format(Application.WorksheetFunction.Min(Plage), "dd/mm/yyyy")
End If
' Date Fin
CritDateFin = tbxEnd.Value
If tbxEnd.Value = "" Or Not IsDate(tbxEnd.Value) Then
CritDateFin = Format(Application.WorksheetFunction.Max(Plage), "dd/mm/yyyy")
End If
CritDateFin = DateAdd("d", 1, CritDateFin)
LigList = 1
' Vider la listview
ListBox1.Clear
' Boucle sur toutes les lignes
For Lig = 2 To derLig
' Rechercher par rapport aux critères
If CDate(wsBD.Range("A" & Lig).Value) >= CDate(CritDateDeb) And _
CDate(wsBD.Range("A" & Lig).Value) < CDate(CritDateFin) Then
' Remplir la première colonne
With ListBox1
.AddItem wsBD.Range("A" & Lig).Value
'Remplissage colonnes 2 à 5
.List(.ListCount - 1, 1) = wsBD.Range("B" & Lig).Value
.List(.ListCount - 1, 2) = wsBD.Range("C" & Lig).Value
.List(.ListCount - 1, 3) = wsBD.Range("D" & Lig).Value
.List(.ListCount - 1, 4) = wsBD.Range("E" & Lig).Value
.List(.ListCount - 1, 5) = wsBD.Range("F" & Lig).Value
.List(.ListCount - 1, 6) = wsBD.Range("G" & Lig).Value
.List(.ListCount - 1, 7) = wsBD.Range("H" & Lig).Value
.List(.ListCount - 1, 8) = wsBD.Range("I" & Lig).Value
.List(.ListCount - 1, 9) = Format(wsBD.Range("J" & Lig), "#,##0.00")
End With
LigList = LigList + 1
End If
Next Lig
CALCL
End Sub |
Partager