Fusionner cellules VBA sous la fonction IF

Version imprimable

1 pièce(s) jointe(s)

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:

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!

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:

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

Regarde si ça te convient :

Code:

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é.

17/08/2014, 03h44
awa123

1 pièce(s) jointe(s)

Merci Hervé !

Citation:

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:

=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:

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

à tester sur mon fichier joint aussi pour le constater

MERCI BEAUCOUP!!

Bonjour,

Citation:

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:

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

Donc pour l'intégration dans le code :

Code:

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é !

Citation:

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

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

Code:

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

En colonne B:

Code:

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é.

17/08/2014, 14h36
awa123
C'est génial!

le code devient magique!

pouvez vous juste m'expliquer la fonction suivante :
Code:

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!
17/08/2014, 16h02
Theze
Re,

Citation:

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)

Citation:

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

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

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:

1 2 3 4 5 6 7 Sub Test() MsgBox Range("A1").Text MsgBox Range("A1").Value2 End Sub
Hervé.
Hervé.
17/08/2014, 22h28
awa123

EXCELLENT!! merci