XL 2016 RESOLU. VBA aide adaptation code VBA de rechercher d’erreurs dans une feuille

richard31

XLDnaute Occasionnel
Bonjour!

j’ai trouvé un code VBA qui permet de rechercher les erreurs dans un classeur et d’afficher ensuite une fenêtre qui liste les cellules en erreurs. Voici le lien très bien expliqué avec deux vidéos (je suis mauvais en VBA ^^):http://www.xlerateur.com/divers/2016/11/24/vba-ajouter-un-outil-de-recherche-derreur-5426/

Le souci est que cela ne fonctionne pas si on à des codes d’erreurs Excel classiques (#NUL!
#DIV/0!
#VALEUR!
#REF!
#NOM?
#NOMBRE!
#N/A

L’utilisateur ne scanne que le MOT “erreur” c’est bête car je trouve ça super ! SI on à un gros fichier pour s’assurer qu’il n’y à pas d’erreurs de formules liens ou autre.

Qui serai le regarder pour qu’en fait il scanne tous les classeurs et identifie les erreurs ( en indiquant la feuille , la ou les cellules ) ce que fait cet outil mais pas correctement …
 

Fichiers joints

richard31

XLDnaute Occasionnel
Bonjour!

j’ai trouvé un code VBA qui permet de rechercher les erreurs dans un classeur et d’afficher ensuite une fenêtre qui liste les cellules en erreurs. Voici le lien très bien expliqué avec deux vidéos (je suis mauvais en VBA ^^):http://www.xlerateur.com/divers/2016/11/24/vba-ajouter-un-outil-de-recherche-derreur-5426/

Le souci est que cela ne fonctionne pas si on à des codes d’erreurs Excel classiques (#NUL!
#DIV/0!
#VALEUR!
#REF!
#NOM?
#NOMBRE!
#N/A

L’utilisateur ne scanne que le MOT “erreur” c’est bête car je trouve ça super ! SI on à un gros fichier pour s’assurer qu’il n’y à pas d’erreurs de formules liens ou autre.

Qui serai le regarder pour qu’en fait il scanne tous les classeurs et identifie les erreurs ( en indiquant la feuille , la ou les cellules ) ce que fait cet outil mais pas correctement …

avec l aide d'un collègue on à trouvé et c est top ça scanne tout le fichier et remonte les erreurs. Le bon code est le suivant :

Sub AfficherUF_Erreur()

Dim wksFeuille As Worksheet
Dim rgeCellule As Range

For Each wksFeuille In ActiveWorkbook.Worksheets
For Each rgeCellule In wksFeuille.UsedRange
If IsError(rgeCellule.Value) Then
UF_Erreurs.ListBoxErreurs.AddItem "ERREUR"
UF_Erreurs.ListBoxErreurs.List(UF_Erreurs.ListBoxErreurs.ListCount - 1, 1) = wksFeuille.Name
UF_Erreurs.ListBoxErreurs.List(UF_Erreurs.ListBoxErreurs.ListCount - 1, 2) = rgeCellule.Address
End If
Next
Next

UF_Erreurs.Show 0
End Sub
 

Dranreb

XLDnaute Barbatruc
Bonjour.
Dans module1 :
VB:
Option Explicit

Sub AfficherUF_Erreur()
   Dim Wsh As Worksheet
   Dim RngErr As Range, Cel As Range
   For Each Wsh In ActiveWorkbook.Worksheets
      On Error Resume Next
      Set RngErr = Wsh.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
      If Err = 0 Then
         For Each Cel In RngErr
            UF_Erreurs.ListBoxErreurs.AddItem Choose((CLng(Cel.Value) - 1993) \ 7, _
               "#NUL!", "#DIV/0!", "#VALEUR!", "#REF!", "#NOM?", "#NOMBRE!", "#N/A")
                UF_Erreurs.ListBoxErreurs.List(UF_Erreurs.ListBoxErreurs.ListCount - 1, 1) = Wsh.Name
                UF_Erreurs.ListBoxErreurs.List(UF_Erreurs.ListBoxErreurs.ListCount - 1, 2) = Cel.Address(False, False)
            Next Cel: End If: Next Wsh
   If UF_Erreurs.ListBoxErreurs.ListCount = 0 Then
      MsgBox "Aucune cellule en erreur trouvée dans ce classeur", vbInformation, "Voir les erreurs"
   Else: UF_Erreurs.Show 0: End If
   End Sub
Dans UF_Erreurs :
VB:
Option Explicit
Private Sub ListBoxErreurs_Click()
Dim L As Long
L = ListBoxErreurs.ListIndex
Application.Goto ActiveWorkbook.Worksheets(ListBoxErreurs.List(L, 1)).Range(ListBoxErreurs.List(L, 2))
End Sub
 

richard31

XLDnaute Occasionnel
Encore mieux !!!

ça donne le nom de l'erreur, l'emplacement sur la feuille, et le nom de la cellule ! C'est TOP ;)

Meri beaucoup beaucoup !
 

richard31

XLDnaute Occasionnel
Bonjour.
Dans module1 :
VB:
Option Explicit

Sub AfficherUF_Erreur()
   Dim Wsh As Worksheet
   Dim RngErr As Range, Cel As Range
   For Each Wsh In ActiveWorkbook.Worksheets
      On Error Resume Next
      Set RngErr = Wsh.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
      If Err = 0 Then
         For Each Cel In RngErr
            UF_Erreurs.ListBoxErreurs.AddItem Choose((CLng(Cel.Value) - 1993) \ 7, _
               "#NUL!", "#DIV/0!", "#VALEUR!", "#REF!", "#NOM?", "#NOMBRE!", "#N/A")
                UF_Erreurs.ListBoxErreurs.List(UF_Erreurs.ListBoxErreurs.ListCount - 1, 1) = Wsh.Name
                UF_Erreurs.ListBoxErreurs.List(UF_Erreurs.ListBoxErreurs.ListCount - 1, 2) = Cel.Address(False, False)
            Next Cel: End If: Next Wsh
   If UF_Erreurs.ListBoxErreurs.ListCount = 0 Then
      MsgBox "Aucune cellule en erreur trouvée dans ce classeur", vbInformation, "Voir les erreurs"
   Else: UF_Erreurs.Show 0: End If
   End Sub
Dans UF_Erreurs :
VB:
Option Explicit
Private Sub ListBoxErreurs_Click()
Dim L As Long
L = ListBoxErreurs.ListIndex
Application.Goto ActiveWorkbook.Worksheets(ListBoxErreurs.List(L, 1)).Range(ListBoxErreurs.List(L, 2))
End Sub
le seul truc c'est que cela ne va pas lire les formules qui sont en erreurs ! Si donc il y à #REF dans une formule cela n'est pas détecté . Le seul moyen que j'ai trouvé c'est de faire une recherche .. S'il y avait ça on aurai tout !

Voilu
 

Dranreb

XLDnaute Barbatruc
Ben si, je scanne les valeurs de cellules contenant des formules ! En général une formule dont une expression s'est tranformée en #REF! par exemple est elle même en erreur. Mais pour les constantes, il est possible d'ajouter une séquence supplémentaire très semblable à celle qui existe mais avec cette fois Set RngErr = Wsh.Cells.SpecialCells(xlCellTypeConstants, xlErrors)
 
Dernière édition:

richard31

XLDnaute Occasionnel
Ben si, je scanne les valeurs de cellules contenant des formules ! En général une formule dont une expression s'est tranformée en #REF! par exemple est elle même en erreur. Mais pour les constantes, il est possible d'ajouter une séquence supplémentaire très semblable à celle qui existe mais avec cette fois Set RngErr = Wsh.Cells.SpecialCells(xlCellTypeConstants, xlErrors)
HA bon ? je ne sais pas du tout doué en VB plus en formules :( :( rajouter une séquence ? Donc on met la variable au départ ça ok je comprends mais pas la suite ....
 

Dranreb

XLDnaute Barbatruc
Concrètement je voulais dire ça, même si je l'écrirais sûrement différemment tellement j'ai horreur des répétitions :
VB:
Sub AfficherUF_Erreur()
   Dim Wsh As Worksheet
   Dim RngErr As Range, Cel As Range
   For Each Wsh In ActiveWorkbook.Worksheets
      On Error Resume Next
      Set RngErr = Wsh.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
      If Err = 0 Then
         For Each Cel In RngErr
            UF_Erreurs.ListBoxErreurs.AddItem Choose((CLng(Cel.Value) - 1993) \ 7, _
               "#NUL!", "#DIV/0!", "#VALEUR!", "#REF!", "#NOM?", "#NOMBRE!", "#N/A")
                UF_Erreurs.ListBoxErreurs.List(UF_Erreurs.ListBoxErreurs.ListCount - 1, 1) = Wsh.Name
                UF_Erreurs.ListBoxErreurs.List(UF_Erreurs.ListBoxErreurs.ListCount - 1, 2) = Cel.Address(False, False)
            Next Cel: End If
      Err.Clear
      Set RngErr = Wsh.Cells.SpecialCells(xlCellTypeConstants, xlErrors)
      If Err = 0 Then
         For Each Cel In RngErr
            UF_Erreurs.ListBoxErreurs.AddItem Choose((CLng(Cel.Value) - 1993) \ 7, _
               "#NUL!", "#DIV/0!", "#VALEUR!", "#REF!", "#NOM?", "#NOMBRE!", "#N/A")
                UF_Erreurs.ListBoxErreurs.List(UF_Erreurs.ListBoxErreurs.ListCount - 1, 1) = Wsh.Name
                UF_Erreurs.ListBoxErreurs.List(UF_Erreurs.ListBoxErreurs.ListCount - 1, 2) = Cel.Address(False, False)
            Next Cel: End If
      On Error GoTo 0: Next Wsh
   If UF_Erreurs.ListBoxErreurs.ListCount = 0 Then
      MsgBox "Aucune cellule en erreur trouvée dans ce classeur", vbInformation, "Voir les erreurs"
   Else: UF_Erreurs.Show 0: End If
   End Sub
 

richard31

XLDnaute Occasionnel
Concrètement je voulais dire ça, même si je l'écrirais sûrement différemment tellement j'ai horreur des répétitions :
VB:
Sub AfficherUF_Erreur()
   Dim Wsh As Worksheet
   Dim RngErr As Range, Cel As Range
   For Each Wsh In ActiveWorkbook.Worksheets
      On Error Resume Next
      Set RngErr = Wsh.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
      If Err = 0 Then
         For Each Cel In RngErr
            UF_Erreurs.ListBoxErreurs.AddItem Choose((CLng(Cel.Value) - 1993) \ 7, _
               "#NUL!", "#DIV/0!", "#VALEUR!", "#REF!", "#NOM?", "#NOMBRE!", "#N/A")
                UF_Erreurs.ListBoxErreurs.List(UF_Erreurs.ListBoxErreurs.ListCount - 1, 1) = Wsh.Name
                UF_Erreurs.ListBoxErreurs.List(UF_Erreurs.ListBoxErreurs.ListCount - 1, 2) = Cel.Address(False, False)
            Next Cel: End If
      Err.Clear
      Set RngErr = Wsh.Cells.SpecialCells(xlCellTypeConstants, xlErrors)
      If Err = 0 Then
         For Each Cel In RngErr
            UF_Erreurs.ListBoxErreurs.AddItem Choose((CLng(Cel.Value) - 1993) \ 7, _
               "#NUL!", "#DIV/0!", "#VALEUR!", "#REF!", "#NOM?", "#NOMBRE!", "#N/A")
                UF_Erreurs.ListBoxErreurs.List(UF_Erreurs.ListBoxErreurs.ListCount - 1, 1) = Wsh.Name
                UF_Erreurs.ListBoxErreurs.List(UF_Erreurs.ListBoxErreurs.ListCount - 1, 2) = Cel.Address(False, False)
            Next Cel: End If
      On Error GoTo 0: Next Wsh
   If UF_Erreurs.ListBoxErreurs.ListCount = 0 Then
      MsgBox "Aucune cellule en erreur trouvée dans ce classeur", vbInformation, "Voir les erreurs"
   Else: UF_Erreurs.Show 0: End If
   End Sub
 

Dranreb

XLDnaute Barbatruc
Comme ça c'est un peu plus court :
VB:
Sub AfficherUF_Erreur()
   Dim Wsh As Worksheet
   For Each Wsh In ActiveWorkbook.Worksheets
      On Error Resume Next
      AjoutLbxErr Wsh.Cells.SpecialCells(xlCellTypeConstants, xlErrors), Wsh.Name
      AjoutLbxErr Wsh.Cells.SpecialCells(xlCellTypeFormulas, xlErrors), Wsh.Name
      On Error GoTo 0
      Next Wsh
   If UF_Erreurs.ListBoxErreurs.ListCount = 0 Then
      MsgBox "Aucune cellule en erreur trouvée dans ce classeur", vbInformation, "Voir les erreurs"
   Else: UF_Erreurs.Show 0: End If
   End Sub
Private Sub AjoutLbxErr(ByVal RngErr As Range, ByVal NomFeuil As String)
   Dim Cel As Range
   For Each Cel In RngErr
      UF_Erreurs.ListBoxErreurs.AddItem Choose((CLng(Cel.Value) - 1993) \ 7, _
         "#NUL!", "#DIV/0!", "#VALEUR!", "#REF!", "#NOM?", "#NOMBRE!", "#N/A")
          UF_Erreurs.ListBoxErreurs.List(UF_Erreurs.ListBoxErreurs.ListCount - 1, 1) = NomFeuil
          UF_Erreurs.ListBoxErreurs.List(UF_Erreurs.ListBoxErreurs.ListCount - 1, 2) = Cel.Address(False, False)
      Next Cel
   End Sub
 

richard31

XLDnaute Occasionnel
Quelle formule avec #REF! pourrait bien ne pas valoir #REF! ???
En fait dans certaines formules si le lien n'est plus bon par exemple (vers un fichier externe par exemple) il vient se mettre #REF exemple :
=SIERREUR(SI(ESTVIDE('[Vue_Agent.xls]#REF'!B3);" ";'[Vue_Agent.xls]#REF'!B3);"")
Car j'ai changé le nom de la feuille dans le fichier Vue_Agent.xls par ex. pour voir .. Je sais pas soi c'est possible en VB . je le fait avec la recherche manuellement en sélectionnant les options.
Dans : Classeur
Rechercher : Par ligne
Regarder dans : formules
 

Dranreb

XLDnaute Barbatruc
Oui mais que vaut la cellule avec cette formule alors ?
Ah oui, vu. SIERREUR devant évidemment.
 
Dernière édition:

richard31

XLDnaute Occasionnel
Oui mais que vaut la cellule avec cette formule alors ?
Ah oui, vu. SIERREUR devant évidemment.
oui c'est ça ^^ le si erreur évite d'avoir un fichier tout moche si on doit le donner. Je sais c'est con ce que je cherche ! ;) Mais avec ce petit outil s il fait ça ce serai top sur n'importe quel fichier on serai sur qu'il n'y aucune erreur de liaisons ou de formules ..
 

Dranreb

XLDnaute Barbatruc
Comment est la liaison dans ce cas, menu Données, groupe Connexions, commande Modifier les liaisons ?
 

richard31

XLDnaute Occasionnel
Comment est la liaison dans ce cas, menu Données, groupe Connexions, commande Modifier les liaisons ?
la liaison ne remonte pas en erreur . Il y à que si on va voir la formule. D' ou le souci bien sur . Il faudrait enlever tous les SIERREUR mais je ne peux pas :(
 

richard31

XLDnaute Occasionnel
la liaison ne remonte pas en erreur . Il y à que si on va voir la formule. D' ou le souci bien sur . Il faudrait enlever tous les SIERREUR mais je ne peux pas :(
mais c'est déjà énorme ton coup de main ! ça évite les erreurs de calculs. Et pour le reste c'est assez simple de faire une recherche F5 avec les options d'analyse dans les formules. j'ai enregistré la macro pour mais impossible à rajouter avec le reste ..
 

richard31

XLDnaute Occasionnel
la macro enregistrée donne ça :

Cells.Find(What:="#REF", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate


mais je sais pas la rajouter dans l outil ^^
 

richard31

XLDnaute Occasionnel
merci en tout les cas ! pour le reste ce sera un bouton en plus juste pour rechercher les #REF avec le code ci dessous ça marche ! ^^
 

Discussions similaires


Haut Bas