Discussion :

[awa123]

Bonjour,

Je voudrais que mon code VBA me fusionne les cellules de la colonne 3 (colonne C) à la colonne 11 (colonne K) si et seulement si le code a trouvé que la cellule cells(19+i,1) = cells(16,7) à la ligne i (il bouclera).

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
For I = 0 To nb_ligne
 
            If Cells(19 + I, 1).Text = Cells(16, 7) Then
            Cells(19 + I, 3) = "repos"
            Cells(19 + I, 8) = ""
            Cells(19 + I, 10) = ""
 
                For j = 0 To 8
 
                    With Cells(19 + I, 3 + j).Interior
 
                        .ThemeColor = xlThemeColorDark1
                        .TintAndShade = -0.349986266670736
                    End With
 
                   ' Cells(19 + I, 3 + j).Select
 
                  '   .[C&I:G&I].Merge
 
                Next j
            End If
        Next I

voir mon fichier joint, ce sera plus clair je pense.

Merci infiniment!

[Theze]

Bonjour,

Dans mon code, j'utilise une fonction pour définir le nombre de jours dans le mois et quand février donne 28, il ne devrait pas apparaître d'autres jours en dessous enfin, c'est mon avis.
La formule de total ne donne pas le bon total car pour Excel, 1 heure = 1/24 alors, je pense que tu devrais utiliser cette formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
=SOMME(J19:J49)/(1/24)

Regarde si ça te convient :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
63
 
Sub test()
 
    Dim nb_ligne As Integer
    Dim I As Long
    Dim LaDate As Date
 
    With Worksheets("feuille d'heures mensuel 1")
 
         With .Range("C19:K49").Interior
 
            .ThemeColor = xlThemeColorDark1
 
        End With
 
        'effacer le contenu des cellules avant de cmmencer une nouvelle génération
        .Range("C19:K49").ClearContents
 
        'supprime les fusions pécédentes si existes...
        .Range("C19:K49").UnMerge
 
        'calcule le nombre de jours dans le mois
        'Attention, il faut revoir les formules car le max de jours toujours égal à 30 ???
        LaDate = CDate("01/" & Cells(16, 2) & "/" & Right(Cells(16, 3), 4))
        nb_ligne = Day(DateSerial(Year(LaDate), Month(LaDate) + 1, 1) - 1)
 
        'heure d'arrivée
        .Cells(19, 3) = .Cells(14, 7)
        .Cells(19, 3).AutoFill .Range(.Cells(19, 3), .Cells(nb_ligne + 18, 3)) 'étend à la plage...
 
        'heure de départ
        .Cells(19, 8) = .Cells(15, 7)
        .Cells(19, 8).AutoFill .Range(.Cells(19, 8), .Cells(nb_ligne + 18, 8)) 'étend à la plage...
 
        'delta
        .Cells(19, 10).Formula = "=" & .Cells(19, 8).Address(0, 0) & "-" & .Cells(19, 3).Address(0, 0)
        .Cells(19, 10).AutoFill .Range(.Cells(19, 10), .Cells(nb_ligne + 18, 10)) 'étend à la plage...
 
        ' Jours de repos
        For I = 0 To nb_ligne
 
            If .Cells(19 + I, 1).Text = .Cells(16, 7) Then
 
                .Cells(19 + I, 3) = "repos"
                .Cells(19 + I, 8) = ""
                .Cells(19 + I, 10) = ""
 
                Range(.Cells(19 + I, 3), .Cells(19 + I, 11)).Merge
 
                With .Range(.Cells(19 + I, 3), .Cells(19 + I, 11)).Interior
 
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = -0.349986266670736
 
                End With
 
            End If
 
        Next I
 
    End With
 
End Sub

Hervé.

[awa123]

Merci Hervé !

Dans mon code, j'utilise une fonction pour définir le nombre de jours dans le mois et quand février donne 28, il ne devrait pas apparaître d'autres jours en dessous enfin, c'est mon avis.

oui vous avez tout a fait raison, la formule que j'utilise pour définir le nombre de jours n'est pas fiable, surtout si on fait face au mois de février. Quel fonction pourrai-je utiliser pour corriger cela?


