Probléme #REF après suppression de lignes

[LocSta]

Salut à tous, j'ai un souci avec un de mes classeurs excel.
Une de mes feuilles fait appel à une autre feuille via des formules. Dans cette autre feuille il y a une macro qui permet de supprimer certaine ligne. Le problème c'est qu'une fois ces lignes supprimer ma premiére feuille ne trouve plus les bonnes cellules et me marque dont #REF comme résultat.

Ma formule avant suppression des lignes dans l'autre feuille:
=SI(OU('Archives Finale'!F53="";'Archives Finale'!F53=0);"";'Archives Finale'!F53)

Ma formule aprés suppression des lignes dans l'autre feuille:
=SI(OU('Archives Finale'!#REF!="";'Archives Finale'!#REF!=0);"";'Archives Finale'!#REF!)

Y a t'il une solution pour résoudre ce probléme?
Merci.

 

[ballmaster]

Re : Probléme #REF après suppression de lignes

Bonjour Locsta,

tes 2 classeurs sont-ils ouverts en même temps lorsque ta macro tourne?
Car, il se peut que dans ce cas, excel "perde" les références de cellules.

Tu devrais alors faire tourner ta macro dans ton 1er classeurs, puis ouvrir ton 2ème classeur et effectuer la MAJ de ta liaison.

 

[LocSta]

Re : Probléme #REF après suppression de lignes

Les 2 feuilles sont dans le meme classeur, une fois que la macro (situé dans la feuille 2) à fini de faire sont travail dans la feuille 2, les formules de la feuille n°1 de sont plus correcte (voir 1er message). Est t'il possible de faire en sorte que ça ne modifie pas les formules de ma feuille n°1 car elle est protégé et donc ne peut pas être mis à jours par les utilisateurs ne diposant pas du mot de passe.

 

[Monique]

Re : Probléme #REF après suppression de lignes

Bonjour,

à essayer

=SI(INDEX(Feuil2!A:A;LIGNE())=0;"";INDEX(Feuil2!A:A;LIGNE()))

INDEX(Feuil2!A:A;LIGNE())
fait référence à la cellule A10 de la feuille 2 quand la formule est en ligne 10

INDEX(Feuil2!A:A;LIGNE()-10)
fait référence à la cellule A20 de la feuille 2 quand la formule est en ligne 30

ou bien simplement
=INDEX(Feuil2!A:A;LIGNE())
avec un format de nombre Standard;; pour ne pas voir les 0

 

[ballmaster]

Re : Probléme #REF après suppression de lignes

Bonjour Monique,

Locsta tu peux dans ton code rajouter ceci :

Application.Calculation = xlManual

Peut-être que cela permettra d'éviter ce problème de perte d'info.

 

[LocSta]

Re : Probléme #REF après suppression de lignes

Aucune de ces deux solutions ne fonctionne, avec la formule Index ça fait la meme chose. Enfét dés que je supprime une ligne dans ma feuille 2 un de mes formules va etre modifier 'Archives Finale'!D8! => 'Archives Finale'!#REF!. C'est la formule en elle meme qui est modifiée et non pas uniquement le résultat de cette formule.
Aidez moi, je galére

 

[Tibo]

Re : Probléme #REF après suppression de lignes

Bonjour,

Il semble que le problème doit survenir lorsque ta macro supprime la ligne 53 de ton onglet 'Archives Finale'

Ce qui est plutôt logique, puisque Excel ne retrouve plus la trace de cette ligne 53.

Pour ma part, je serais parti sur une formule identique à celle de Monique.

Pour qu'on soit en mesure de t'aider efficaceent, il faudrait que tu joignes un extrait de ton fichier (sans données confidentielles)

@+

 

[plasmadav]

Re : Probléme #REF après suppression de lignes

je suis preneur aussi d'une solution egalement, je suis dans le meme cas de figure que locsta

 

[LocSta]

Re : Probléme #REF après suppression de lignes

Salut Tibo et plasmadav
J'ai essayer avec la formule de Monique, mais ça ne marche pas
Il m'est difficile de joindre mon fichier car il y a beaucoup de liaisons avec plusieurs feuilles, je passerais trop de temps pour le modifier pour pourvoir le mettre ici.
Dans ma feuille "Principale" j'ai par exemple en
D25 => "=INDEX('Archives Finale'!D:AM;LIGNE('Archives Finale'!D17);(COLONNE('Archives Finale'!D17)-3))"
Qui après suppression d'une ligne dans dans la feuille "Archives Finale" devient => "=INDEX('Archives Finale'!D:AM;LIGNE('Archives Finale'!#REF!);(COLONNE('Archives Finale'!#REF!)-3))"
J'utilise pourtant les formules INDEX qui ne font pas appele directement aux cellules mais à leurs emplacements. Le probléme viens donc de la modification de la formule, pourquoi la formule est telle modifiée alors que la macro n'effectue aucun travail sur cette feuille

 

[wilfried_42]

Re : Probléme #REF après suppression de lignes

Bonjour à tous

peut etre que je vais dire une betise mais je la dit quand meme
la cellule D17, quelle soit sur une feuille ou sur une autre c'est toujours la cellule D17

=INDEX('Archives Finale'!D:AM;LIGNE(D17);(COLONNE(D17)-3))

etant donné que la matrice est bien definie dans la bonne feuille
Ligne de D17 donnera 17 aussi bien sur une feuille que sur une autre

 

[Monique]

Re : Probléme #REF après suppression de lignes

Bonjour,

Si on supprime la ligne 17 de la feuille "Archives finale",
LIGNE('Archives Finale'!D17) et COLONNE('Archives Finale'!D17) n'existent plus

à essayer :
=INDEX('Archives Finale'!D:AM;LIGNE()-8;COLONNE()-3)
C'est LIGNE()-8 et COLONNE()-3 de la feuille où est la formule

Si la formule est en D25 de la feuille "Principale",
elle récupère le contenu de la ligne(D25)-8 soit 17 et de la colonne(D25)-3 soit la 1ère colonne de la zone D : AM
donc le contenu de D17 de la feuille "Archives Finale"

 

[LocSta]

Re : Probléme #REF après suppression de lignes

Ok merci, j'me sent un peu con sur ce coup, j'y est meme pas penser.
Ca va surement marcher maintenant
J'ai penser à une autre solution au passage
La fonction RECHERCHEV avec le N°de ligne (qui se met à jours automatiquement) tout simplement.
Merci à tous, je vais essayer ça.

 

[LocSta]

Re : Probléme #REF après suppression de lignes

Ca marche merci Monique, j'ai fais avec ta méthode.
Vous devriez vous faire payer par le support technique Microsoft Office. Bonne journée.