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
| Private Sub CB_CResume_Click()
Dim dmin As Date
Dim dmax As Date
Dim typ As String 'variable de controle de contenu de cell
Dim lr2 As Integer ' derniere ligne remplie de la feuille destinataire
Dim y As Integer 'enumerateur ligne feuille destinataire
Dim i As Integer 'enumerateur ligne feuille source
Dim ldbr As Integer ' derniere ligne remplie feuille source
Dim a As String ' nom feuille source pour eviter faute de frappe ^^ lol
Dim b As String 'nom feuille destinataire pour eviter faute de frappe ^^ lol
Dim typ2 As String 'variable de controle de contenu de cell
b = "Resume"
a = "BDR"
dmin = Me.CB_J1.Text & "/" & Me.CB_M1.Text & "/" & Me.CB_A1.Text
dmax = Me.CB_J2.Text & "/" & Me.CB_M2.Text & "/" & Me.CB_A2.Text
ldbr = Worksheets(a).Range("A1").CurrentRegion.Rows.Count
lr2 = 2 ' pour etre sur de lancer le deuxieme while
i = 1
While i <= ldbr + 1
y = 1
typ = Worksheets(a).Cells(i, 4).Text
If Worksheets(a).Cells(i, 1).Value <= dmax And Worksheets(a).Cells(i, 1).Value >= dmin Then
While y < lr2 + 1
lr2 = Worksheets(b).Range("A1").CurrentRegion.Rows.Count
typ2 = Worksheets(b).Cells(y, 1).Text
If Worksheets(b).Cells(y, 1).Text = typ Then
Worksheets(b).Rows(y + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Worksheets(b).Cells(y + 1, 1).Value = Worksheets(a).Cells(i, 1).Value
Worksheets(b).Cells(y + 1, 2).Value = Worksheets(a).Cells(i, 2).Value
Worksheets(b).Cells(y + 1, 2).Interior.ColorIndex = Worksheets(a).Cells(i, 2).Interior.ColorIndex
Worksheets(b).Cells(y + 1, 2).Font.ColorIndex = Worksheets(a).Cells(i, 2).Font.ColorIndex
Worksheets(b).Cells(y + 1, 3).Value = Worksheets(a).Cells(i, 3).Value
y = lr2 + 1
End If
y = y + 1
Wend
End If
i = i + 1
Wend
End Sub |
Partager