Au niveau du calcul de la différence d'heure entre heure où l'employé est venu au travail et l'heure à laquelle il a quitté le boulot fonctionne parfaitement avec la formule suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=GAUCHE($H21;5)-GAUCHE($C21;5)+SI(GAUCHE($C21;5)>GAUCHE($H21;5);1)

cette formule me permet d'effectuer une différence d'heure correcte. Par exemple si l'heure de d'arrivée est 23:00 et l'heure de départ est 02:00, ça ne me fait pas 2-23=-21 (qui est totalement faux) mais le bon calcul.
Cependant il y a quelque chose qui m'embête : si l'heure d'arrivée est 23:00 et l'heure de départ est 04:00, le résultat sera "04:59", ce qui est techniquement correcte, mais j'aurai souhaité avoir un chiffre rond, donc 05:00 dans cet exemple

je vous joins le fichier pour que vous le constatez vous même

la formule suivante n'a pas fonctionné chez moi

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMME(J19:J49)/(1/24)

à tester sur mon fichier joint aussi pour le constater

MERCI BEAUCOUP!!

[Theze]

Bonjour,

Au niveau du calcul de la différence d'heure entre heure où l'employé est venu au travail et l'heure à laquelle il a quitté le boulot fonctionne parfaitement avec la formule suivante :
Code :Sélectionner tout - Visualiser dans une fenêtre à part
=GAUCHE($H21;5)-GAUCHE($C21;5)+SI(GAUCHE($C21;5)>GAUCHE($H21;5);1)

Comme j'ai dis précédemment, pour Excel, le jour vaut 1 donc l'heure vaut 1/24 soit 0,4166667 donc on voit bien ici que c'est numérique, il n'est pas nécessaire d'utiliser des fonctions Texte.
Pour le calcul de différence d'heure, je te propose cette formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
=SI(C19>H19;1-C19+H19;H19-C19)

Donc pour l'intégration dans le code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
63
64
65
66
 
Sub test()
 
    Dim nb_ligne As Integer
    Dim I As Long
    Dim LaDate As Date
 
    With Worksheets("feuille d'heures mensuel 1")
 
         With .Range("C19:K49").Interior
 
            .ThemeColor = xlThemeColorDark1
 
        End With
 
        'effacer le contenu des cellules avant de cmmencer une nouvelle génération
        .Range("C19:K49").ClearContents
 
        'supprime les fusions pécédentes si existes...
        .Range("C19:K49").UnMerge
 
        'calcule le nombre de jours dans le mois
        'Attention, il faut revoir les formules car le max de jours toujours égal à 30 ???
        LaDate = CDate("01/" & Cells(16, 2) & "/" & Right(Cells(16, 3), 4))
        nb_ligne = Day(DateSerial(Year(LaDate), Month(LaDate) + 1, 1) - 1)
 
        'heure d'arrivée
        .Cells(19, 3) = .Cells(14, 7)
        .Cells(19, 3).AutoFill .Range(.Cells(19, 3), .Cells(nb_ligne + 18, 3)) 'étend à la plage...
 
        'heure de départ
        .Cells(19, 8) = .Cells(15, 7)
        .Cells(19, 8).AutoFill .Range(.Cells(19, 8), .Cells(nb_ligne + 18, 8)) 'étend à la plage...
 
        'delta
        .Cells(19, 10).Formula = "=IF(" & .Cells(19, 3).Address(0, 0) & ">" & .Cells(19, 8).Address(0, 0) & ",1-" _
                                 & .Cells(19, 3).Address(0, 0) & "+" & .Cells(19, 8).Address(0, 0) & "," _
                                 & .Cells(19, 8).Address(0, 0) & "-" & .Cells(19, 3).Address(0, 0) & ")"
 
        .Cells(19, 10).AutoFill .Range(.Cells(19, 10), .Cells(nb_ligne + 18, 10)) 'étend à la plage...
 
        ' Jours de repos
        For I = 0 To nb_ligne
 
            If .Cells(19 + I, 1).Text = .Cells(16, 7) Then
 
                .Cells(19 + I, 3) = "repos"
                .Cells(19 + I, 8) = ""
                .Cells(19 + I, 10) = ""
 
                Range(.Cells(19 + I, 3), .Cells(19 + I, 11)).Merge
 
                With .Range(.Cells(19 + I, 3), .Cells(19 + I, 11)).Interior
 
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = -0.349986266670736
 
                End With
 
            End If
 
        Next I
 
    End With
 
