> Tous les forums > Forum Bureautique
 Macro pour copie de données selon critèreSujet résolu
Ajouter un message à la discussion
Pages : [1] 2 ... Fin
Page 1 sur 2 [Fin]
Kassie
  Posté le 01/08/2018 @ 16:16 
Aller en bas de la page 
Petite astucienne




Bonjour à tous,


On me demande l'automatisation d'une tâche et je sèche. Je me débrouille quand même un peu en macro mais sur ce coup, je ne sais pas comment m'y prendre. Je me tourne donc vers vous dans l'espoir d'un coup de pouce svp.

Le fichier sur lequel je travaille contient des milliers de lignes. Je joins un exemple avec des données fictives. https://www.cjoint.com/c/HHbox7o2uLw

Si la colonne "U" (Avis) contient "F" ou (modification de projet, j'allège le processus) "X", je dois copier la ligne entière dans la feuille "Avis-CAS 2", à la suite de ce qu'il s'y trouve. Lors de la copie, je dois également ajouter la date du jour dans la colonne "T", (Date Avis envoyé) et enlever le X dans la colonne.

Je récupérerai les données de la feuille Avis-Cas 2 pour faire une fusion papier et courriel.


Windows 8.1
Chrome 67.0.3396.99

Office 2013 et 2016

Merci infiniment d'avance pour votre aide !



Modifié par Kassie le 01/08/2018 19:18
Publicité
ferrand
 Posté le 02/08/2018 à 06:21 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bonjour,

J'ai jeté un oeil sur ton fichier... A mon avis, il faudrait d'abord surtout y remettre de l'ordre, pour pouvoir le traiter correctement en base de données.

Pour le ToutCas ce n'est pas encore trop désordonné, peut-être parce allégé, à part le système de couleurs qu'il faudrait basculer en MFC. En tout cas, si tu veux récupérer les couleurs, cela interdit pratiquement de travailler avec des tableaux et oblige à faire du copier-coller, ce qui n'est pas la meilleure solution. Je conseillerais dans ce cas d'utiliser un filtre automatique, pour filtrer les X sur la colonne U et on pourrait alors copier les lignes visibles...

Mais pour le collage, ça se complique, un premier tableau va jusqu'à la ligne 3297, et comporte des totaux au-dessous, et plus bas on trouve 2 mini-tableaux qui n'ont pas l'air d'avoir la même structure. Le genre de truc à ne jamais faire ! On ne sait décemment pas où l'on peut coller quelque chose, sauf à le faire n'importe où...

Dans tous les cas, on ne pourra jamais faire un travail de qualité minimale. (A cet égard, je ne suis pas preneur .) Mais je te conseille volontiers de te pencher sur les règles d'or à appliquer pour construire et gérer une base de données dans Excel. Je suis loin d'être intégriste en la matière, mais certaines règles sont tout à fait incontournables si l'on veut travailler efficacement.

Cordialement.

Kassie
 Posté le 06/08/2018 à 15:03 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

Bonjour,

Merci Ferrand pour tes commentaires. Oh la boulette de ma part ! Mea culpa, Mea culpa, Mea maxima culpa ! J'avais "nettoyé" le Tout Cas, mais pas la feuille Avis Cas-2, que je croyais vide. J'ai été trop vite et j'ai manqué de rigueur. On m'a foutu ce fichier entre les pattes et je me suis dit que je le fignolerai plus tard... Erreur et je m'en excuse.

Le fichier à nettoyé et à travailler est celui-ci: https://www.cjoint.com/c/HHgm5ZLiGSw

La feuille Avis Cas-2 est vide si ce n'est l'entête de colonnes.

On peut travailler sans se soucier des couleurs. J'ai ajouté des colonnes à partir de quoi je ferai de la MFC.

Si la colonne "U" contient un X, alors j'ai besoin que la ligne en question soit copiée dans la feuille AVIS Cas-2. On enlève le X sur la ligne en question et on ajoute la date du jour dan sla colonne T (Date avis envoyé).

Est-ce quelque chose de possible ?

Merci beaucoup d'avance!

ferrand
 Posté le 08/08/2018 à 09:53 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bonjour,

Là c'est nettoyé !

Si je résume : feuille Tout-Cas, on supprime le "X" en U et on met date du jour en T... pas de problème pour ça.

Parallèlement, on reporte la ligne sur la feuille Avis : le "X" doit-il rester en U ou être ôté ? Et la date figurer en T ou non ?

Tel que ça se présente, ce sera une procédure à lancer, tu mets tes "X" et tu lances au moyen d'un bouton (ou raccourci clavier) et l'opération se fait. Toutes les lignes à "X" seront traitées en bloc...

Autre option : proc. automatique, déclenchée par la mise du "X". A ce moment-là le traitement se fait ligne par ligne au fil de tes inscriptions de "X".

Autre option éventuelle : faut-il prévoir une procédure de retour de Avis vers Tout-Cas ? (raisons diverses, voire erreur...)

Je rentre de clinique, mais je peux te faire ça dans la soirée, ce qui te laisse le temps de me confirmer tes options.

Cordialement.

Kassie
 Posté le 08/08/2018 à 14:16 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

Merci Ferrand pour ton retour et ton aide.

Oui, Feuille Tout-Cas, on supprime le "X" et on met la date du jour dans la colonne T.

Il serait bien que la ligne soit reportée avec la modification, donc avec la date du jour de la procédure et sans le X.

J'aimerais beaucoup mieux une procédure à lancer qu'une procédure automatique. Ça me donnera la date réelle de l'envoi d'avis, ce qui ne serait pas le cas avec un traitement en continue.

Pas de procédure de retour à prévoir, ce ne sera pas utile. En cas d'erreur, j'utiliserai une copie de back-up. Là dessus, la rigueur est appliquée ! :)

