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
| Sub extraregroupe()
Dim zonestatut As Range
Set zonestatut = Range("b7:b13")
Dim cell As Range
manpower = "MPOWER"
Dim zonecodehor As Range
Set zonecodehor = Range("I7:I10")
Dim planning As Range
Set planning = Range("d7:f10")
Dim jourdumois As Range
Set jourdumois = Range("d5:f5")
Dim lstperso As Range
Set lstperso = Range("a7:a13")
Dim debutTableau As Range
Set debutTableau = Range("a19")
Dim i As Integer
' valeur de i = le num de la colonne du debut planning en partant de 0
i = 3
'valeur de j = num de la derniere colonne du planning (+i)
j = i + 2
'k est une valeur pour que dans le code le i soit constant...
k = i
'efface le tableau avant de le creer
debutTableau.Select
debutTableau.CurrentRegion.Clear
'regroupe les noms des extras
debutTableau.Offset(1, 0).Select
For Each cell In zonestatut
If cell = manpower Then
ActiveCell = cell.Offset(0, -1)
ActiveCell.Offset(1, 0).Select
End If
Next
Dim zonemapower As Range
Set zonemapower = debutTableau.Offset(1, 0).CurrentRegion
'tableau des jours du mois
debutTableau.Offset(0, 1).Select
For Each cell In jourdumois
ActiveCell = cell.Offset(0, 0)
ActiveCell.Offset(0, 1).Select
Next
'regroupe les codes horaires sur les noms et les jours
debutTableau.Offset(1, 1).Select
Dim cellule As Range
For Each cellule In zonemapower
For Each cell In lstperso
If cell = cellule Then
For i = k To j
ActiveCell = cell.Offset(0, i).Value
ActiveCell.Offset(0, 1).Select
Next i
ActiveCell.Offset(1, -3).Select
End If
Next cell
Next cellule
End Sub |
Partager