Améliorer code VBA "étendre plusieurs formules et MFC"
Bonjour,
Dans mon document Excel, il y a un champ de saisie qui permet de rechercher des listes à partir d'un code unique.
Je peux rechercher une liste une seule fois, au-delà, la formule qui permet ce traitement présente un décalage et progressivement la colonne contenant cette formule ne donne plus les bon résultats. Pour éviter ce problème, je demande à Excel, via une macro, d'étendre la formule jusqu'à la 16 000 ème ligne.
J'espérais que la ligne contenant cette formule : " =RECHERCHEV('Traitement saisie'!E2;'Données de travail'!B2:C6400;2;FAUX)" s'applique à chaque fois, sauf qu'à nouveau, lorsqu'une recherche a déjà été effectuée (puis classée) je me retrouve systématiquement avec une formule initialement présente dans une autre cellule (puisque les cellules qui ont été recherchées avec cette formule ont été classées dans l'ordre alphabétique).
Je me retrouve par exemple avec une cellule contenant : =RECHERCHEV('Traitement saisie'!E452;'Données de travail'!B569:C6967;2;FAUX). Qui va ensuite se retrouver étendue sur 16 000 lignes avec, au final, de gros décalages et des formules qui recherchent des infos nulle part.
J'utilise cette macro :
Private Sub CommandButton2_Click()
Range("B:B").Select
Selection.Copy
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-12
End Sub
Savez-vous comment je pourrais lui demander d'étendre systématiquement "=RECHERCHEV('Traitement saisie'!E2;'Données de travail'!B2:C6400" de la ligne 2 à la ligne 6400, lorsque j'appuie sur le bouton.
Merci de votre attention !
Bonne soirée
Bonsoir,
As-tu déjà entendu parler des références absolues ?
Dans ta RECHERCHEV, la plage renseignée dans l'argument "Table-matrice", pour la première formule devrait être:
'Données de travail'!$B$2:$C$6400
Bonsoir,
C'est comme ça que je l'ai écris au départ, mais ça ne fonctionnait pas. Je me quis rendu compte que c'est l'utilisation des $ qui posait problème.
En les retirant tout fonctionnait.
Bonjour,
Si tu recopies ta formule avec la poignée de recopie, la seule solution est d'utiliser des références absolues pour le second argument de la RECHERCHEV.
Comme il y a (dans ton bout de code et dans tes explications) des choses qui paraissent étranges, je te suggère de déposer une copie (allégée et sans données confidentielles) de ton fichier (y compris les macros). Ajoute des explications sur ce que tu veux réaliser concrètement (avec l'un ou l'autre exemple) ... on y verra sans doute plus clair.
Bonjour,
C'est ce que je souhaitais faire au départ, mais si j'utilise des références absolues, je n'obtiens qu'un seul résultat, toujours le même (qui correspond à B2), sur l'ensemble des cellules où j'étend la formule.
Même en allégeant le document, il conserve un poids trop important pour passer via les fichiers joins. Je passe donc par cjoint.
Lien : https://cjoint.com/c/HJgolTglaHh
Voici ce que je recherche à faire :
Tout se passe dans la feuille 8 (si tu veux faire un tour dans le document pour vérifier la validité des formules ne te gène pas ! A priori ça fonctionne bien) :
J'ai un champ de recherche que j'utilise pour rechercher des "numéro d'étude".
A chaque numéro d'étude, j'ai une liste de codes ; ces derniers apparaissent alors en colonne B.
Via la feuille 5 notamment, ces codes donnent des correspondances (genre / espèce floristiques).
Je peux faire 1 seule recherche, ensuite il y a des décalages qui m'empêchent d'utiliser le document à nouveau, je dois donc étendre les formules en veillant à ce qu'elles repartent du début.
J'ai fait un bouton "nettoyer" qui est censé étendre les formules des colonnes B, C, E et F (la cellule D a terme, et probablement d'autres).
Les formules sont :
Colonne B : =RECHERCHEV('Traitement saisie'!E2;'Données de travail'!B2:C299;2;FAUX)
Colonne C : =SI(ESTNA(RECHERCHEV(B2;'Database synonymes complete'!AP$2:AQ$22127;1;FAUX));"Erreur de saisie ou cellule vide";RECHERCHEV(B2;'Database synonymes complete'!AP$2:AQ$22127;1;FAUX))
Colonne D : =SI(NB.SI('Database complete'!AP$2:AQ$22127;B2)>1;"choisir ->";RECHERCHEV(B2;'Database complete'!AP$2:AQ$22127;2;FAUX))
Colonne E : =SIERREUR(E2;"Synonyme ou erreur de saisie")
Elles doivent être étendues sur quelques milliers de lignes (pour prendre en compte toute la base de données) et elles doivent conserver leur MFC.
J'espère être suffisamment clair dans mes explications ; j'ai fait une annotation dans mon document Excel.
Merci d'avoir pris la peine de jeter un œil à mon problème.
Bonne journée !
Bonsoir,
Mon antivirus refuse obstinément le téléchargement de fichiers depuis cjoint.com Je ne vais pas lui forcer la main
On n'a pas besoin du fichier avec ta base complète: une centaine de lignes devrait suffire !
Bonjour,
Dans ce cas, je joins le document avec 15 lignes à chaque fois. Il ne faut pas s'inquiéter des erreurs !
Garder 100 lignes à chaque fois ça laisse un document de 14mo.
J'ai fait un autre document pour tester cette formule :
=RECHERCHEV('Traitement saisie'!E2;'Données de travail'!$B$2:$E$6400;4;FAUX) ; rien y fait, les $ posent problème...
Merci,
Bonne journée !
Bonjour,
Bon ... j'ai (enfin) pu ouvrir ton fichier et très sincèrement je te suggère d'arrêter tout: parti comme tu l'es, tu vas (mais il semble que ce soit déjà le cas) te retrouver dans une m... noire !
Dans ta feuille "Correspondances", tu fais une recherche sur une valeur qui se trouve en feuille "traitement saisie", dans une cellule dont la référence est relative (E2). Cette cellule est elle-même le résultat d'une formule 'Données de travail'!B2
... laquelle est aussi le résultat d'une formule
=RECHERCHEV('Centralisation des données'!A2;'Centralisation des données'!C:D;2;0)et ainsi de suite (parce que là, je n'ai pas fini de remonter la chaîne
Par ailleurs, la valeur cherchée 3057_ ... est la même pour toutes les formules de ta feuille "Correspondances". La fonction RECHERCHEV ne trouve que la première occurrence de cette valeur dans un tableau et donc tu "forces" le comportement normal de cette fonction jusqu'à obtenir ce que tu cherches.
Enfin, tu fais un tri (via une macro) sur une plage contenant des formules faisant référence à des adresses relatives ... ce qui génère également une erreur monumentale
Je te dis ça, mais tu peux attendre un avis différent du mien, évidemment ! À ta place je reprendrais tout à zéro, mais à chaque étape, il faudrait que tu vérifies si ce que tu fais est bien "orthodoxe". Recopier les formules (que ce soit "à la main" ou par macro) n'aurait aucun sens. Il ne sert donc à rien que je cherche une réponse à ta question, j'en suis navré !
Merci d'avoir répondu.
J'étais justement en train de simplifier un peu mon document.
Après de nombreux tests, je n'ai aucune erreur, si ce n'est le décalage qui se fait ; d'où mon souhait de pouvoir écraser les données via l'étalement automatique de formules.
Enfin, tu fais un tri (via une macro) sur une plage contenant des formules faisant référence à des adresses relatives ... ce qui génère également une erreur monumentale
Oui c'est ce qui me chagrine en ce moment.
Quelque chose qui ferait comme ça :
En colonne B, tu étales cette formule : Colonne B : =RECHERCHEV('Traitement saisie'!E2;'Données de travail'!B2:E6400;4;FAUX)
En colonne C, tu étales cette formule : =SI(ESTNA(RECHERCHEV(B2;'Database synonymes complete'!AP$2:AQ$22127;1;FAUX));"Erreur de saisie ou cellule vide";RECHERCHEV(B2;'Database synonymes complete'!AP$2:AQ$22127;1;FAUX))
En D : =SI(NB.SI('Database complete'!AP$2:AQ$22127;B2)>1;"choisir ->";RECHERCHEV(B2;'Database complete'!AP$2:AQ$22127;2;FAUX))
En E : =SIERREUR(E2;"Synonyme ou erreur de saisie")
Etc.
Il y a peut-être des éléments que je ne saisi pas... , j'ai l'impression que ma requête revient à "couvrir la merde avec de la peinture", mais c'est le dernier problème avant un fonctionnement, a priori, tout à fait normal de mon document.
Je peux peut-être expliquer l'utilité de toutes ces formules.
Je vais aussi revoir chaque formule pour voir si on peut faire plus simple.
EDIT :
Je vais opter pour réaliser les recherches sur une autre colonne et faire ensuite faire apparaître les infos de la colonne où j'ai fati la recherche dans une autre colonne.
La colonne recherche ne sera jamais modifiée, classée ou autre.
En revanche, les mêmes informations qui apparaîtrons dans une autre colonne pourront être traitée facilement, sans décalage possible.
Bonne journée !
Bonsoir,
Je ne saurais te dire si c'est la bonne technique, ne connaissant pas les tenants et aboutissants de ton projet, ni ce dont tu disposes au départ ... Mais si tu vois comment avancer, personne ne va t'en empêcherJe vais opter pour réaliser les recherches sur une autre colonne et faire ensuite faire apparaître les infos de la colonne où j'ai fati la recherche dans une autre colonne.
La colonne recherche ne sera jamais modifiée, classée ou autre.
En revanche, les mêmes informations qui apparaîtrons dans une autre colonne pourront être traitée facilement, sans décalage possible.
Bon travail !
ça, on en aura besoin, si tu reviens un de ces jours avec des questionsJe peux peut-être expliquer l'utilité de toutes ces formules
Sujet résolu en passant par des macros.