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
| Option Compare Text
Sub Repartition_Listing()
Dim f1 As Worksheet, f2 As Worksheet
Dim DerLig_f1 As Long, DerCol_f1 As Long, Lig As Long, i As Long, C As Long
Dim Cell As Range
Application.ScreenUpdating = False
Set f1 = Sheets("Sondage")
Set f2 = Sheets("Listing")
DerLig_f1 = f1.[A100000].End(xlUp).Row
DerCol_f1 = f1.[XFD6].End(xlToLeft).Column
'Préparation feuille "Resultats"
f2.Cells.ClearContents 'on efface les précédents résultats
'Relevé des dates de la feuille ""
ReDim Pers(DerLig_f1, DerCol_f1) As String
ReDim Jour(DerLig_f1, DerCol_f1) As String
ReDim Horaire(DerLig_f1, DerCol_f1) As String
ReDim V_OK(DerLig_f1, DerCol_f1) As String
Nb_Cell = 1
For i = 7 To DerLig_f1
For J = 4 To DerCol_f1
If f1.Cells(i, J) = "OK" Or f1.Cells(i, J) = "(OK)" Then
V_OK(i, J) = "OK"
Pers(i, J) = f1.Cells(i, "A")
If J Mod 2 = 0 Then Jour(i, J) = f1.Cells(5, J) Else Jour(i, J) = f1.Cells(5, J - 1)
Horaire(i, J) = f1.Cells(6, J)
'Nb_Cell = Nb_Cell + 1
End If
Next J
Next i
'Remplissage listing
Lig = 2
For i = 7 To DerLig_f1
For J = 4 To DerCol_f1
If V_OK(i, J) = "OK" Then
f2.Cells(Lig, "A") = Pers(i, J)
f2.Cells(Lig, "B") = Jour(i, J)
f2.Cells(Lig, "C") = Horaire(i, J)
Lig = Lig + 1
End If
Next J
Next i
f2.Range("A1:C1") = Array("Personnel", "Jour", "Horaire")
f2.Select
Set f1 = Nothing
Set f2 = Nothing
Set Col = Nothing
End Sub |
Partager