Macro différence d'heure, somme selon critére VBA

Question

Je souhaite effectuer la macro suivante: Trier les données selon un criétére ( jusque la pas de soucis) Ensuite, je souhaite faire la différence entre 2 colonnes qui sont au format "28/03/2017 11:39:20" dans la colonne F. J'ai réussi à faire ça mais pas en VBA. Puis, je souhaite effectuer la somme des éléments de la colonne F qui ont toutes une cellule E identique. Reformulé autrement, en parcourant la colonne E, j'ajoute la durée de la colonne F à un compteur de temps qui est différent pour chaque element différent de la colonne E. J'espère que c'est assez claire. Je vous joins le fichier Merci de votre aide CLR...

i20100 · Answer

Sub Temps() Dim plage As Range, derligB, Tcol_F derlfin = Worksheets("Sheet1").UsedRange.SpecialCells(xlCellTypeLastCell).Row 'Cells(1, 4) = derlfin 'Fige l'affichage excel Application.ScreenUpdating = False 'redimmensionne le Tableau au nombre de ligne colonne B -1 (titre) ReDim Tcol_F(derlfin - 1) 'Defintion en memoire de la plage de recherche Set plage = Range("A2:A" &amp; derlfin) 'Raz index du tableau indexT = 0 'Boucle de recherche: pour chaque objet dans la plage For Each cel In plage With Columns("F:F") .ColumnWidth = 10 .NumberFormat = "&#91;h&#93;:mm" End With 'calcul temps: ecriture dans une "case" du tableau Tcol_F(indexT) = CDate(CDate(Range("B" &amp; cel.Row)) - CDate(Range("A" &amp; cel.Row))) 'Increment index tableau indexT = indexT + 1 Next cel 'Ecriture des cellules colonne E avec les valeurs inscritent dans le tabeau Range("F2:F" &amp; derlfin) = Application.Transpose(Tcol_F) 'Defige l'affichage excel Application.ScreenUpdating = True End Sub...

MFerrand · Answer

Je suppose qu'il s'agit de ta macro intitulée FiltreColonne ! D'abord, tu procèdes à une épuration (élimination d'éléments) selon deux critères... Je crois qu'il est bon d'avoir des idées claires sur ce que l'on fait ou pas ! Un tri consiste à ordonner tous les éléments d'une plage selon 1 ou plusieurs critères (dans une ou plusieurs colonnes) dans un ordre croissant ou décroissant. On ne supprime rien, on ne masque rien... Un filtrage consiste à masquer des lignes d'une plage de façon à ne laisser apparaître que les lignes répondant à un ou plusieurs critères (dans une ou plusieurs colonnes). On ne supprime rien... Une épuration consiste par contre à éliminer des éléments de la plage concernée... On peut ajouter qu'une extraction consiste à prélever des éléments pour les reproduire ailleurs, sans pour autant les supprimer de la plage d'origine. Ces précisions étant faites, les questions que tu poses ne présentent pas de difficulté, ce qui en pose c'est que tu sembles dire que ta macro existante ne pose aucun souci ! Le nombre d'anomalies et de problèmes potentiels qu'elle présente en si peu de code implique que l'on ne peut passer allègrement dessus et aller à la suite, ce serait bâtir sur des sables mouvants ! La priorité est d'abord de réécrire ça intégralement et de façon cohérente. Et que tu prennes conscience de toutes les anomalies qu'elle recèle en son état actuel ! Cordialement...

MFerrand · Answer

Je n'ai pas bien saisi le sens de ton propos... ! En tout cas le mien était clair : tant que la macro FiltreColonne est présente dans ce classeur, impensable de s'occupeer d'autre chose avant de l'avoir écrite correctement en en éliminant les problèmes et anomalies...

Je n'ai d'ailleurs pas été plus loin et pas lu l'autre macro (ce qui ne m'a d'ailleurs pas empêché d'y voir une anomalie du même type qu'une de celles présentes dans la première macro)...

si je me place sur la cellule considéré, il m'affiche de nouveau la date.

Pas la date, une date !

Si cela t'étonne, il faut te documenter sur ce qu'est une date dans Excel !

une somme selon le nombre d'élément différent de la colonne E ? Cela se traduirait par une formule

Si on souhaite une formule de calcul, il faut la mettre manuellement ! On ne mobilise pas VBA pour cela, car il est plus rapide de mettre une formule manuellement et de l'étendre sur la colonne que de faire l'identique en VBA et quand la formule y est, elle y reste.

Si on utilise VBA, c'est pour ne pas mettre de formule, et faire le calcul indépendamment...

MFerrand · Answer

