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
| Public Sub Sinistre_Decl()
Dim Nombre_Sinistre_Declare As Integer
Dim Date_Souscription_Adhésion As Range
Dim Numéro_Police As Range
Dim Date_Survenance As Range
Dim Somme As Long
Dim nblignes As Integer
Dim i As Integer
Dim j As Integer
Worksheets("Feuil1").Activate
DernLigne1 = Sheets("Feuil1").Range("G" & Rows.Count).End(xlUp).Row 'définit la dernière ligne colonne A
DernLigne2 = Sheets("Feuil1").Range("R" & Rows.Count).End(xlUp).Row 'définit la dernière ligne colonne B
Set Date_Souscription_Adhésion = Sheets("Feuil1").Range("G2:G" & DernLigne1) 'définit la colonne A
Set Date_Survenance = Sheets("Feuil1").Range("R2:R" & DernLigne2) 'définit la colonne B
For i = 1 To Date_Souscription_Adhésion.Rows.Count 'boucle sur la colonne G
For j = 1 To Date_Survenance.Rows.Count
nblignes = WorksheetFunction.CountIfs(Range("G2:G37"), ">=" & DateSerial(4, 1, 2016), Range("G2:G37"), "<=" & DateSerial(4, 30, 2016), Range("R2:R37"), ">=" & DateSerial(9, 1, 2016), Range("R2:R37"), "<=" & DateSerial(9, 30, 2016))
Next j
Next i
MsgBox "nombre de ligne " & nblignes
End Sub |
Partager