Remplacer "#REF!" par "0"

[fire007]

Bonjour à tous,
Je voudrais savoir si quelqu'un connait une petite macro VBA pour remplacer les "#REF!" d'une feuille par des "0"?
En fait le problème est que je vais chercher des valeurs d'un tableau croisé dynamique de la feuille 2 pour les mettre dans la feuille 1, et quand je décoche certaines options du TCD, des lignes sont suppimées et il apparait dans la feuille 1 des "REF!".
Je connais la formule si(esterreur)... mais le problème est que j'ai des centaines de cases...

Merci pour votre aide.

 

[Dull]

Re : Remplacer "#REF!" par "0"

Salut Fire007, le Forum

Un bout de Code à adapter (Feuille et Plage)

Sub EssAi()
Dim C As Range
For Each C In Feuil1.Range("A1:Z1000") 'Feuille et Plage à Modifier
    If IsError(C) Then C = "0"
Next C
End Sub

Bonne Journée

 

[fire007]

Re : Remplacer "#REF!" par "0"

Merci!
Cependant cela marche dans un premier temps, c'est à dire qu'il me remplace bien le "#REF!" par "0", malheureusement la formule qu'il y avait précédemment dans la case (=LIREDONNEESTABCROISDYNAMIQUE("Somme de 1998";.....)) a été remplacée par un 0, ce qui fait que lorsque je rechange les options de mon TCD, les cases où il n'y avait pas de zéro sont bien mises à jour, mais les cases où le "REF!" a été remplacé par le "0" restent à "0".

Là je suis vraiment perdu, je ne vois pas comment je peux bien faire.
Quelqu'un pour m'aider?

Merci

 

[James007]

Re : Remplacer "#REF!" par "0"

Bonjour,

Dommage que ta question initiale soit restée imprécise ... car l'ami Dull 🙂 t'aurait concocté une réponse adaptée ...

Pourquoi n'intégres-tu pas ta condition d'erreur dans ta formule de base ...?

A+
🙂

 

[job75]

Re : Remplacer "#REF!" par "0"

Bonjour fire007, Dull, James,

Ppourquoi n'intégres-tu pas ta condition d'erreur dans ta formule de base ...?

Réponse a déjà été donnée :

Je connais la formule si(esterreur)... mais le problème est que j'ai des centaines de cases...

Si les #REF! viennent de références détruites dans les formules, la seule solution est d'éviter leur suppression.

Mais s'ils proviennent du résultat du calcul de la fonction LIREDONNEESTABCROISDYNAMIQUE alors exécuter cette macro :

Sub CompleteFormule()
Dim cel As Range, F As String
For Each cel In ActiveSheet.UsedRange
  If cel.HasFormula And IsError(cel) Then
    F = Mid(cel.Formula, 2, 9999) 'élimine le signe ="
    cel.Formula = "=IF(ISERROR(" & F & "),0," & F & ")"
  End If
Next
End Sub

Si l'on préfère, on peut remplacer 0 par """" pour avoir un texte vide.

A+

 

[job75]

Re : Remplacer "#REF!" par "0"

Re,

Si l'on veut ne modifier que les formules contenant la fonction LIREDONNEESTABCROISDYNAMIQUE, utiliser :

Sub CompleteFormule()
Dim cel As Range, F As String
For Each cel In ActiveSheet.UsedRange
  If cel.HasFormula And IsError(cel) Then
    F = Mid(cel.FormulaLocal, 2, 9999) 'élimine le signe =
    If InStr(F, "LIREDONNEESTABCROISDYNAMIQUE") Then
      cel.FormulaLocal = "=SI(ESTERREUR(" & F & ");0;" & F & ")"
    End If
  End If
Next
End Sub

Noter qu'on se sert ici de FormulaLocal.

A+

 

[chris]

Re : Remplacer "#REF!" par "0"

Bonjour

Pourquoi ne pas tout simplement mettre dès la formule initiale : SI(ESTERREUR(LIREDONNEESTABCROISDYNAMIQUE...();0;LIREDONNEESTABCROISDYNAMIQUE...())

 

[fire007]

Re : Remplacer "#REF!" par "0"

Pourquoi ne pas tout simplement mettre dès la formule initiale : SI(ESTERREUR(LIREDONNEESTABCROISDYNAMIQUE...();0;L IREDONNEESTABCROISDYNAMIQUE...())

Ce serait une possibilité, mais il me faudrait plusieurs jours pour modifier les centaines de cases dans lesquelles il y a ma formule...

Pour mieux comprendre je vous ai fait un exemple, il se trouve à cette adresse :

Ce lien n'existe plus

1ère étape : Allez dans la feuille "TCD M", allez sur la 3ème colonne, cliquez sur la zone déroulante "Zone Eco" et décochez "EU".
2ème étape : Allez dans la feuille "M" et vous verrez pleins de "#REF!".

Voilà mon problème. Je voudrais que tous ces "#REF!" se transforment en "0", mais je veux également que ce soit réversible, avec par exemple dans le cas où je recoche "EU" cela me redonne mes valeurs initiales et non plus les "0".

Je ne sais pas si c'est possible...
Merci

 

[James007]

Re : Remplacer "#REF!" par "0"

Bonjour à tous 🙂

Job75 t'a donné la réponse que tu attends au message # 6

A +
🙂

 

[fire007]

Re : Remplacer "#REF!" par "0"

Bonjour,
Apparemment cela ne fonctionne pas.
J'ai mis ce code dans le code de la feuille mais celà n'a aucun effet, j'ai toujours les #REF!" qui apparaissent.
Un idée ?
Merci

 

[James007]

Re : Remplacer "#REF!" par "0"

Re,

La solution de Job75 est une macro.... que tu dois copier dans un module standard ...

Ensuite il faut la lancer avec Alt F8 ...


A +
🙂

 

[fire007]

Re : Remplacer "#REF!" par "0"

Ça a l'air de fonctionner.
Merci à tous pour votre aide! 😀