OK ! En préalable, je ne saurais trop insister une fois de plus sur l'indentation du code, indispensable pour travailler correctement dessus... Je commence donc par reproduire ta procédure sans aucun changement mais indentée ! Sub FiltreColonne() Dim i As Integer Dim derl As Integer Dim j As Integer Dim plage As Range, derligB, Tcol_F derldebut = Worksheets("Sheet1").UsedRange.SpecialCells(xlCellTypeLastCell).Row For j = 1 To derldebut If Cells(j, 5) = "" Then Cells(j, 5).EntireRow.Delete End If Next j For i = 4 To derldebut If Cells(i, 4) = "?" Then Cells(i, 4).EntireRow.Delete End If Next i derlfin = Worksheets("Sheet1").UsedRange.SpecialCells(xlCellTypeLastCell).Row Application.ScreenUpdating = False ReDim Tcol_F(derlfin - 1) Set plage = Range("A2:A" &amp; derlfin) indexT = 0 For Each cel In plage With Columns("F:F") .ColumnWidth = 10 .NumberFormat = "&#91;h&#93;:mm" End With Tcol_F(indexT) = CDate(CDate(Range("B" &amp; cel.Row)) - CDate(Range("A" &amp; cel.Row))) indexT = indexT + 1 Next cel Range("F2:F" &amp; derlfin) = Application.Transpose(Tcol_F) Application.ScreenUpdating = True End Sub J'ai aussi éliminé les lignes commentaires... On voir tout de suite mieux et directement la structure de la procédure... Les constats que l'on peut faire : 1) Variables : 4 variables utilisées ne sont pas déclarées : derldebut, derlfin, index_T, cel. 2 variables déclarées ne sont pas utilisées : derl, derlligB. L'utilisation de 2 variables i et j pour jouer le même rôle successivement n'est pas justifié. Il était rationnel d'utiliser la même variable. Même chose en ce qui concerne derldebut et derlfin, même utilisation, pas en même temps, une variable suffisait ! On note aussi que TcolF, déclaré comme Variant est redimensionné en tant que tableau, n'ayant pas d'autre utilisation justifiant un Variant, il convenait de le déclarer en temps que tableau... Notons que le dimensionnement pouvait être plus efficace, notamment une 2e dimension aurait évité d'avoir à le transposer à la fin (mais cela est secondaire). 2) Suppression de lignes : C'est le problème majeur ! Lorsqu'on supprime des lignes individuellement, on modifie simultanément le rang des lignes qui suivent, aussi en supprimant dans l'ordre croissant on modifie les rangs des lignes non encore testées. Il s'ensuit que certaines ne seront pas testées et que la procédure ne sera pas fiable. Lors de suppressions, on procède toujours dans l'ordre décroissant, de façon à assurer que tous les éléments parcourus soient testés. En outre, pour la 1re boucle de supppression, on part de la ligne 1, ligne d'en-tête ! Il était logique de ne pas y toucher... Plus curieux, pour la 2e on part de la ligne 4 ! On ne voit pas pourquoi on exclut les lignes 2 et 3 qui n'ont rien de différent en nature. Et pourquoi n'a-t-on pas fait le test dans la même boucle ? 3) Autres éléments mineurs dans le contexte : Expression commençant par Range ou Cells ou Columns ne référant pas à la feuille à laquelle elles appartiennent (soit expressions non qualifiées), alors qu'il était loisible de mettre la feuille sous bloc With, améliorant sensiblement le code. Le seul With qu'on trouve c'est affublé d'une Columns("F:F") &#91;NB- Columns("F") suffisait dans ce cas !&#93;, et inclus dans une boucle qui fait que l'on répète l'affectation With à chaque tour de boucle, alors que l'on n'intervient que sur cette colonne... Pas très optimisé, et il était judicieux de procéder autrement. Rétablir à True le rafraîchissement de l'affichage en fin de procédure ne sert à rien, Excel va le faire automatiquement. Cela n'est utile que si on doit le faire en cours de procédure. Par contre la mise à False qui précède ne l'a été qu'après les suppressions de lignes ! Il était plus logique de mettre cette commande avant ! J'espère ne rien oublier ! A force, on finit par ne plus tout voir... Reste plus qu'à écrire de façon plus cohérente ...

MFerrand · Answer

Voilà : Sub FiltreColonne() Dim kF(), n%, i% Application.ScreenUpdating = False With Worksheets("Sheet1") n = .UsedRange.SpecialCells(xlCellTypeLastCell).Row For i = n To 2 Step -1 If .Cells(i, 5) = "" Or .Cells(i, 4) = "?" Then .Rows(i).Delete Next i n = .Cells(.Rows.Count, 5).End(xlUp).Row ReDim kF(2 To n, 0) For i = 2 To n kF(i, 0) = .Cells(i, 2).Value2 - .Cells(i, 1).Value2 Next i With .Range("F2:F" &amp; n) .Value = kF .NumberFormat = "&#91;h&#93;:mm" End With End With End Sub Tu noteras que la rationnalisation s'est accompagnée d'un certain amaigrissement... Particularités non apparues dans la liste précédente : D'abord le nombre de variables s'est réduit à 3 (pas besoin de plus en effet !) J'ai modifié des noms de variables... mais ça c'est perso. j'ai une préférence pour les noms courts, et au-delà de 3 caractères je trouve que ça commence à être long ! Mais on peut ne pas partager si on aime passer du temps à taper des noms... Je conserve l'utilisation UserRange et xlCellTypeLastCell pour la première évaluation, car s'il y a des trous dans ton fichier de départ, le test sur une colonne risque de ne pas se révéler adéquat. Mais à la 2e évaluation, je teste sur la colonne E, dont on est assurée qu'elle servie, après suppressions de lignes, alors que le même test aurait donné le même résultat (les suppressions de lignes ne font pas diminuer UsedRange...) On peut se passer de EntireRow en ciblant directement la ligne... On parcourt les lignes pour la 2e opération, sans s'encombrer d'autres éléments : on n'a que les différences de temps à recueillir. Le redimensionnement du tableau est fait sur les lignes de la plage, 2 à n, ce qui permet adéquation des indices avec les lignes : i s'applique aux deux sans variable intermédiaire ni même correctif... ColumnWidth ? Je n'ai pas vu pourquoi il faudrait élargir la colonne... ? Et encore moins pourquoi le faire plus d'un millier de fois !! Pareil pour NumberFormat, on peut le faire une fois pour la colonne, pas la peine de le faire à chaque cellule !!! Plus intéressant, on se passe de convertir les dates en dates pour faire la différence des temps, en utilisant Value2 qui les renvoie sous forme de nombres (numéros de série). L'affectation s'opère sans transposer (2e dimension du tableau...) et la mise au format se fait au moment de l'affectation... Cordialement...