Problème dans une formule

**fabien70190** · 15/09/2016, 10h18

Bonjour à tous,

Voila je suis en train de réaliser un planning de production avec diverses informations, que je rentre manuellement dans excel (nombres de pièces à produire, temps de productions par pièces, date et heure de commencement).
Je ne connais pas grand chose en VBA, mais je pense pas être très loin de mon objectif. Je précise que j'ai réalisé le tableau, avec une personne qui si connait mieux que moi en VBA, mais qui m'a abandonné...

Mon but : En fonction des données rentré manuellement, j'aimerais que mon tableau me calcul la date et heure de fin, sachant que l'entreprise travail 2 x 8 (39h / semaine).
Mon problème : Sur la première ligne mon calcul se fait bien, par contre sur la deuxième ligne le calcul donne un résultat faux.

Je sais qu'il ne faut pas mettre de fichier sur le forum, donc je joint le code ci dessous, mais je pense pas que ça soit très compréhensible donc dans le doute je mets aussi le fichier.

Le code qui pose problème :

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
'Ecriture du résultat
            Range("I" & ln) = Cells(4, col - 1).Value + (5 + Cells(7, col - 1).Value) / 24
 
            For k = 1 To 5
                g = Choose(k, 5, 22, 39, 56, 73) 'numéro de colonne de la feuille gantt de E (5) à BU (73)
                If Int(fG.Cells(1, g)) = Int(Range("H" & ln)) Then
                    colD = g + Hour(Range("H" & ln)) - 5
                End If
 
                If Int(fG.Cells(1, g)) = Int(Range("I" & ln)) Then
                    colF = g + Hour(Range("I" & ln)) - 5
                End If
            Next k
            fG.Range(fG.Cells(ln, colD), fG.Cells(ln, colF)).Interior.Color = RGB(0, 0, 255)
 
        End If
suivant:
    Next ln
 
    Application.EnableEvents = True
End Sub

Le code au complet :

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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
Option Explicit
 
Dim derLn, ln, j, col, duree, hdeb, hr
Dim An, u_And, z, dateJ, tDate
Dim n, a, b, c, d, e, r, rv
Dim fG, g, k, cln, colD, colF
 
Sub DateDeFin()
 
    'Call JoursFermeture
    Application.EnableEvents = False
    derLn = Range("H" & Rows.Count).End(xlUp).Row
    Range("I5:I" & derLn).ClearContents
    Set fG = Sheets("Gantt")
 
    'initialisation de la feuille Gantt
    fG.Range("E5:CI13,E16:CI24,E27:CI35,E38:CI46,E49:CI57").Interior.Color = xlNone
    fG.Range("A4:CI4,E15:CI15,E26:CI26,E37:CI37,E48:CI48").Interior.Color = RGB(165, 165, 165)
 
    For ln = 5 To derLn
        'On initialise (efface) la ligne 7 du tableau de travail
        Range("L7:Y7").ClearContents
 
        'col = 12    'colonne du premier jour de la semaine
        If IsDate(Range("H" & ln).Value) And Range("H" & ln).Value <> "" And Range("F" & ln).Value <> "" Then
            'On recherche dans le tableau annexe la date de début
            For col = 12 To 25
                If Cells(4, col).Value = Int(Range("H" & ln)) Then
                    Exit For
                End If
            Next col
 
            'On passe toutes les colonnes du tableau de travail jusqu'à épuisement des
            'heures à reporter
            r = Range("F" & ln)   'durée à reporter
            Do While r > 0
                If Cells(7, col).Value + r + 5 < Cells(6, col).Value Then
                    Cells(7, col).Value = Cells(7, col).Value + r
                    'Ecriture du résultat
                    Range("I" & ln) = Cells(4, col).Value + (5 + Cells(7, col).Value) / 24
 
                    'Gantt
                    For k = 1 To 5
                        g = Choose(k, 5, 22, 39, 56, 73) 'numéro de colonne dans tableau gantt (5, 22, 39, 56, 73)
                        If Int(fG.Cells(1, g)) = Int(Range("H" & ln)) Then
                            colD = g + Hour(Range("H" & ln)) - 5 'début de la couleur bleu dans gantt
                        End If
 
                        If Int(fG.Cells(1, g)) = Int(Range("I" & ln)) Then
                            colF = g + Hour(Range("I" & ln)) - 5 'fin de la couleur bleu dans gantt
                        End If
                    Next k
                    fG.Range(fG.Cells(ln, colD), fG.Cells(ln, colF)).Interior.Color = RGB(0, 0, 255)
 
 
 
                    GoTo suivant
                Else
                    r = r + Cells(7, col).Value
                    Cells(7, col).Value = Application.Min(Cells(6, col) - 5, r)
                    r = r - Cells(7, col)
                End If
 
                col = col + 1
                Call VerifJF    'il faut regarder si la nouvelle colonne est un jour férié
 
                If col > 25 Then    '25 parce qu'on a limité le tableau à la colonne 25
                    Range("I" & ln).Value = "Après le " & Int(Range("H" & ln))
 
 
                    fG.Range(fG.Cells(ln - 1, colD), fG.Cells(ln - 1, 87)).Interior.Color = RGB(0, 0, 255)
 
 
 
 
                    GoTo suivant
                End If
            Loop
            'Ecriture du résultat
            Range("I" & ln) = Cells(4, col - 1).Value + (5 + Cells(7, col - 1).Value) / 24
 
            For k = 1 To 5
                g = Choose(k, 5, 22, 39, 56, 73) 'numéro de colonne de la feuille gantt de E (5) à BU (73)
                If Int(fG.Cells(1, g)) = Int(Range("H" & ln)) Then
                    colD = g + Hour(Range("H" & ln)) - 5
                End If
 
                If Int(fG.Cells(1, g)) = Int(Range("I" & ln)) Then
                    colF = g + Hour(Range("I" & ln)) - 5
                End If
            Next k
            fG.Range(fG.Cells(ln, colD), fG.Cells(ln, colF)).Interior.Color = RGB(0, 0, 255)
 
        End If