End Sub

Cette formule est pour la totalisation des heures dans ton premier fichier puisque le résultat était erroné !

=SOMME(J19:J49)/(1/24)

Pour les trois derniers jour du mois :
En colonne A à tirer sur les trois jours :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
=SI(B47<>"";DATE(STXT($C$16;8;4);MOIS(1&$B$16);B47);"")

En colonne B:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
=SI(JOUR(DATE(ANNEE(1 &B16 &STXT($C$16;8;4));MOIS(1 &B16 &STXT($C$16;8;4))+1;1)-1)>28;29;"")
=SI(JOUR(DATE(ANNEE(1 &B16 &STXT($C$16;8;4));MOIS(1 &B16 &STXT($C$16;8;4))+1;1)-1)>29;30;"")
=SI(JOUR(DATE(ANNEE(1 &B16 &STXT($C$16;8;4));MOIS(1 &B16 &STXT($C$16;8;4))+1;1)-1)>30;31;"")

Hervé.

[awa123]

C'est génial!

le code devient magique!

pouvez vous juste m'expliquer la fonction suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
=SI(JOUR(DATE(ANNEE(le numéro du mois & l'année en question);MOIS(num du mois & l'année )+1;1)-1)>28;29;"")

pourquoi mettre +1;1)-1) à la fin de ce code, j'ai pas compris

d'autre part pourquoi rentrer le numéro du mois en cours dans la fonction ANNEE(), seul l'année aurait suffit, non?
de même pourquoi rentrer l'année dans la fonction MOIS(), seul le numéro du mois en cours serait suffisant?


merci encore!

[Theze]

Re,

pourquoi rentrer le numéro du mois en cours dans la fonction ANNEE(), seul l'année aurait suffit, non?
de même pourquoi rentrer l'année dans la fonction MOIS(), seul le numéro du mois en cours serait suffisant?

Les fonctions ANNEE et MOIS attendent une numéro de série, aujourd'hui, le numéro de série est 41868, demain ça sera 41869 et ainsi de suite puisque le jour vaut 1. Les fonctions Excel sont assez puissantes pour interpréter les valeurs passées en argument, la preuve "ANNEE(1 &B16 &STXT($C$16;8;4))"' est une concaténation du jour (1), du mois (cellule C16) et de l'année (extraite de "Année: 2014" en cellule C16)

pourquoi mettre +1;1)-1) à la fin de ce code, j'ai pas compris

Admettons que le mois soit février, la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
=DATE(ANNEE(1 &B16 &STXT($C$16;8;4));MOIS(1 &B16 &STXT($C$16;8;4));1)

retourne 01/02/2014
année 2014, mois 2 (février), jour 1 donc, le premier du mois
Si à la fonction MOIS tu rajoute 1 (+1) la fonction DATE retourne alors 01/03/2014 logique non ? Et comme toi tu veux connaître le dernier jour du mois précédent, il suffit de retrancher 1 à la fonction DATE ce qui donne 28/02/2014 et la fonction JOUR va extraire le jour de cette date donc le 28 !
Il faut bien garder à l'esprit que pour Excel une date est un Long (le 01/01/1900 vaut 1 et le 17/08/2014 vaut 41868 soit 41868 jour depuis le 1er janvier 1900) et la représentation "28/02/2014" n'est rien d'autre qu'un formatage. Pour t'en convaicre, entre une date en A1 sous le format "20/02/2014" et lance cette proc :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
Sub Test()
 
    MsgBox Range("A1").Text
    MsgBox Range("A1").Value2
 
End Sub

Hervé.
Hervé.