Problème #VALEUR à l'ouverture du tableur avec formule INDIRECT

[Shinrei]

Bonjour à tous,

Après des recherches infructueuses et sans réponse concrète jusqu’à maintenant, je me tourne vers vous pour obtenir votre avis sur le problème que je rencontre.
Dans un premier temps, voici le tableur EXCEL :

Base de données - CC suisses.xlsm (96,0 Ko)

Quelques mots de contexte : Il s’agit d’un tableur EXCEL construit sur deux onglets : un formulaire sur le 1er et un tableau de données sur le 2nd.
Le tableau recueil des informations afférentes à des centres commerciaux en Suisse, que nous collectons dans le cadre de notre métier (j’ai effacé une partie des données, malheureusement confidentielles et qui n’auront de toute façon pas d’importance quant au problème rencontré).

L’objectif du tableau : Trier les différents centres commerciaux par nombre d’enseignes, par visiteurs à l’année etc… et d’en faire un classement (d’où la 1ère colonne).

Cette première colonne est donc importante et c’est là que réside mon problème. Elle est construite,à partir de la 3ème ligne, avec la formule suivante : =INDIRECT(ADRESSE(LIGNE()-1;COLONNE()))+1. Pour que lorsque l’on insère une nouvelle ligne avec le formulaire dans le tableau, le classement se mette à jour.

Tout fonctionne très bien, à l’exception de la chose suivante : systématiquement à l’ouverture du fichier, les cellules qui comportent la formule indiquée ci-dessus, ont un #VALEUR. (Vous le verrez en ouvrant le fichier).
Pourtant un simple double-clic sur l’une de ces cellules avec #VALEUR et appuyer sur Entrée, suffit pour recalibrer tout le classement. Comme si la formule avait besoin d’être manuellement validée…

Pour info, si cela est nécessaire ou peut-être utile, vous trouverez ci-dessous la petite macro avec laquelle le formulaire fonctionne :

Sub XT()
’
’ XT Macro
’

’
Range(« G6 »).Select
Sheets(« Tableau CC suisses »).Select
Rows(« 6:6 »).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range(« A7 »).Select
Selection.Copy
Range(« A6 »).Select
ActiveSheet.Paste
Range(« B6 »).Select
Sheets(« Formulaire nouveau centre »).Select
Range(« E3:K3 »).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(« Tableau CC suisses »).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets(« Formulaire nouveau centre »).Select
Range(« L3 »).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(« Tableau CC suisses »).Select
Range(« K6 »).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(« I7 »).Select
Application.CutCopyMode = False
Selection.Copy
Range(« I6 »).Select
ActiveSheet.Paste
Range(« J7 »).Select
Application.CutCopyMode = False
Selection.Copy
Range(« J6 »).Select
ActiveSheet.Paste
Range(« N6 »).Select
Application.CutCopyMode = False
ActiveWorkbook.Save
Rows(« 6:6 »).Select
End Sub

Ma question… ( ) : comment puis-je faire pour que le classement en colonne A de mon tableur apparaisse normalement dès l’ouverture du fichier ?

Je fais avec le peu de connaissance en EXCEL que j’ai, et là, ça me dépasse !

Bonne fin d’après-midi à tous,

Shinrei

[FFO]

Bonjour

J’ai aucun souci de la sorte à l’ouverture de ton fichier aucun #VALEUR colonne A

Dans les options d’excel /Formules as tu bien côché rubrique « Mode de calcul » sous rubrique « Calcul du classeur » l’option « Automatique » ?

Peut être une piste à explorer

Chez moi aucun souci

[Shinrei]

Bonjour FFO,

Merci pour ton retour !

Oui je te confirme que cette rubrique est bien en « Automatique », cf les deux PJ.
Pourtant j’ai tout de même ces #VALEUR, comme tu le verras aussi sur la PJ

[FFO]

C est obligatoirement lié à ton Excel et non à ton fichier car moi je n ai aucun souci
As tu possibilité de l ouvrir à partir d une autre PC pour constater la présence ou non de cette anomalie ?

[Shinrei]

Bonjour FFO,

Je viens de transférer ce fichier à deux de mes collègues, ils ont également ce problème de #VALEUR.

J’ai pensé que cela pouvait venir de ma version d’EXCEL qui n’est pas la plus récente mais apparemment non. Je suis sous la version 16.41 et ils sont sous la version 16.53.

