Copier un nombre très important de lignes

Sinon pour toutes les questions existentielle du pourquoi/comment les colonnes et ordre, c'était juste par rapport à mon code dans lequel on peut lui demander de faire une recherche de valeur dans les colonnes choisies (ou toutes), et que le fichier résultat pouvait suite à notre demande retourner qu'un certain nombre de colonnes choisie tout en les mettant dans l'ordre voulu.

Il est bien évident que le code que j'ai fourni ne correspond pas à la demande, c'était une suite de "recherche" par rapport à cette dernière.
Mon code un peu lourd sur une recherche d'une donnée sur une seule colonne se défend pas mal en terme de temps, mais il arrive vite à ses limites.

L'important et que je le comprenne.

Tous vos codes sont super optimisés et vont très vite, il me reste plus qu'à les analyser pour me perfectionner.

Désolé Jacky, je n'ai pas été bon sur ce coup là !

@ bientôt

LouReeD

Bonsoir Loureed,

Désolé Jacky, je n'ai pas été bon sur ce coup là !

Je crois que tu exagères un peu
Quant à moi je voudrais être aussi bon que cela et je fais tout ce que je peux, malgré mon grand âge, pour y arriver (lol)

Bien amicalement et au plaisir

Bonsoir Bart,

Je suis très interessé par l'ensemble du code que tu as fourni pour contruire et alimenter toutes ces feuilles
Il est vrai que je ne suis pas à ta hauteur, donc je flanche sur certaines paties, en particulier celle ci

'********************************** UNIQUES COMPTES ********************************************************
     Stop
     Set dict = CreateObject("scripting.dictionary")
     dict.comparemode = vbTextCompare
     For I = 2 To UBound(aA)
          If Len(aA(I, 2)) Then dict(aA(I, 2)) = vbEmpty
     Next

si je comprends bien tu construit un objet (tableau) dict dans lequel tu feras des comparaison au niveau du texte ?
Si c'est cela, la suite je ne comprens plus : tu veux lire toutes les cellules de la colonne 2 et qu'en fais tu ?
Serais tu super gentil et m'expliquer ces quelques lignes
Un grand merci d'avance et bien cordialement

Jacky

re,

https://excelmacromastery.com/vba-dictionary/