Bon retour et soigne toi bien et surtout, un immense merci Ferrand !

ferrand
 Posté le 08/08/2018 à 18:42 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

C'est noté ! @+ (suis à mi-repas ! )

ferrand
 Posté le 09/08/2018 à 00:39 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Re,

Voilà une procédure à tester (et vérifier qu'elle fait ce qui était demandé... )

Sub AvisEnvoyés()
Dim TC, AC(), i&, n&
TC = ActiveSheet.Range("A1").CurrentRegion
For i = 2 To UBound(TC)
If UCase(TC(i, 21)) = "X" Then
TC(i, 20) = CLng(Date)
TC(i, 21) = Empty
ReDim Preserve AC(n)
AC(n) = WorksheetFunction.Index(TC, i, 0)
n = n + 1
End If
Next i
With ActiveSheet.Range("A1").CurrentRegion
.Value = TC
.Columns(20).NumberFormat = "dd-mmm-yy"
End With
With Worksheets("Avis Cas-2")
i = .Range("A" & .Rows.Count).End(xlUp).Row + 1
With .Range("A" & i).Resize(n, UBound(TC, 2))
.Value = WorksheetFunction.Transpose(WorksheetFunction.Transpose(AC))
.Columns(20).NumberFormat = "dd-mmm-yy"
End With
End With
End Sub

Un peu difficile à lire ici car PCA ne propose pas de dispositif conservant l'indentation du code, mais tu pourras la lire plus facilement dans ton classeur (Module1).

La méthode utilisée mérite quelques explications :

- la 1re ligne (après Sub... qui déclare la procédure) déclare les variables qui seront utilisées dans la procédure :
TC est une variable de type Variant (non typée car c'est le type par défaut) à laquelle on affectera un tableau (constitué par l'ensemble des données de Tout CAS 2
AC() est une variable tableau dynamique, que l'on dimensionnera au fur et à mesure de l'exécution (également de type Variant, car tes données sont de divers types) qui nous servira à recueillir les données à transférer
i et n sont des variables de type Long (le caractère & indique ce type et équivaut à As Long, les caractères de déclarations de type existent pour les variables numériques et String, elles permettent de raccourcir les déclarations) ; le type Integer (nombres entiers jusqu'à 32767) suffisait pour la taille de ton tableau, mais ton tableau réel sera certainement plus long, et des tests (faits avec un autre intervenant sur un autre Forum) ont montré que dans tous les cas l'exécution avec une variable Long était plus rapide qu'avec une variable Integer...)

La 2e ligne initialise TC (j'ai placé le bouton de lancement sur la feuille Tout CAS 2, donc ActiveSheet représente bien cette feuille [active], si tu devais la lancer d'ailleurs il faudrait modifier au profit de Worksheets("NomDeFeuille")) : sans recherche particulière on prend la région courante à partir de A1, la ligne d'en-tête étant par définition servie, il suffit qu'une colonne soit remplie pour que toute la plage de données soit couverte, et elle est couerte même avec tes nombreux trous !, en fait il une ligne entière sans données pour la couper...
Avec cette méthode, on s'évite de chercher combien de lignes on a et de colonnes...

Avec la 3e ligne on initialise une boucle avec i comme compteur de boucle (qui se termine avec Next i) dont le code va se répéter autant de fois qu'il y a de ligne dans le tableau, de la ligne 2 à la fin. On travaille donc sur un tableau, entièrement hors Excel, ce qui est plus rapide. (Nota : et où qu'on prélève une plage sur une feuille pour en faire un tableau de cette façon, le tableau sera toujours de base 1, ce pourquoi on peut toujours le parcourir de 1 ou 2 (si en-tête) jusqu'à fin (UBound) pour les lignes, et même chose pour les colonnes.)
Dans la boucle, on commence par tester si TC(i, 21) (correspondant à la colonne U) est égal à "X" (on utilise UCase, fonction qui transforme en majuscules, par précaution pour le cas où on aurait tapé "x" au lieu de "X"... évidemment si tu distingguait "x" et "X" il faudrait modifier cela ! )
Si c'est le cas, on met la date en TC(i, 20) et on vide TC(i, 21).
Puis on incrémente le tableau résultat en le redimensionnant avec n, tout en préservant les valeurs déjà insérées dans le tableau (au démarrage n à la valeur 0, car déclarée numérique mais non initialisée, on dimensionne AC au départ sur un premier élément d'indice 0, et après affectation on incrémente n pour le tour suivant, à la fin de chaque tour n est supérieur de 1 à l'indice maximal du tableau, ce sera la cas toujours à la fin de la boucle et n représentera alors le nombre de lignes en cours de transfert, compensant ainsi le premier indice 0 du tableau).
Après chaque redimensionnement du tableau on affecte le nouvel élément n avec la ligne entière du tableau TC.

A ce stade, je dois faire 2 parenthèses : la 1re concerne l'affectation de la date au moyen de CLng(Date). Date est une fonction qui renvoie la date du jour, sans ambiguïté donnée de type Date, cependant on la convertit en entier Long correspondant à son numéro de série (façon dont sont stockée les dates dans Excel). Pourquoi cette conversion ?
VBA a la fâcheuse manie d'être particulièrement actif dès qu'il détecte une date et dès lors qu'il détecte un texte ayant un format de date il convertit, seulement quand on ne le force pas à convertir avec une fonction idoine qui utilisera les paramètres FR, il interprète la date selon son format natif (américain), et on se retrouve avec une inversion jour/mois. J'ai d'ailleurs attendu pour le premier test d'être au 9 car le 08/08 ne pouvait être significatif, et je me suis effectivement retrouvé avec le 9 août sur le tableau initial et le 8 septembre sur les lignes transférées ! Alors effectivement Date fournit une date mais pour qu'elle soit intelligible par Excel comme date elle lui donne un format par défaut FR (jj/mm/aaaa) qui permet comme lorsqu'on saisit directement une date dans une cellule à Excel de convertir la saisie en nombre et doter la cellule d'un format de date automatiquement. Ici le problème ne se posait pas avec le tableau TC lors de sa réaffectation à la feuille, mais je ne saurais dire si VBA n'a opéré aucune conversion sur ce tableau ou s'il en a opéré 2, la seconde annulant la première ; de même pour tableau résultat, comment savoir si son traitement particulier a conduit à 1 conversion ou 3 ! On ne peut se baser que sur le résultat final affecté à Excel pour constater qu'il y a au moins une conversion de trop... les détections intermédiaires ne seraient pas significatives du fait que pour en prendre connaissance il faudrait les afficher, avec conversion possible au cours de cette manoeuvre. La conversion en entier garantit l'impossibilité pour VBA de convertir (dans ce cas, on doit lors de l'affectation mettre les cellules concernées à un format de date, si elles ne l'ont pas préalablement, pour que la date s'affiche, ce que l'on fera...)

La 2e parenthèse concerne l'affectation du tableau résultat sous la forme : WorksheetFunction.Index(TC, i, 0).
VBA travaille très bien et très vite avec des tableaux mais dispose de peu de fonctions pour intervenir dans les tableaux et on est généralement obligé d'affecter élément par élément. Ici, la ligne étant détecté, on aurait dû redimensionner le tableau résultat sur 2 dimensions et sous la forme (colonnes, lignes) les colonnes étant fixes et les variant, et affecter les 34 éléments de la lignes... Excel fournit en VBA les moyens de procéder autrement. On connait la fonction INDEX et son usage dans Excel, ce qu'on méconnaît souvent (parce qu'on utilise peu cette faculté dans Excel), c'est que cette fonction peut renvoyer une matrice au lieu de ce contenter d'une seule valeur, si l'index ligne est mis à 0, elle renvoie la colonne entière, si l'index colonne est à 0 elle renvoie la ligne entière, et elle peut s'appliquer à un tableau VBA comme à toute autre matrice.
Ainsi, on affecte la ligne entière d'un seul coup, soit un tableau, et on utilise un tableau résultat à une seule dimension au lieu de deux.

La partie suivante du code (après la boucle) est constitué par un bloc With... End With. Lorsqu'on écrit With expression.renvoyant.un.objet, toute expression à l'intérieur du bloc précédée d'un point réfèrera à cet objet, ce qui réduit l'écriture de façon qui être très conséquente, mais plus important, VBA met la référence en mémoire et y accède beaucoup plus rapidement. C'est donc une instruction particulièrement intéressante, à utiliser dès qu'on à se référer plus d'une fois à un objet...
Dans ce bloc, on réaffecte le tableau prélevé au départ et modifié par ajout de la date et effacement du "X". Puis on met la colonne date au format date (certaines cellules en avaient un, d'autres non... j'ai retenu le format qui semblait être choisi, si ce n'est pas le cas tu pourras le modifier (mais l'écrire dans le code sous forme identifiable par VBA : d pour le jour (d ou dd ou ddd ou dddd), m pour le mois (m ou mm ou mmm ou mmmm) et y pour l'année (yy ou yyyy).

La dernière partie est également constituée par un bloc With : la 1re ligne qui suit With cherche de façon classique la dernière ligne utilisée dans la colonne A de la feuille mise sous bloc With et ajoute 1 pour définir la ligne d'insertion de nos résultats (note que .Range et .Rows réfèrent à la feuille dans cette ligne).
Suit un 2e bloc With imbriqué dans le premier, avec un objet référant à la feuille, soit la plage d'insertion des résultats définie à partir de la cellule le ligne i trouvée précédemment en colonne A, qu'on redimensionne au nombre de lignes de résultats, soit n comme on l'a vu, et de colonnes correspondant à la 2e dimension de notre tableau TC du départ (encore en mémoire). On sait que c'est 34, mais si ce nombre devait varier sans que les colonnes T et U soient affectées, il n'y aurait rien à retoucher à la macro.
L'affectation, à partir d'un tableau à une dimension contenant des tableaux représentant chacun une ligne, se fait par une double transposition (utilisation de la fonction Excel TRANSPOSE) pour rétablir les données telles qu'elles doivent figurer dans le tableau.
La ligne suivante applique un format de date à la colonne date, comme précédemment.

En espérant que ça ne t'a pas filé un mal de tête... voilà le classeur.

Cordialement.

https://www.cjoint.com/c/HHiwMQiqB0u



Modifié par ferrand le 09/08/2018 00:44
Kassie
 Posté le 13/08/2018 à 15:07 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

Un immense merci Ferrand. Merci pour ce code et merci pour toutes les généreuses informations que tu y joints. Moi qui adore comprendre, je suis gâtée !

J'ai lu et non, je ne comprends pas tout. J'applique le tout et te reviens pour le résultat.

Encore une fois, un immense merci !

Kassie
 Posté le 14/08/2018 à 17:18 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

Bonjour Ferrand,

Je reviens vers toi pour de l'aide, je ne comprends pas et pourtant j'essaie fort.

J'ai une erreur à cette ligne:

With .Range("A" & i).Resize(n, UBound(TC, 2))

Ce qui est étrange c'est que certaines des lignes (si je fais des extractions pour tests) fonctionnent et d'autres non. Évidemment l'intégralité du fichier bloque. J'ai épuré mes données, (Fx Épuragage), recopié données seulement, testé dans le fichier test Storybrook et dans le réel, partout pareil, j'ai une constante. J'ai supprimé toutes les colonnes et les lignes ne contenant pas de données pour être certaine que quelque chose ne provenait pas de là. Aucun changement.

Je refermer mon fichier et retenter. Cette fois, c'est sur cette ligne qu'il bloque, avec l'intégralité des données.

AC(n) = WorksheetFunction.Index(TC, i, 0)

Je ne comprends pas, ça fait des heures que je cherche et que je teste tout ce que je peux. As-tu une piste pour moi svp ?

**Update : Je viens de voir que si je n'ai pas de X pour avis, c'est alors qu'il me fait une erreur...

Merci !



Modifié par Kassie le 14/08/2018 17:24
Publicité
ferrand
 Posté le 15/08/2018 à 23:40 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bonsoir,

Je suis parvenu depuis peu à destination après un voyage plutôt fatiguant, et là il a encore fallu que je cherche pourquoi mon ordi ne se connectait plus automatiqument comme il le fait toujours habituellement... Après avoir constaté que le problème était dû à l'intervention d'un "pro" et que je vais avoir un peu de boulot pour remettre en état ce qu'il n'a pas su correctement faire, sans dire ce qu'il avait fait qui m'aurait au moins évité de commencé par chercher dans d'autres directions... je ne vais guère tenir une longue discussion ce soir...

Reprenons les choses dans l'ordre pour être sûr d'y voir clair : la procédure est simple dans ce qu'elle fait, elle parcourt ton tableau de données à la recherche de mention "X" en colonne U, si elle en trouve, elle met la date du jour en T et élimine le "X" en U, puis prélève la ligne de données (qu'elle conserve provisoirement dans un tableau) afin de reporter les lignes prélevées sur une 2e feuille une fois toutes les lignes de données examinées.

Les explications que j'ai fournies dans mon post précédent du 9 août (nuit du 8 au 9) sont destinées à te fournir des indications plus précises sur la façon dont elle procède pour faire cela, qui n'apparaîtra pas spontanément si l'on n'a pas d'habitudes de travailler selon certaines des méthodes utilisées, donc pour te premettre de mieux comprendre les mécanismes de chaque action, et à terme mieux les maîtriser. S'il y a des choses que tu ne comprends pas, il convient de préciser lesquelles, car je ne peux le deviner...

La première chose à faire est d'abord de constater que la procédure fonctionne dans le fichier modèle que j'ai retourné, qu'elle remplit son office en réalisant ce qui était prévu ou non. Il convient de s'assurer que cet essai a bien été fait, avant de passer à des modifications sur le fichier, ou de faire agir la procédure sur un autre fichier, car c'est à partir de ce fonctionnement de bases sur les éléments modèle fournis au départ que l'on pourra examiner à quoi sont dus les dysfonctionnements éventuels susceptibles d'apparaître par la suite. Et en cas de dysfonctionnement sur des essais ultérieurs relever en quoi le contexte se distinguait alors de la situation testée au départ.

A la suite si le dysfonctionnement se traduit par une erreur d'exécution, il convient bien sûr de relever sur quelle ligne de code se produit l'erreur, mais également d'indiquer quel est le numéro d'erreur, et les valeurs des variables impliquées au moment de l'erreur. Au cas particulier de la ligne que tu cites, les valeurs de i et n sont indispensables pour tenter de détecter la source... Il est facile d'obtenir cette information lorsque tu es en mode débogage : il suffit de ballader le curseur sur le nom des variables et leur valeur s'affiche dans une info-bulle.

Les conditions de réalisation de l'opération prévoyaient que tu apposes tes "X" et qu'ensuite tu lances la procédure. Evidemment, si n'y a aucun "X", le tableau de lignes transférées n'existera pas et son affectation ne pourra alors se traduire que par une erreur. Contre cette erreur qui peut intervenir sur un clic intempestif alors que les conditions préalables n'en sont pas réunies, on peut établir un garde-fou simple :

Next i
If n = 0 Then Exit Sub
With ActiveSheet.Range("A1").CurrentRegion

Ajouter la ligne citée ici au milieu (en rouge) entre les 2 lignes indiquées. A la fin du traitement des données, si aucune ligne n'a donné lieu à traitement, inutile de poursuivre...

S'il y a d'autres erreurs que cette erreur logique, il faut en reprendre les explications selon les indications données ci-dessus pour pouvoir les analyser.

Cordialement.



Modifié par ferrand le 15/08/2018 23:41
Kassie
 Posté le 16/08/2018 à 16:02 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

Bonjour Ferrand,

Je suis désolée pour toi de ces soucis de PC et de connexion. Espérant que tout s'arrange rapidement pour toi !

Je ne savais pas qu'en mode déboggage on pouvait voir où ça coince. Pratique ! Voici donc ce que ça me donne:

Erreur d'exécution '13":

Incompatibilité de type

Le modèle avec les données bidon fonctionne bien. Si je mets de vraies données, il coince tout autant. J'ai validé mes données, je ne vois rien qui ne soit pas conforme ou réglo. J'ai donc modifié mon vrai fichier pour l'anonymiser et diminuer la quantité de données et le voici. J'ai aussi passé une macro pour trouver et supprimer les noms masqués. Ce n'est pas non plus le problème de toute évidence.

Voici donc le fichier avec l'erreur. https://www.cjoint.com/c/HHqoaUhNmhw

Merci encore et encore !

ferrand
 Posté le 18/08/2018 à 02:26 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bonsoir,

J'ai effectivement constaté l'erreur... Son caractère surprenant, du fait qu'elle ne se produit pas dans un fichier, mais se produit dans un autre, sur une ligne tout à fait comparable à la précédente. Mais en fait la particularité de la ligne de code est de faire appel à la fonction INDEX d'Excel, fonction qui permet de cibler un élément d'un tableau ou une ligne ou une colonne, à partir du tableau lui-même pris dans son ensemble. Ce n'est donc pas la ligne qu'on prélève qui est à la source de l'erreur mais le tableau dans lequel on fait ce prélèvement !

Plus précisément, si dans les nouvelles versions d'Excel une cellule peut contenir jusqu'à 32767 caractères, on sait déjà qu'un tel contenu n'est pas exportable par copier-coller, il sera tronqué, et que certaines fonctionnalités demeurent bornées sur l'ancienne limitation à 255 caractères. Et c'est visiblement le cas pour un tableau utilisé par INDEX...

Si donc tu ramènes le contenu des cellules de la colonnes Observations des lignes 30 et 38 à 255 caractères, tout fonctionnera normalement !

Ce serait évidemment la solution la plus simple pour moi !!

Mais rassure-toi, on peut trouver des moyens de contournement en limitant au mieux le temps supplémentaire que ça prendra. Mais avant d'apporter un correctif il serait bon de savoir si cette situation (texte de longueur supérieure à 255 caractères) peut affecter d'autres colonnes. Puis on testera laquelle des 2 ou 3 solutions qui m'apparaissent sera la plus économique.

En attendant tu peux tester la procédure en éliminant préalablement les points de blocage.

Cordialement.

ferrand
 Posté le 22/08/2018 à 23:13 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Toujours pas réapparue ?

A ce régime on va finir par mettre des années pour traiter une question !

Kassie
 Posté le 28/08/2018 à 16:42 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

Je reviens à la seconde, j'étais en vacances (yééé !) et je me suis fait une joie extrême de n'ouvrir aucune application utilisable au bureau! Je vous reviens très vite là dessus et merci !

Kassie
 Posté le 28/08/2018 à 17:35 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

Rebonjour,

Et si on ne copiait simplement pas la colonne "AI" (Observations) ou même de AI à la fin des colonnes ?? Elles ne sont pas nécessaires pour la suite lors de la copie. Est-ce que ceci nous simplifierais la tâche ?

ferrand
 Posté le 29/08/2018 à 00:32 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

J'espère que tes vacances se sont bien déroulées... mais ça ne leur aurait pas nui de signaler que tu t'absentais...

La question que j'avais posée était de savoir si d'autres colonnes pouvaient être concernées par ce dépassement de 255 caractères. Il serait bon d'y répondre.

Pour la solution, elle est testée depuis le 18, et son exécution s'avère plus rapide que celle de la procédure initiale ! Mais il faut que je me remette en mémoire la question...

Kassie
 Posté le 29/08/2018 à 14:23 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

Non en effet ça n'aurait pas nui, ça manquait de délicatesse de ma part. J'ai tendance à être "sauvage" quand je disparais. Tu m'en vois désolée. Je le prends comme une leçon de vie et je t'en remercie de ne pas m'en tenir rigueur.

Deux colonnes peuvent être concernées par le dépassement de 255 caractères, soit les colonnees AI et AJ, correspondant à Observations et Notes.

Comment déjoue-t-on la limitation de INDEX ?

Merci

Publicité
ferrand
 Posté le 30/08/2018 à 02:31 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

On n'utilise plus Index ! Mais on réduit le tableau Source prélevé pour travailler aux deux colonnes concernées, ce qui élimine le problème au niveau du tableau Source (et qui nous fait également gagner du temps...) mais le problème se transpose sur Transpose qui réagit comme Index...

Donc on teste la colonne Observations lorsque la ligne est à prélever. Si le nombre de caractères dépasse 255, on prélève le contenu dans un tableau spécifique en notant sur quelle ligne résultat il faudra le rétablir, ce qu'on fait après affectation (et on le supprime du tableau résultat).

Il faut donc que j'opère une rectification pour inclure la colonne Notes dans ce test...

@+

Kassie
 Posté le 06/09/2018 à 18:04 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

Bonjour Ferrand,

À mon tour de venir aux nouvelles... Est-ce que tout va bien ? Avez-vous eu le temps de jeter un coup d’œil là dessus avec les dernières données ?

Merci encore !

ferrand
 Posté le 07/09/2018 à 10:31 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bonjour,

J'attendais que tu réapparaisses ! Le code est fait depuis le 01/09, je ne l'ai cependant pas encore testé.

Je ferai ça d'ici demain. @+

ferrand
 Posté le 09/09/2018 à 02:19 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bonsoir,

Ma version 3 m'apparaît fournir les mêmes résultats que la 2bis précédente, je vais donc te laisser poursuivre les tests...

Sub AvisEnvoyés()
Dim TC, AC(), Obs(), i&, n&, j&, k%, o%
'Dim t!
't = Timer
ReDim Obs(2, 0): Obs(0, 0) = 0
With ActiveSheet.Range("A1").CurrentRegion
k = .Columns.Count
o = WorksheetFunction.Match("Observations", .Rows(1), 0)
TC = .Offset(, 19).Resize(, 2)
For i = 2 To UBound(TC)
If UCase(TC(i, 2)) = "X" Then
ReDim Preserve AC(n)
AC(n) = .Rows(i).Value
AC(n)(1, 20) = CLng(Date): AC(n)(1, 21) = Empty
If Len(AC(n)(1, o)) > 255 Or Len(AC(n)(1, o + 1)) > 255 Then
Obs(0, 0) = Obs(0, 0) + 1
ReDim Preserve Obs(2, Obs(0, 0))
Obs(0, Obs(0, 0)) = n
Obs(1, Obs(0, 0)) = AC(n)(1, o): Obs(2, Obs(0, 0)) = AC(n)(1, o + 1)
AC(n)(1, o) = "": AC(n)(1, o + 1) = ""
End If
n = n + 1
TC(i, 1) = CLng(Date): TC(i, 2) = Empty
End If
Next i
If n = 0 Then Exit Sub
.Offset(, 19).Resize(, 2).Value = TC
.Columns(20).NumberFormat = "dd-mmm-yy"
End With
Application.ScreenUpdating = False
With Worksheets("Avis CAS-2")
i = .Range("A" & .Rows.Count).End(xlUp).Row + 1
With .Range("A" & i).Resize(n, k)
.Value = WorksheetFunction.Transpose(WorksheetFunction.Transpose(AC))
.Columns(20).NumberFormat = "dd-mmm-yy"
.WrapText = True
If Obs(0, 0) > 0 Then
For j = 1 To Obs(0, 0)
.Cells(i + Obs(0, j) - 1, o) = Obs(1, j)
.Cells(i + Obs(0, j) - 1, o + 1) = Obs(2, j)
Next j
End If
End With
End With
'MsgBox (Timer - t) * 1000
End Sub

J'ai laissé les commandes de mesure de la durée d'exécution, mises en commentaires. Pour les utiliser il suffit d'enlever les apostrophes en début de ligne, et la durée s'affichera en millisecondes. Chez moi, avec l'exemple utilisé, cela tourne autour de 100 ms (soit 1/10e de seconde). Le dernier ajout n'a quasiment pas fait varier cette durée, qui reste inférieure à la durée de la première version. Je vais essayer de m'en souvenir pour des configuration similaires, on prélève les lignes sur la feuille au lieu de les récupérer comme précédemment sur un tableau, ce qui en principe est plus lent, mais on continue à faire les tests sur un tableau, nettement réduit (à une colonne), ce qui apparemment compense largement l'opération sur feuille qui demeure limitée en nombre de lignes à prélever...

Cordialement.

https://www.cjoint.com/c/HIjarpfQS48

Kassie
 Posté le 10/09/2018 à 17:42 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

Tu attendais que je réapparaisse et j'attendais des nouvelles patiemment pour ne pas me montrer harcelante... Chère communication...

J'ai mis en place la dernière mouture du code. C'est parfaitement fonctionnel. J'ai un temps d'exécution sur le fichier final de 191.4063ms ce qui est excellent. J'ai fait divers tests et tout semble bien.

J'ai un peu de mal à comprendre le code de la dernière mouture par contre. Un cours intensif de VBA ne serait peut-être pas un luxe ! lol

Je te dis un IMMENSE merci Ferrand pour l'aide et tout le temps que tu as passé sur ce projet. J'apprécie vraiment beaucoup.

Avec gratitude,

ferrand
 Posté le 11/09/2018 à 02:26 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bonsoir,

Tu me dis ce que tu ne comprends pas, et je tâcherai d'en fournir l'explication...

Bonne journée (pour demain !)

Kassie
 Posté le 11/09/2018 à 16:32 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

Bonjour,

Je ne comprends pas toute l'opération faite pour les Observations (Obs). Tu me dis avoir réduit le tableau à une colonne ?

Autant j'était parvenue à comprendre l'autre code, autant celui-ci me semble flou et complexe. Je manque clairement de formation sur la programmation complexe.

Bonne journée ou bonne soirée !

ferrand
 Posté le 12/09/2018 à 19:03 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bonjour,

Ce qui est réduit, à 2 colonnes en fait c'est le tableau TC dans lequel on recueille les données source :

With ActiveSheet.Range("A1").CurrentRegion
[...]
TC = .Offset(, 19).Resize(, 2)

On recueillait auparavant tout le tableau, on ne recueille plus que les 2 colonnes qui sont susceptibles de modifications, selon test sur la 2e colonne du tableau prélevé.

Pour les lignes à transférer sur l'autre feuille, elle ne sont donc plus reprise du tableau prélevé sur la source, mais directement sur la feuille source :

For i = 2 To UBound(TC)
If UCase(TC(i, 2)) = "X" Then
ReDim Preserve AC(n)
AC(n) = .Rows(i).Value

S'agissant d'une ligne répondant à la condition, on en modifie les valeurs des colonnes T et U :

AC(n)(1, 20) = CLng(Date): AC(n)(1, 21) = Empty

AC(n) est l'élément du tableau résultat qu'on constitue, cet élément n en est une ligne entière, donc un tableau à 2 dimensions de base 1 comprenant 1 ligne et autant de colonnes que dans le tableau source, d'où la syntaxe : AC(n)(1, 20). On vise l'élément d'indice (1, 20) [ligne 1, colonne 20] de l'élément n du tableau AC...

Ensuite on teste si l'élément correspondant à la colonne Observations (ainsi que la colonne suivante : Notes) est une chaîne comportant plus de 255 caractères :

If Len(AC(n)(1, o)) > 255 Or Len(AC(n)(1, o + 1)) > 255 Then

o a été préalablement initialisée avec le numéro de la colonne Observations [ o = WorksheetFunction.Match("Observations", .Rows(1), 0) ], on teste donc les éléments (1, o) et (1, o + 1) de AC(n).

Si le test est positif, on incrémente le tableau Obs :

Obs(0, 0) = Obs(0, 0) + 1
ReDim Preserve Obs(2, Obs(0, 0))

Ce tableau a été déclarée comme tableau dynamique, c'est à dire que l'on peut redimensionner sa dernière dimension au fil des besoins, en utilisant le mot clé Preserve pour préserver le contenu déjà affecté au tableau. Il a été dimensionné au départ : ReDim Obs(2, 0) comme tableau comportant 3 éléments dans sa 1re dimension, d'indices 0 à 2, et un élément dans sa seconde dimension, d'indice 0. On utilise l'élément (0, 0) [les éléments (1, 0) et (2, 0) resteront inutilisés] pour conserver l'incrémentation. Pour incrémenter, on ajoute donc 1 à cet élément et on redimensionne sur cette nouvelle valeur, ce qui ajoute 3 éléments au tableau : (0, .), (1, .) et (2, .)

Dans l'élément (0, .) on va placer le numéro d'élément de notre tableau AC, soit n, qui nous permettra d'identifier sur quelle ligne devront être rétablies les valeurs enlevées du tableau résultat.

Et dans (1, .) et (2, .) on place le contenu correspondant aux colonnes Observations et Notes (que l'on élimine aussi du tableau AC :

Obs(1, Obs(0, 0)) = AC(n)(1, o): Obs(2, Obs(0, 0)) = AC(n)(1, o + 1)
AC(n)(1, o) = "": AC(n)(1, o + 1) = ""
End If

Une fois le tableau résultat constitué, on recherche la première ligne à servir sur la feuille cible, numéro de ligne qu'on affecte à la variable i, puis on définit la plage d'affectation et on affecte les résultats.

Ceci fait, on vérifie si des Observations et Notes sont à rétablir :

If Obs(0, 0) > 0 Then

Si c'est le cas, on y procède. On connait les colonnes : o et o+1. On détermine la ligne à partir de l'élément (0, .) du tableau Obs : première ligne de la plage affectée + élément (0, .) -1.

For j = 1 To Obs(0, 0)
.Cells(i + Obs(0, j) - 1, o) = Obs(1, j)
.Cells(i + Obs(0, j) - 1, o + 1) = Obs(2, j)
Next j

Et voilà !

Cordialement.



Modifié par ferrand le 12/09/2018 19:06
Kassie
 Posté le 13/09/2018 à 15:57 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

Ferrand, un IMMENSE merci pour ton temps, ta réflexion, tes réponses cordiales et la générosité de tes explications. C'est vraiment très très apprécié.

Avec gratitude sincère, je te souhaite une excellente journée

Publicité
Pages : [1] 2 ... Fin
Page 1 sur 2 [Fin]

Vous devez être connecté pour poster des messages. Cliquez ici pour vous identifier.

Vous n'avez pas de compte ? Créez-en un gratuitement !


Sujets relatifs
Aucun sujet pertinent lié trouvé
 > Tous les forums > Forum Bureautique