suivant:
    Next ln
 
    Application.EnableEvents = True
End Sub
 
Sub VerifJF()
    'On vérifie que le jour de la ligne (ln) étudiée n'est pas férié
    While Cells(6, col).Value = 0 And col <= 25
        col = col + 1 'on passe à la colonne suivante si le jour est férié
    Wend
End Sub
 
Sub JoursFermeture()
 
    An = Cells(1, 5).Value
    Application.Goto Reference:="Jours_de_fermeture"
    u_And = Year(Selection.Offset(1, 0).Value)
    If An <> u_And Then 'Mise à jour des dates de fermeture dans le tableau des données
            Selection.Offset(1, 0).Value = Pâques(An)
            'For z = 5 To 30 'Lignes des congés été, hiver et jours feriés
                'dateJ = Selection.Offset(z, 0).Value
                'Selection.Offset(z, 0).Value = Day(dateJ) & "/" & Month(dateJ) & "/" & Year(Date)
            'Next z
    End If
End Sub
 
Function Pâques(An)
 
    n = An - 1900
    a = n Mod 19
    b = Int((7 * a + 1) / 19)
    c = (11 * a - b + 4) Mod 29
    d = Int(n / 4)
    e = (n - c + d + 31) Mod 7
    If (25 - c - e) > 0 Then
        Pâques = DateSerial(An, 4, 25 - c - e)
    Else
        Pâques = DateSerial(An, 3, 56 - c - e)
    End If
End Function
 
 
 
 
 
Sub Evenement()
    Application.EnableEvents = True
End Sub

Merci de votre aide précieuse.

**unparia** · 15/09/2016, 10h53