un dictionaire, c'est un outil très puissant et vite, qu'on peut utiliser pour toutes sorte de choses, mais ici, c'était pour créer une liste de numéros uniques. Avec ce loop, j'écris chaque élément de la 2eme colonne comme "key" et vbempty comme item (cela n'a pas d'importance ici, parce qu j'étais uniquement intéressé des keys). Si j'écris 20 fois le même numéro vers le dictionaire, il ignore les 19 doublons, donc au bout du loop, on a les numéros uniques sans doublons.

Bonjour Bart,

Merci pour ta réponse
Toutefois la ligne suivante me semble nébuleuse :

          If Len(aA(I, 2)) Then dict(aA(I, 2)) = vbEmpty

En effet, si j'ai bien compris, tu affectes l'item vbEmpty, soit 0, en fonction de key qui est la longueur de la donnée située dans la colonne 2 qui est toujours 8
Pourquoi fais tu référence à la longueur de la donnée ?

Salut Jacky,

C'est une manière de vérifier que la cellule [ligne = I, colonne = 2] du tableau aA n'est pas vide. (la fonction If va vérifier la longueur du texte contenu en aA(I, 2), et si longueur = 0 cela équivaut cellule vide = FALSE => pas d'ajout dans le dictionnaire, et si longueur > 0, TRUE => ajout potentiel).

Ca évite de remplir le dictionnaire de valeurs vides. Je suppose qu'elles ne sont pas utiles pour le calcul.

Je ne sais pas si tu as bien saisi ensuite, le dictionnaire est vraiment, dans ce cas, utilisé comme équivalent de la Fonction UNIQUE - Support Microsoft dans Excel. Donc comme le disait Bart, ajouter plusieurs fois la même valeur ne pose pas problème car les doublons sont ignorés.

Petite explication du dictionnaire - filtrer les valeurs uniques d'une liste :

Pour se faire une image mentale le dictionnaire est constitué de 2 colonnes, les clés, à gauche, et les valeurs, à droite. Qui forment des couples. Les clés SONT OBLIGATOIREMENT UNIQUES, il n'existe pas de doublons de clés, ce n'est simplement pas possible. Les valeurs, elles, sont ce qu'on veut associer à la clé.

Dans ce cas, Bart veut simplement récupérer les valeurs uniques d'une liste. Il utilise donc un dictionnaire pour filtrer sa liste : en mettant les valeurs de la liste dans les clés du dictionnaire, celui-ci va automatiquement supprimer les doublons.

Par ailleurs, il faut donner à chaque clé une valeur de couplage, mais dans ce cas ça n'intéresse pas Bart, donc il met "vbEmpty" une valeur fantoche pour ne pas faire buguer le programme (le dictionnaire requiert toujours l'insertion d'un COUPLE clé-variable).

Ensuite il lui suffit de récupérer la liste des clés du dictionnaire, qui sera la liste des valeurs uniques de la liste initiale.

Bonjour Saboh,

Tu es formidable, tes explications sont claires et complètes.
Donc selon ce que tu m'expliques et selon ce que j'ai compris, un dictionaire refuse les clefs en doublon (chose que je ne savait pas), c'est pourquoi Bart en lisant toutes les données de la colonne "Compte-Général" et en les considérant comme clefs, il les rend uniques dans son dictiionnaire "dict". De plus comme chaque clé doit avoir une valeur de couplage, il lui adjoint "vbEmpty", soit 0, comme il aurait pu lui adjoindre "toto" car pour lui cette valeur n'a aucune importance.
Voila donc ce que j'ai compris, sauf erreur d'interprétation de tes explications, grâce à toi.
Par sécurité j'ai vérifié tout cela

      For L = 0 To UBound(aKeys)
                      MsgBox (aKeys(L))
      Next

et je ne retrouve bien que les "Compte-Général" sans aucun doublon

Saboh tu es super, car, comme je tiens à comprendre ce qui m'intéresse, et ne tiens pas à rester dans l'ignorance, grâce à ta gentillesse je progresse à grands pas dans les connaissances un peu hard du VBA Excel

Encore un grand merci et au plaisir de te retrouver

Exactement tu as tout compris.

Le site qu'a mentionné Bart explique tout cela très bien, c'est celui que j'utilise aussi. Par contre il est en anglais, à la limite tu peux essayer de le traduire, ou chercher un équivalent français. Je vois que Excel-pratique ne couvre pas le sujet, c'est assez normal car ce sont des "librairies externes" càd des outils de programmation non intégrés de base dans VBA (mais très utiles !).

Pour info, vbEmpty plutôt que "toto" c'est de l'optimisation (chaque toto prend un peu de place en mémoire, alors que vbEmpty non). Mais cela tu n'as pas besoin de t'en préoccuper pour le moment, l'optimisation viendra ensuite naturellement. Il ne faut pas noyer le poisson !

Au plaisir

Rebonjour Saboh,

Encore un grand merci pour tes explications
Elles furent tellement excellentes que je permets de te recontacter et d'abuser de ta gentillesse

J'avance dans le code de Bart, petit à petit, tout en le trouvant un peu élaboré. J'en suis à la construction des 19 feuilles, chose que j'avais su réaliiser, à ma façon, en utisant un "For" - "Next" et "Exit For si" (voir le premier fichier que j'ai proposé au demandeur). Bart utilise la gestion des erreurs pour ouvrir ces feuilles. Sa méthode me laisse un peu perplexe car j'ai du mal à l'interpréter

            Set sh = Nothing 
               On Error Resume Next ' passe à la ligne suivante en cas d'erreur
                   Set sh = Sheets(CStr(aKeys(iKeys)))  
               On Error GoTo 0  ' Désactive le gestionnaire d’erreurs
               If sh Is Nothing Then         'si la feuille n'existe pas alors la créer
                    Set sh = Sheets.Add(after:=Sheets(Sheets.Count))
                    sh.Name = aKeys(iKeys)
               End If

Si je comprends bien
On vide la feuille sh (est ce bien cela ?)
En cas d'erreur (quelle erreur ?) on passe à la ligne suivante et on attribue la valeur de la clef à la feuille
On désactive la gestion d'erreur (Pourquoi ?)
Enfin si la feuille que l'on vient de nommer n'existe pas on la crée

Voila ce que pense avoir compris, mais j'ai du mal à interpréter "sh = Nothing" qui peut engendrer une erreur

re, salut Saboh12617,Jacky,

avec "Set sh = Nothing", on "vide" le variable "sh" (une sorte d'etiquette, certainement pas la feuille) en assignant "nothing". Puis on essayera d'assigner une feuille à se variable (supposons la feuille "Jacky"). Si cette feuille n'existe pas, VBA bloque et c'est pourquoi on a mis ces 2 lignes "on error Resume next" et "on error goto 0", pour éviter cela.

Puis la ligne suivante, "If sh Is Nothing Then" >> si sh est encore "nothing", cela veut dire que la feuille "Jacky" n'existe pas dans ce fichier (sans ces lignes "On Error ...", VBA bloquait) et donc on ajoute une nouvelle feuille après toutes les autres feuilles. La ligne suivante on nomme cette nouvelle feuille "Jacky"

Si vous êtes content de votre méthode, bon, utilisez-la ...

Bonjour Jacky ,

Comme je suis quelque peu coincé à cause d'une opération bénigne et un temps exécrable, je tente de répondre à ta demande. Mes petits camarades m'ont devancé , mais je publie malgré tout(abondance de biens ne nuit pas).

Set sh = Nothing

Sh est une variable objet. C'est pour ça que l'affectation à cette variable se fait par le biais de Set <variable> = et non pas simplement par le signe d'affection =.

Comme le signifie Nothing (rien), quand on affecte Nothing à une variable objet alors cette variable ne pointe plus vers un objet. Si la variable sh avant référençait une feuille de calcul alors maintenant elle ne référence plus rien (ne pointe plus vers rien). Ca n'a rien à voir avec le fait de vider ou non une feuille de calcul. On dit simplement à la variable : avant tu référençais une feuille de calcul et maintenant tu ne références plus rien (Nothing).

Set sh = Sheets(CStr(aKeys(iKeys)))

Maintenant que la variable sh ne pointe plus vers un objet, on va la faire pointer vers une nouvelle feuille.

De deux choses l'une :

• soit la feuille existe et alors l'instruction Set = se passe bien et sh pointe vers la feuille désirée
• soit la feuille n'existe pas et alors Set sh ne peut pas faire pointer sh vers la feuille désirée. Dans ce cas, l'instruction aboutit à une erreur d'exécution. Erreur qui ne bloque pas l'exécution puisque qu'on avait dit au Compilateur de continuer l'exécution même en cas d'erreur (on error resume next)

On Error GoTo 0 ' Désactive le gestionnaire d’erreurs

Maintenant que nous avons traité ce que nous désirions, on rétablit la prise en compte normale des erreurs au cas où dans la suite du code une autre erreur se produirait. Sinon toutes les erreurs suivantes seraient masquées et on pourrait aboutir à un résultat faux si l'erreur d'après n'était pas traitée.

If sh Is Nothing Then

On peut maintenant tester si sh pointe vers une feuille (set sh c'est bien passé) ou bien si sh ne pointe vers rien (set sh n'a pas trouvé la feuille désirée). Si sh ne pointe vers "rien", alors il faut créer une nouvelle feuille, l'affecter à sh et donner à cette feuille (référencée par sh) le nom désiré:

               If sh Is Nothing Then         'si la feuille n'existe pas alors la créer
                    Set sh = Sheets.Add(after:=Sheets(Sheets.Count))
                    sh.Name = aKeys(iKeys)
               End If

Bonjour Jacky,

C'est la manière, en VBA, de faire de la gestion d'erreur. C'est la simulation la plus proche en VBA des "blocs try catch" qu'on voit dans beaucoup d'autres languages de programmation, et qui sont un peu plus "simples" à comprendre. Ce sont des instructions qui permettent de travailler "à l'aveugle" : on crée un bloc de code qui fonctionne meme si il lui manque certaines informations.

Pour cet exemple vous avez compris le principe, on va essayer de trouver la feuille avec le nom contenu dans aKeys(iKeys) (par exemple : la feuille "blablabla"), et on va adapter la suite du code en function de ce qu'on trouve.

Si la feuille existe, aucun soucis, notre variable "sh" est bien affectée lors de l'instruction

Set sh = Sheets(CStr(aKeys(iKeys)))

En revanche, si elle n'existe pas, que se passe-t-il ?

Le code s'arrete sur cette instruction, avec une erreur indiquant que l'affectation de sh n'est pas possible car cette feuille n'existe pas.

C'est là qu'intervient le "tour de passe-passe" : on peut forcer le code à ne pas s'arreter sur les erreurs avec On Error Resume Next. Donc l'exécution continue comme si tout allait bien, sauf que la variable sh est vide !!

La solution est j'espère maintenant plus claire :

1. On va assigner notre variable sh à une "pré-valeur", ici comme c'est une feuille (un Objet), la valeur par défaut qu'on utilise (pour plusieurs raisons que je ne détaillerai pas ici) est Nothing (rien).
2. On va essayer d'affecter à sh sa "vraie" valeur : la feuille recherchée. On utilise On Error Resume Next pour ne pas arreter l'exécution du code. (On remet "On Error Goto 0" après pour ne pas masquer les futures erreurs potentielles du code ! C'est important, ça permet de comprendre ce qui coince)
3. On va regarder ce que contient sh avec If :

Si elle contient la valeur de défaut (sh Is Nothing), alors l'étape 2 n'a pas fonctionné : il faut ajouter la feuille. C'est l'instruction suivante

Set sh = Sheets.Add(after:=Sheets(Sheets.Count))

Sinon, sh est différente de la valeur de défaut, on est bon la feuille existe déjà et elle est dans notre variable.

C'est un principe générique de programmation. Si c'était je ne sais pas, le numéro de ligne contenant "A" par exemple, sans savoir si "A" est dans le tableau. On pourrait écrire en (1) nbLigne=-1. Puis en (2) on cherche "A" et on écrit nbLigne = Recherche("A"). Puis en (3), on sait qu'un numéro de ligne est toujours positif, donc on regarde : Si nbLigne = -1 (ou mieux nbLigne < 0), ça veut dire qu'on n'a pas trouvé "A".

Voilà le principe.

EDIT : J'ai été pris de vitesse, je pense que toutes les réponses sont complémentaires. Avec ça tu devrais t'y retrouver

Bonjour Bart
Bonjour MaFraise
Bonjour Saboh

Merci pour vos interventions rapides
Grâce à vous je vais devenir un super expert du VBA (lol)

Je vais regarder vos explications avec soin dans la journée

Encore merci à vous trois

re Jacky, MaFraise, Saboh,

l'explication de MaFrais et Saboh et mieux que la mienne parce que je suis gêné par certains termes précis en français, ce n'est pas ma langue maternelle (et j'utilise Google Translate pour me dépanner).

Donc merci vous 2

vous êtes tous les 3 merveilleux,

Grâce à vous je progresse à la vitesse grand V dans l'utilisation de VBA Excel en ce qui concerne les procédures élaborées

En effet, j'avais très mal interprété l'utilisation de "Set sh = nothing" et d'autre part je n'ai jamais utilisé la gestion des erreurs comme vous le faites.
Donc un énorme merci

Je vais continuer à me plonger dans la suite du code de Bart avant de clore ce post

Je tiens à vous remercier énormément et surtout à affirmer haut et fort que heureusement qu'il y a des gens comme vous sur ce Forum, personnes avec lesquelles on peut discuter afin de résoudre nos difficultés quel qu'en soit leur niveau.

Bart ne te fais aucun souci avec les difficultés que tu éprouves à utiiliser le français, comme je te l'ai déjà dit, mon épouse, bien que de nationalité française, est d'origine Belge (ses parents étaient Flamands). Elle a vécu jusqu'à 52 ans à Menin (Mennen en belge) et de ce fait ne parlait qu'en Flamand ou en Néerlandais, pratiquement peu en Français. Quand elle m'a rejoint nombreuses furent ses difficultés à retrouver le Français. Donc je comprends très bien

Je vous souhaite à tous les trois une bonne fin de journée et à bientôt

Merci du retour Jacky ,

Pour BsAlv que je salue .

Si je savais me débrouiller avec une langue étrangère aussi bien que tu le fais avec le français , je serai le plus heureux des forumeurs.

Alors inutile de t'excuser auprès de nous tous qui suivons tes interventions avec intérêt .

Bonjour Bart, bonjour mafraise, bonjour Saboh,

J'ai honte, une fois de plus je dois faire appel à votre gentillesse.
J'ai du mal à interpréter la suite ducode

1)

                    aLignes = Application.Transpose(Split(Mid(s, IIf(b, 3, 1)), ","))

pourquoi passer de colonnes en lignes ou vice versa ?

2)

Function Colonnes(Source As Range, Optional Destination As Range)
     Dim aA, aOut, aMFC, X, I, r, s
     '*********************************************************************
     ' fonction pour savoir quelles colonnes et leur séquence dans le résultat
     'aussi les colonnes pour appliquer une MFC
     '*********************************************************************
     aA = Source.Value
      'entêtes de la source
     If Destination Is Nothing Then          'si il n'y a pas d'entête
          'aout=worksheetfunction.sequence(,ubound(aa)) 'à partir d'Excel2021
          aOut = ColExtract
          MsgBox ColExtract
          'Evaluate("column(offset(a1,,,," & UBound(aA, 2) & "))")     'pour les versions plus anciennes
          aMFC = ColCherche
          MsgBox aMFC
     Else
          X = Destination.Value              ' entêtes de la destination
          MsgBox X
          ReDim aOut(1 To UBound(X, 2))      ' préparer matrice
          For I = 1 To UBound(X, 2)
               r = Application.Match(X(1, I), aA, 0)
               If IsNumeric(r) Then aOut(I) = r Else aOut(I) = UBound(aA, 2) + 1     'numéro de la colonne dans le source oubien derniere colonne (blague)
          Next
          s = ""
          For I = 0 To UBound(ColCherche)
               r = Application.Match(ColCherche(I), aOut, 0)     'la colonne de cherche, est-elle aussi dans la destination
               If IsNumeric(r) Then s = s & "," & r
          Next
          If Len(s) > 0 Then aMFC = Split(Mid(s, 2), ",") Else aMFC = "-"
     End If

     Colonnes = Array(aOut, aMFC)

End Function

Je ne comprends pas cette fonction, si ce n'est qu'elle est interprétée en fonction du fait que "Destination est nothing" ou non. Qu'est Destination ?
Ensuite je ne comprends pas

Vraiment désolé de vous importuner, mais ......
Un grand merci à chacun d'entre vous

Bonjour Jacky,

Pour votre première question, voyez les 2 screenshots ci-après :

J'ai séparé l'instruction en 2 étapes, et exécuté le code pas à pas pour voir la différence entre "avec" et "sans" transpose.

Vous voyez que cela permet de passer d'un tableau 1D (une liste) à un tableau 2D.

C'est à dire que pour récupérer un élément, au lieu d'écrire aLignes(index) on doit écrire aLignes(index, 1).

Alors bon, effectivement ça semble très étrange ici, ça complique juste les choses.

Mais on doit regarder après :

Arr = Application.Index(aA, aLignes, arr0(0))

Application.Index c'est l'appel à la fonction INDEX (INDEX, fonction) - Support Microsoft d'Excel, car on peut aussi utiliser les fonctions du classeur en VBA, c'est très pratique. (comme application.transpose justement).

Mais attention, il faut que les arguments correspondent à des plages Excel, autrement on va avoir des résultats étranges et/ou des erreurs. Hors une plage Excel ce n'est ni plus ni moins qu'un tableau 2D.

En l'occurrence ici, la fonction INDEX prend comme 2e argument le numéro de ligne cherchée(s). Vous le savez certaines fonctions acceptent à la place d'un seul argument (ici le numéro de ligne), une plage. On passe alors en formule matricielle (CTRL+MAJ+ENTR) dans Excel, et la fonction est évaluée pour chaque valeur de la liste donnée. Donc Bart fait fonctionner INDEX pour toutes les valeurs de sa liste d'un coup.

L'objectif final était donc "simplement" d'éviter d'écrire une boucle, en utilisant le plein potentiel des fonctions Excel en VBA. C'est très ingénieux.

Pour bien comprendre, regardez les 2 screens suivants :

Dans le second, j'ai RETIRE application.transpose. On voit que le tableau "arr" est plein d'erreurs. La fonction INDEX ne fonctionne pas avec les tableau 1D.

Dans le premier, le code de Bart, on voit que le tableau "arr" contient tous les résultats de la fonction INDEX pour chaque valeur du tableau "aLignes". C'est le résultat escompté.

Pensez à utiliser l'espion de variables, c'est votre meilleur ami "pour comprendre".

Pour la 2e question, la fonction "Colonnes", je ne suis pas sur mais je crois qu'il s'agit d'un filtre sur le résultat pour sélectionner, dans le tableau initial avec toutes les colonnes, les colonnes que l'on veut exporter.

Par défaut toutes, mais autrement on peut choisir par exemple les 3 premières et la dernière, et cette fonction "nettoie" les colonnes non désirées.

Destination ce sont les en-tetes de colonne à garder. La fonction s'adapte si cette info est manquante (destination is missing) et renvoie alors toutes les colonnes… ?