Peut-être que tu as une version différente de nous ?

[FFO]

Bonjour
Moi je suis sur office 2019 version famille et etudiants très basique et aucun souci à l ouverture de ton fichier
Je ne peux t en dire plus n ayant pas le défaut
Desolé

[Shinrei]

Merci de ton retour,

Pas de soucis, ça reste une piste à explorer.

Si d’autres personnes passent par là et peuvent confirmer s’ils ont les #VALEUR ou non ainsi que leurs versions d’EXCEL, ça peut m’éclairer.

Bonne journée

[FFO]

Essaies avec ce fichier dans lequel j’ai rajouté à l’ouverture du classeur un petit code qui rafraichi celui-ci à son ouverture

Peut être du mieux

C’es à tenter

Base de données - CC suisses.xlsm (97,3 Ko)

[Shinrei]

Je viens d’essayer d’ouvrir ton fichier, voici ce que j’ai comme message :

Puis en cliquant sur « Oui » :

Et en cliquant sur « Afficher », il m’ouvre un fichier open office avec un message d’erreur

Edit :
1) Le problème relaté dans le message ci-dessus survient parce que le Mac n’aime pas les fichiers Excel partagé sur réseau public.

2) Le problème du post a été réglé en incorporant le code suivant dans ThisWorkBook :

Private Sub Workbook_Open()
Worksheets(« Tableau CC suisses »).Calculate
End Sub

Je pense que c’est ce code que tu avais ajouté FFO ?

[FFO]

Tu es sur open office ?

[FFO]

Non ce n’est pas tout à fait ces lignes que j’ai intégré c’est celles ci plus générales quant au rafraichissement du contenu du fichier

Private Sub Workbook_Open()
ThisWorkbook.RefreshAll
End Sub

Les lignes que tu donnes s’adressent uniquement au rafraichissement des formules

Le souci de la colonne A avec ce résultat « #VALEUR » est il résolu avec ce rajout de lignes de code dans le ThisWorkBook ?

[taz067]

bonjour,
idem aucune alerte de #valeur avec le fichier original et je suis sous Office365 sur PC

[Shinrei]

Oui je te confirme que les lignes dans mon edit fonctionnent bien, et règlent le problème des #VALEUR.

J’ai testé également ton code pour le rafraîchissement, mais malheureusement il ne résout pas le problème.

J’en reviens donc à mon 1er post, tout cela me donne l’impression que les formules qui finissent en #VALEUR ont besoin d’être manuellement validée (ou via le code dans ThisWorkBook) pour fonctionner. C’est très étonnant …

[Shinrei]

Bonjour,

Merci pour ton retour,

Peux-tu me dire sous quelle version exacte d’Office365 ?

En ayant testé sous 16.41 et 16.53, le problème est toujours présent (sans le code ThisWorkBook vu avec FFO)

[FFO]

« J’ai testé également ton code pour le rafraîchissement, mais malheureusement il ne résout pas le problème. »

Testé comment ?

Ces lignes sont mises dans le ThisWorkBook pour s exécuter automatiquement à l ouverture du fichier sans aucune intervention manuelle

C est ce que j ai mis en place dans le dernier fichier que je t ai transmis
Que donne t il à l ouverture ?
Toujours colonne A la même anomalie ?

[taz067]

[Shinrei]

J’ai mis les lignes ici, puis j’ai enregistré et fermé le document.

À la réouverture, les #VALEUR étaient toujours présents.

En revanche, avec les lignes ci-après, le problème est bien résolu :

[FFO]

OK donc je n ais pas utilisé là bonne instruction

RefreshAll rafraîchit l ensemble des données du classeur
Calculate ne rafraichit que les formules
N ayant pas l anomalie donc ne pouvant en cerner l origine j ai cible large bien mal m en a pris
L essentiel est que tu ais la bonne ligne de code pour bien fonctionner
Donc tout est OK maintenant avec les lignes de code qui solutionnent le #VALEUR (Calculate)
Merci de confirmer

[Shinrei]

Je te confirme que tout est OK, le problème est effectivement bien résolu.

Merci beaucoup pour ton temps et ton aide !

[FFO]

Merci pour ton retour
Bon courage à et peut être au plaisir de te lire à nouveau
Valide la bonne réponse
A la prochaine