Comment trouver une formule qui pose problème mais qui ne peut être affichée?

Aloha

XLDnaute Accro
Bonjour,

J'ai ajouté dans un fichier existant un certain nombre de formules et je reçois maintenant tout le temps le message qu'il y a une formule qui ne peut être exécutée mais qu'il est impossible de localiser la cellule qui la contient (bizarre, non?).

Comment faire pour la localiser quand-même?

Merci pour tout tuyau

Aloha
 
Dernière édition:

Dugenou

XLDnaute Barbatruc
Re : Comment trouver une formule qui pose problème mais qui ne peut être affichée?

Bonjour,

Peut-être la cellule est dans une ligne ou une colonne ou une feuille masquée.

Le plus simple serait que tu déposes ton fichier en pj

Cordialement
 

Aloha

XLDnaute Accro
Re : Comment trouver une formule qui pose problème mais qui ne peut être affichée?

Bonjour et merci pour la réponse; je ne pense pas que l'une des hypothèses énoncées est pertinente et malheureusement je ne peux pas joindre le fichier qui est très volumineux et contient beaucoup de données sensibles.

Bonne après-midi

Aloha
 

Dranreb

XLDnaute Barbatruc
Re : Comment trouver une formule qui pose problème mais qui ne peut être affichée?

Bonjour.
Possible qu'une fonction personnalisée s'appuie sans rien dire sur une cellule dont le calcul n'est pas fait ni ne peut être entrepris sans connaître le résultat de cette fonction. Excel reçoit donc une demande de valeur qu'il ne peut fournir sans pouvoir comprendre d'où ça vient dans la jungle des interactions possibles dans VBA...
Cordialement.
 

job75

XLDnaute Barbatruc
Re : Comment trouver une formule qui pose problème mais qui ne peut être affichée?

Bonjour Aloha, Dugenou,

Je ne vois pas comment un tel message peut survenir.

Est-il propre au MAC ? Ou vient-il d'une macro ?

A tout hasard, lancez cette macro qui évalue les formules et signale les erreurs :

Code:
Sub EvaluerFormule()
Dim w As Worksheet, plage As Range, cel As Range, v
On Error Resume Next
For Each w In Worksheets
  Err = 0
  Set plage = w.Cells.SpecialCells(xlCellTypeFormulas)
  If Err = 0 Then
    For Each cel In plage
      Err = 0: v = ""
      v = Evaluate(cel.Formula)
      If Err Or IsError(v) Then _
        MsgBox "Erreur en " & w.Name & "!" & cel.Address(0, 0)
    Next
  End If
Next
End Sub
Edit : salut Dranreb

A+
 
Dernière édition:

Aloha

XLDnaute Accro
Re : Comment trouver une formule qui pose problème mais qui ne peut être affichée?

Bonjour "Dranreb",

Normalement VBA n'est pas en cause dans ce problème, mais une toute bête formule.

Bonjour "job75",

Merci pour le code, qui, malheureusement, ne fonctionne pas, en tout cas pas sur mon Mac (cf. Image7 attachée).


J'ai fait également une copie d'écran du message d'erreur (Image8).

A+

Aloha
 

Fichiers joints

JNP

XLDnaute Barbatruc
Re : Comment trouver une formule qui pose problème mais qui ne peut être affichée?

Bonjour le fil :),
As-tu regardé du côté du gestionnaire de nom :rolleyes: ? Ou des MFC ? Ou bien encore (mais ça va être plus coton) du côté des listes de validation :p ?
Bonne soirée :cool:
 

Dranreb

XLDnaute Barbatruc
Re : Comment trouver une formule qui pose problème mais qui ne peut être affichée?

Bonjour Alohah,
Normalement VBA n'est pas en cause dans ce problème, mais une toute bête formule.
J'ai déjà eu ce problème de référence circulaire un peu vicieuse, chaque fois c'était dû à une interaction assez complexe due à de mauvais callages de cellules dans des appels de fonctions personnalisées.
VBA est presque obligatoirement impliqué car c'est la seule voie qui empêche Excel de faire le compte exact des dépendants et antécédents de cellules lui permettant d'aboutir à l'annonce d'une cellule précise en référence circulaire.
 
Dernière édition:

Aloha

XLDnaute Accro
Re : Comment trouver une formule qui pose problème mais qui ne peut être affichée?

Bonjour "JNP", non je n'ai pas exploré ces pistes. Je le ferai
Bonjour "Dranreb", je vais faire le test en supprimant tout le code

Merci à vous

Bonne soirée

Aloha
 

job75

XLDnaute Barbatruc
Re : Comment trouver une formule qui pose problème mais qui ne peut être affichée?

Bonjour Aloha, le fil, le forum,

Il s'agit donc de détecter une référence circulaire.

Alors essayez cette macro :

