Formule sous VBA

Version imprimable

02/12/2020, 17h49
Quiro

Formule sous VBA

Bonsoir a tous,

Je vous contact car je bloque sur un sujet.
J'ai un fichier que j'ai cree pour l'equipe pour le suivi d'info.
Les gars me transmettent une feuille de ce classeur chaque semaine (la meme).
La feuille (feuil2) qu'ils m'envoient n'a aucune mise en forme ni rien. Mais cette feuille va recuperer les infos dans la page principale feuil 1 de la maniere la plus simple possible avec la forme A1 =Feuil1!A1 , B1 = Feuil1!B1 etc j'ai la meme formule pour toutes les cellules.
Le probleme est que lorsqu'une ligne est supprimee de la feuille 1 j'ai #REF qui s'affiche sur la feuille 2.
Exemple : on supprime la ligne 21 feuille 1 => #REF apparait a la ligne 21 feuille 2. Mais lorsque j'ecris a nous sur la ligne 21 (22 en fait qui est devenue 21 car la ligne 21 a ete supprimee) bah les infos de la ligne 21 feuille 1 apparaissent sur la ligne 22 de la feuille 2 et la ligne 21 de la feuille 2 continue a afficher #REF

Y'a t'il un moyen d'eviter ce bug.
Ne serait il pas mieux de passer par un macro?

Merci d'avance pour vos reponses
02/12/2020, 18h46
TonyNiort79

Bonsoir à Tous,
c'est normal qu'il affiche une erreur car le lien n'existe plus,
ce qu'il faudrait c'est la même formule mais en indirect

Code:

=INDIRECT("Feuil1!A1")

tout ce qui se trouve entre les " " est du texte, comme cela le lien ne sera plus supprimé
02/12/2020, 19h14
Quiro

Salut Tony,

Merci, ca marche nickel.
Il me semblait bien qu'il fallait utiliser la formule indirect mais j'oubliais les guillemets

Par contre en mettant les guillemet je ne peux pas etendre la formule :(
Si je l'etend la formule ne s'incremente pas et je n'ai pas l'option qui permet d'increnter.

J'ai adapte le code pour ne pas avoir un 0 si je n'ai rien en Feuil1

Code:

=SI(INDIRECT("Feuil1!A1")="","",INDIRECT("Feuil1!A1"))
02/12/2020, 19h18
Pierre Fauconnier

Salut.

Il me semble qu'il y a d'autres méthodes plus fiables et plus pratiques pour récupérer les données d'une autre feuille. La fonction =INDIRECT("A1") va poser le chouette problème de devoir être adaptée pour chaque cellule de la plage récupérée... Renommer la feuille va en plus poser de sacrés problèmes... Pour utiliser INDIRECT dans ce cas de figure et notamment pouvoir étendre la formule, il faudrait recomposer l'adresse par formule: =INDIRECT("feuil1!" & ADRESSE(LIGNE();COLONNE())).

Mais de toutes façons, cette manière de faire est à mon sens à bannir. Avec un tag 2019, tu pourrais utiliser Power Query pour récupérer les données d'une feuille, à condition qu'elle soit formée correctement. Le besoin de recourir à ta solution témoigne selon moi d'un problème de ~~connexion~~ conception.
02/12/2020, 23h37
Quiro

Citation:

Envoyé par Pierre Fauconnier

Salut.

Il me semble qu'il y a d'autres méthodes plus fiables et plus pratiques pour récupérer les données d'une autre feuille. La fonction =INDIRECT("A1") va poser le chouette problème de devoir être adaptée pour chaque cellule de la plage récupérée... Renommer la feuille va en plus poser de sacrés problèmes... Pour utiliser INDIRECT dans ce cas de figure et notamment pouvoir étendre la formule, il faudrait recomposer l'adresse par formule: =INDIRECT("feuil1!" & ADRESSE(LIGNE();COLONNE())).

Mais de toutes façons, cette manière de faire est à mon sens à bannir. Avec un tag 2019, tu pourrais utiliser Power Query pour récupérer les données d'une feuille, à condition qu'elle soit formée correctement. Le besoin de recourir à ta solution témoigne selon moi d'un problème de connexion.

Salut Pierre,

Merci pour la reponse

Code:

=INDIRECT("feuil1!" & ADRESSE(LIGNE();COLONNE()))

me renvoi un message d'erreur. Ca ne fonctionne pas.

Je n'ai pas bien compris la fin de ton message par contre. Qu'entends tu par probleme de connexion?

Je sais que tu es un fervent defenseur de Power Query, je suis pas mal d'explication et cours que tu donnes dessus sur d'autres sites mais c'est trop nouveau pour moi. Et surtout je ne pense pas que la feuille soit formee correctement.
J'apprend tout doucement PQ, surement un outils qui me sera indispensable pour la suite

Y'a t'il peut etre une possibilitee de passer par une macro par contre?
03/12/2020, 05h20
Pierre Fauconnier

1 pièce(s) jointe(s)

As-tu bien une feuille nommée Feuil1 dans ton classeur? Il faudrait être plus précis sur "ça ne fonctionne pas" et préciser quand tu as le message d'erreur (validation? en réponse dans la cellule?) et ce qu'il dit.

Pièce jointe 585642

Citation:

Envoyé par Quiro

[...]
Je n'ai pas bien compris la fin de ton message par contre. Qu'entends tu par probleme de connexion?[...]

Désolé, je voulais dire de conception.

Citation:

Envoyé par Quiro

[...]
Je sais que tu es un fervent defenseur de Power Query, [...]je ne pense pas que la feuille soit formee correctement.[...]

C'est pourquoi je pense à un problème de conception ;)

Citation:

Envoyé par Quiro

[...]
Y'a t'il peut etre une possibilitee de passer par une macro par contre?

Sans en savoir plus sur ta feuille de départ et ce que tu souhaites à l'arrivée, il est impossible de te répondre. pourquoi ne pas travailler directement sur base de la feuille plutôt que de la "recopier" par formules sur une autre. Je ne comprends pas trop bien le but de la manoeuvre...
03/12/2020, 13h16
Quiro

Salut Pierre,

En fait, j'ai le message classique d'excel qui s'affiche : "Nous avonc rencontre une erreur dans cette formule ...."

Pour reprendre : ce que j'ai appele la feuille 1 est en fait la feuille planning ou les gars font le suivi de leur boulot. Chacun a son propre fichier
Le probleme c'est que les gars a chaque fois detruisent le fichier en faisant des copier coller, en supprimant des formules dessus, en y ajoutant leur propre mise en forme, en masquant des colonnes etc...
Ce planning doit m'etre envoye chaque semaine afin que je puisse compiler les donnees de tout le monde et c'est la que ca devient un cauchemard car ca me prend enormement de temps.

Le but c'est de recuperer toutes ces donnees mais de facons brutes dans un autre onglet "Lead" standard qui me sera envoye chaque semaine de facon a ce que la compilation soit rapide et que je puisse derriere peut etre l'automatiser.

Je sais pas si je suis assez clair. Je n'envois pas le fichier car beaucoup n'acceptent pas de l'ouvrir mais je peux le faire si tu veux.

Encore merci a toi car ce n'est pas la premiere fois que tu m'aides