Bonjour
Et elle est où, dans ce code (je n'ouvre aucun classeur tiers) , l'insertion de ta formule ?

**fabien70190** · 15/09/2016, 10h55

Voici la formule qui pose problème

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
'Ecriture du résultat
            Range("I" & ln) = Cells(4, col - 1).Value + (5 + Cells(7, col - 1).Value) / 24
 
            For k = 1 To 5
                g = Choose(k, 5, 22, 39, 56, 73) 'numéro de colonne de la feuille gantt de E (5) à BU (73)
                If Int(fG.Cells(1, g)) = Int(Range("H" & ln)) Then
                    colD = g + Hour(Range("H" & ln)) - 5
                End If
 
                If Int(fG.Cells(1, g)) = Int(Range("I" & ln)) Then
                    colF = g + Hour(Range("I" & ln)) - 5
                End If
            Next k
            fG.Range(fG.Cells(ln, colD), fG.Cells(ln, colF)).Interior.Color = RGB(0, 0, 255)
 
        End If
suivant:
    Next ln
 
    Application.EnableEvents = True
End Sub

**unparia** · 15/09/2016, 10h59

Désolé, mais je ne vois toujours pas la moindre formule.

**fabien70190** · 19/09/2016, 09h16

En fait, j'ai des formules de calcul directement sur excel pour convertir mes informations rentré en manuellement en heures. Un tableau annexe qui calcul en VBA en fonction du jour le temps travaillé (du lundi au jeudi inclus -> 8h de travail, le vendredi 7h -> de travail le samedi, dimanche et jours férié -> 0h de travail.

Donc si je me trompe pas, le code suivant :

Code :

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

Range("I" & ln) = Cells(4, col - 1).Value + (5 + Cells(7, col - 1).Value) / 24

calcul la date de fin en fonction de Cells(4, col - 1).Value jour de la semaine dans mon tableau annexe, Cells(7, col - 1).Value nombre d'heures travaillé en fonction du jour (toujours dans mon tableau annexe).
Je pense que l'erreur vient de cette formule, mais je ne vois pas ou ??

Quelqu'un aurrait il une idée ?

Merci

**Marc-L** · 19/09/2016, 10h02

Bonjour,

quelle est donc la formule fonctionnant dans une cellule de la feuille de calculs ?

_________________________________________________________________________________________________________

Je suis Paris, Nice, Bruxelles, Charlie, …

**Philippe Tulliez** · 19/09/2016, 11h14

Bonjour,

Je ne connais pas grand chose en VBA, mais je pense pas être très loin de mon objectif. Je précise que j'ai réalisé le tableau, avec une personne qui si connait mieux que moi en VBA, mais qui m'a abandonné...

Si comme tu l'écris, tu ne maîtrises pas le VBA, pourquoi ne pas utiliser les fonctions natives d'excel qui permettent de calculer la date et l'heure de fin d'un projet en fonction d'une date de départ, du nombre d'heures nécessaires à l'élaboration de ce projet des heures travaillées par journées et l'indication des jours fériés.
Il me semble que dans ton cas, il sera plus profitable et plus rapide d'apprendre les fonctions d'excel que de te mettre à apprendre le langage VBA, ses fonctions, méthodes, outils de débogage etc.

**fabien70190** · 19/09/2016, 11h34

Voici une photo, pour mieux comprendre le tableau sans avoir à l'ouvrir, j'ai mis les formules par dessus.

Merci Philippe, mais je ne pense pas que cela soit possible avec les formules d'excel.
Il faut gérer les jours fériés + congès, j'ai des jours de la semaine ou l'atelier tourne 16h et le vendredi 14h, du coup j'ai des journée qui se termine soit à 21h ou à 19h...

Mon tableau est bien avançé en VBA, je pense bientot toucher le but et je ne veut pas tout recommancé.

**Philippe Tulliez** · 19/09/2016, 11h55

Bonjour,

Merci Philippe, mais je ne pense pas que cela soit possible avec les formules d'excel.

Tu serais bien étonné de ce que l'on peut réaliser avec Excel avant de passer par du VBA
Voir cette discussion sur le même sujet http://www.developpez.net/forums/d14...l-date-finale/

**fabien70190** · 21/09/2016, 07h39

C'est impressionnant Philippe, je ne pensais pas qu'il était possible de faire tout ça sans VBA.
Par contre pense tu qu'il est possible d'adapter la formule ? Car l'entreprise est ouverte dans mon cas, de 5h à 21h du lundi au jeudi et de 5h à 19h le vendredi.

**Philippe Tulliez** · 22/09/2016, 16h52

Bonjour,

Par contre pense tu qu'il est possible d'adapter la formule ? Car l'entreprise est ouverte dans mon cas, de 5h à 21h du lundi au jeudi et de 5h à 19h le vendredi.

Il suffirait de compter le nombre de jeudi entre la date de début de projet te la fin de projet et ensuite de la multiplier par les heures prestées ces jours là.
Une des formules pour compter le nombre de mardi entre deux dates

Code :

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

=ENT((DateFin-(7 * ENT(DateDébut/7) + 3))/7+1)

La constante 3 est utilisée pour compter les mardis (1 pour dimanche, 2 pour lundi, 3 pour mardi, etc.)

**fabien70190** · 29/09/2016, 09h32

Merci Philippe pour toutes les info.
Avant de devoir tout refaire est ce que tu vois ce qui ne va pas dans le VBA ?

**fabien70190** · 04/10/2016, 10h13

J'ai trouvé ce qui ne va pas dans le calcul,

Code :

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

Range("I" & ln) = Cells(4, col).Value + (5 + Cells(7, col).Value) / 24

Le chiffre 5 est la pour commençer la formule à 5h du matin (heure d'ouverture de l'entreprise) et du coup la formule ne tiens pas compte de la colonne H.
Comment faire pour intégrer dans la formule la date de début qui est dans la colonne H ?

Un petit coup de main s'il vous plait

**Philippe Tulliez** · 04/10/2016, 11h21

Bonjour,
Une solution parmi d'autres
Tu crées une boucle dont la borne inférieure est la date de début et la borne supérieure la date de fin.
Dans cette boucle, tu effectues un test avec Select Case par exemple où tu testes le jour de la semaine ce qui te permettra de calculer le nombre d'heures exact travaillé par jour en l'additionnant au résultat obtenu à chaque passage de la boucle.
Il y a lieu de rectifier l'heure de début et de fin afin d'obtenir le résultat exact.
Si tu souhaites tenir compte des jours fériés (ce qui me semble indispensable, il faudra créer une liste contenant les jours fériés et utiliser la fonction MATCH pour vérifier si le jour traité dans la boucle est repris dans cette liste.

[EDIT]
Pour te mettre sur la piste, voici un exemple d'une procédure SUB qui calcule le nombre d'heures prestées entre les dates comprises entre A2 et B2 de la feuille nommée Feuil1

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
Sub CalculNombreHeure()
  Dim Jour As Long, SommeHeure As Double, HeureTravaillee As Integer
  Dim fn As WorksheetFunction
  Set fn = Application.WorksheetFunction
  With ThisWorkbook.Worksheets("Feuil1")
    For Jour = .Range("A2").Value To .Range("B2").Value
        Select Case fn.Weekday(Jour, 2)
          Case 1 To 4: HeureTravaillee = 8 ' Lundi au jeudi
          Case 5: HeureTravaillee = 6      ' Vendredi
          Case Else: HeureTravaillee = 0
        End Select
      SommeHeure = SommeHeure + (HeureTravaillee / 24)
    Next
    With .Range("D2")
    .Value = SommeHeure
    .NumberFormat = "[hh]:mm"
    End With
  End With
  Set fn = Nothing
End Sub

Cet exemple ne tient pas compte des jours fériés, ni des heures de début et fin de projet.
Idéalement, je créerais une fonction afin de pouvoir l'utiliser comme fonction personnalisée dans Excel.
Pour rendre la fonction pérenne, je placerais également le nombre d'heures prestées par jour dans une feuille contenant les paramètres avec notamment la liste des fériés

**fabien70190** · 18/10/2016, 11h01

Il n'est pas plutot possible de garder la formule telle qu'elle, et d'y ajouter une variable à la place du chiffre 5 ? (colonne H5 si calcul en I5, colonne H6 si calcul en I6...)
Je ne sais pas trop comment formuler la chose en VBA, mais je suppose que cela est possible ?

**fabien70190** · 24/01/2017, 08h45

Quelqu'un aurait il une idée ?

**Philippe Tulliez** · 24/01/2017, 10h06

Bonjour,

Quelqu'un aurait il une idée ?

Des idées sûrement, des propositions tu en as reçues mais manifestement tu les ignores.

**fabien70190** · 24/01/2017, 16h40

Non je ne les ignores pas, mais j'aimerais savoir si il est possible d'intéger une variable à ma formule, avant de devoir tout modifier...
Je ne suis pas expert en VBA et j'aimerais ne pas avoir à tout modifer.

Merci de ton aide.

**Philippe Tulliez** · 24/01/2017, 17h49

Bonjour,

j'aimerais savoir si il est possible d'intéger une variable à ma formule, avant de devoir tout modifier

Dans la formule que tu as publiée le 4/10/2016 (Post #13)

Code :

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

Range("I" & ln) = Cells(4, col).Value + (5 + Cells(7, col).Value) / 24

Comment expliques-tu que tu aies su remplacer deux constantes par les variables ln et col et que tu ne saches pas le faire pour la constante 5 ?
J'avoue ne pas très bien comprendre

**fabien70190** · 25/01/2017, 08h31

Comment expliques-tu que tu aies su remplacer deux constantes par les variables ln et col et que tu ne saches pas le faire pour la constante 5 ?
J'avoue ne pas très bien comprendre

Comme je l'ai expliqué dans mon premier post :

Je ne connais pas grand chose en VBA, mais je pense pas être très loin de mon objectif. Je précise que j'ai réalisé le tableau, avec une personne qui si connait mieux que moi en VBA, mais qui m'a abandonné...

C'est pour cela que je sollicite l'aide du forum, je connais excel avec les formules mais mes connaissances VBA reste basique.

Problème dans une formule

Macros et VBA Excel

Discussions similaires

Partager

Partager