macro suppresion #REF!

Version imprimable

Bonjour,

J'essaye d'utiliser une macro simple qui me permet de supprimer automatiquement les #REF! de toutes les cellules contenant des formules avec des erreurs #REF!.
Malheureusement la macro ne marche pas. Elle ne fait rien du tout quand on la lance

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub Suppression_REF()
         Dim nm As Object
 
         ' recherche #REF! dans la feuille active
         For Each nm In ActiveSheet.Names
 
            ' ci trouve #REF! alors
            If InStr(1, nm.RefersTo, "#REF!") Then
 
               ' supprime "REF!
               nm.Delete
 
            End If
         Next
End Sub

Bonjour,

Code:

1
2
For Each nm In ActiveSheet.usedrange
If nm.texte = "#REF!" Then

07/08/2015, 14h50
dubinou

Ce nouveau code provoque une erreur d'exécution 438.

Bonjour,

quelque chose comme ça ?

Code:

1
2
3
4
5
6
Sub toto()
Dim Cell As Range
    For Each Cell In Feuil1.UsedRange.SpecialCells(xlCellTypeFormulas).Cells
        If Cell.Text = "#REF!" Then Cell.Clear ' ou delete, tu adaptes
    Next Cell
End Sub

07/08/2015, 16h04
dubinou

C'est pas complètement ça.
Je voudrais garder le contenu de la cellule et pas supprimer complètement tout son contenu.
Juste supprimer #REF! dans les formules contenu dans les cellules de telle sorte que les formules marche.
07/08/2015, 16h17
joe.levrai

euh ... :weird:

c'est pas une macro qu'il te faut, c'est simplement insérer une condition dans ta formule pour tester si elle est en erreur !

exemple

Code:

=SIERREUR(MOYENNE(A1;A4;A6);"")
07/08/2015, 16h27
dubinou

C'est un classeur avec plein de formule dont certaine colonne peuvent être supprimer par une macro en fonction de différent critère.
A chaque fois qu'ne colonne est supprimé toute les formules ce mette en erreur.
Je voudrais juste rajouter une macro pour corriger les formules en supprimer les #REF! du aux colonnes supprimées.
C'est trop laborieux à faire par une formule dans excel. Il y'a trop de case à modifier.
07/08/2015, 16h58
joe.levrai

peux-tu nous donner quelques exemples de formules qui sont dans ton classeur et qui tombent en #REF! quand tu supprimes une colonne ?
quels sont les critères liés à tes suppressions de colonnes ? et pourquoi supprimer des colonnes ?

je crois que tu ne mesures pas le fait que corriger par macro ces effets n'est pas pratique, et sera un peu usine à gaz
sans oublier que tu parles de tonnes de formules ... bonjour le temps de traitement :aie:

Donc, c'est les causes qu'il faut corriger ... il faut résoudre ça en amont
07/08/2015, 18h08
dubinou

1 pièce(s) jointe(s)

Voila le fichier.
Voilà ce que je voudrais faire :
Scanner les formules colonne X et Z. Supprimer les fonctions et ne mettre à la place que la formule valide. (exemple =SI(P3="mat3";(P8*0,8+Q8+S8+U8*1,5)*K8;SI(P4="Type14";O8*K8*L8;(P8*M8+Q8*2)*K8)) et que la valeur en P3 est mat3 alors juste mettre = (P8*0,8+Q8+S8+U8*1,5)*K8
Ensuite supprimer les colonnes et les lignes (bloc en orange mais normalement en blanc sur le fichier d’origine) contenant la valeur 0 ou bien pas de valeur en colonne E,H,J,M,R,U,Z
Et pour finir modifier les formules pour quelle fonctionne sans ces blocs (supprimer le #Ref! des formules).

Je me demande ci c'est pas un peut trop compliqué d'ailleurs

A+
07/08/2015, 21h23
clementmarcotte

Donc, si j'ai bien compris, tu veux tuer le messager au lieu de corriger le problème. M'est avis que tu vas terminer avec un système de créateur d'erreurs en cascade et que les dommages collatéraux vont être considérables.

Et puis ton fichier ne montre aucun #REF, même en mettant mat3 dans P3.

Et puis, comment fait-on pour deviner la bonne formule.

Et puis, si tu veux le fond de ma pensée; tu veux utiliser une technique pourrie, pour arranger un classeur pourri. Ce serait bien plus intelligent de revoir ton classeur d'origine pour éviter tes acrobaties.
07/08/2015, 21h56
joe.levrai

pourquoi ne pas masquer tes lignes/colonnes au lieu de les supprimer ?

Bonjour,

Tu peux tester cette moulinette sur la feuille une fois les colonnes et lignes supprimées, les "*#REF!" et "+#REF!" seront supprimés des formules :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
Sub Test()
 
    Dim Plage As Range
    Dim Cel As Range
    Dim Formule As String
 
    Set Plage = ActiveSheet.UsedRange
 
    For Each Cel In Plage.Cells.SpecialCells(xlCellTypeFormulas)
 
        If InStr(Cel.Formula, "#REF!") <> 0 Then
 
            Formule = Replace(Cel.Formula, "*#REF!", "")
            Formule = Replace(Formule, "+#REF!", "")
            Cel.Formula = Formule
 
        End If
 
    Next Cel
 
End Sub

Hervé.