Code:
Sub ReferenceCirculaire()
Dim w As Worksheet, plage As Range, cel As Range
On Error Resume Next
For Each w In Worksheets
  Err = 0
  Set plage = w.Cells.SpecialCells(xlCellTypeFormulas)
  If Err = 0 Then
    For Each cel In plage
      If Evaluate(cel.Formula) <> cel Then MsgBox _
        "Référence circulaire en " & w.Name & "!" & cel.Address(0, 0) & " ?"
    Next
  End If
Next
End Sub
Fichier joint.

Nota : si vous avez une erreur de compilation sur votre MAC, précisez l'expression qui coince...

A+
 

Fichiers joints

job75

XLDnaute Barbatruc
Re : Comment trouver une formule qui pose problème mais qui ne peut être affichée?

Re,

La macro du post #10 ne détectait pas dans certains cas la référence circulaire.

Par exemple dans le fichier joint avec cette formule en C9.

En effet l'évaluation de la formule restait égale à la valeur de la cellule.

Utilisez donc cette macro (j'ai peaufiné), qui détecte aussi les valeurs d'erreur :

Code:
Sub ReferenceCirculaire()
Dim w As Worksheet, plage As Range, cel As Range, F$
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlManual
On Error Resume Next
For Each w In Worksheets
  Err = 0
  Set plage = w.Cells.SpecialCells(xlCellTypeFormulas)
  If Err = 0 Then
    For Each cel In plage
      F = cel.Formula
      cel.Formula = F & "&""a"""
      If Evaluate(cel.Formula) <> cel Or IsError(cel) Then MsgBox _
        "Référence circulaire (ou erreur) en " & w.Name & "!" & cel.Address(0, 0)
      cel.Formula = F
    Next
  End If
Next
Application.Calculation = xlAutomatic
Application.EnableEvents = True
End Sub
Edit : avec en C9 la formule circulaire = NBVAL(C:C) la macro du post #10 la détectait, et pas celle-ci.

Il faut tester les 2 macros sur le fichier réel.

A+
 

Fichiers joints

Dernière édition:

Aloha

XLDnaute Accro
Re : Comment trouver une formule qui pose problème mais qui ne peut être affichée?

Bonjour "job75",
J'étais absent du forum pendant un petit moment et je te remercie pour les messages que tu as postés depuis. Je vais m'en occuper la semaine prochaine.
Bon dimanche
Aloha
 

hoerwind

XLDnaute Barbatruc
Re : Comment trouver une formule qui pose problème mais qui ne peut être affichée?

Bonjour,

Une référence circulaire s'affiche dans la barre d'état (juste en-dessous de la barre d'onglets) lorsque la feuille concernée est activée.
En parcourant toutes les feuilles elle devrait normalement s'afficher.

As-tu essayé en cochant "Itérations" sous le menu Outils - Options - Calcul ?
Qu'est-ce que cela donne, encore un message d'erreur ?
 

Aloha

XLDnaute Accro
Re : Comment trouver une formule qui pose problème mais qui ne peut être affichée?

Bonjour "hoerwind",

Lorsque je coche "Itération, et que j'enregistre le fichier (puisque cette option semble liée au fichier !?) et que je le re-charge, il n'y a plus le message d'erreur.
"Itération" est mis par défaut à 100; cette valeur, est-elle bonne?

A+

Aloha
 

Aloha

XLDnaute Accro
Re : Comment trouver une formule qui pose problème mais qui ne peut être affichée?

Re,

Mais lorsque j'ai double-cliqué entre 2 lignes pour adapter la hauteur de la ligne supérieure, le message d'erreur est réapparu; lorsque j'ai répété cette opération sur d'autres lignes, il est encore réapparu!

A+

Aloha
 

hoerwind

XLDnaute Barbatruc
Re : Comment trouver une formule qui pose problème mais qui ne peut être affichée?

Bonjour,

C'est donc bien une référence circulaire qui pose problème.
Le nombre d'itérations (100 ou 1) ne modifie en rien le message.

Mais as-tu parcouru toutes les feuilles pour repérer celle qui affiche dans la barre d'état la référence de la cellule qui contient la référence circulaire ?
 

Dranreb

XLDnaute Barbatruc
Re : Comment trouver une formule qui pose problème mais qui ne peut être affichée?

Bonjour hoerwind,
je rappelle qu'on est dans le cas de référence circulaire numéro deux: le tordu, celui où Excel est dans l'incapacité de trouver la cellule qui pose problème parce que rien ne lui indique, dans les paramètre de sa formule, qu'il a besoin de la valeur d'une cellule encore autre pour la calculer, parce que c'est via VBA qu'il en a besoin.
À+
 

job75

XLDnaute Barbatruc
Re : Comment trouver une formule qui pose problème mais qui ne peut être affichée?

Bonjour le fil,

je rappelle qu'on est dans le cas de référence circulaire numéro deux: le tordu, celui où Excel est dans l'incapacité de trouver la cellule qui pose problème (...)
À+
Bien d'accord la-dessus depuis le début.

Comme la formule ne peut alors être calculée, Excel lui donne normalement une valeur d'erreur.

C'est pour cela que dans mes macros je recherche aussi ces valeurs d'erreur...

A+
 

Haut Bas