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

R

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 …
 

Pièces jointes

  • navigation-erreurs 1.xlsm
    18.6 KB · Affichages: 13
Trier par date Trier par votes
R

richard31

XLDnaute Occasionnel
richard31 à dit:
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 …
Cliquez pour agrandir...


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
 
Vote positif 0 Vote négatif
Dranreb

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
 
Vote positif 0 Vote négatif
R

richard31

XLDnaute Occasionnel
Dranreb à dit:
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
Cliquez pour agrandir...

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
 
Vote positif 0 Vote négatif
Dranreb

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:
Vote positif 0 Vote négatif
R

richard31

XLDnaute Occasionnel
Dranreb à dit:
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)
Cliquez pour agrandir...

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 ....
 
Vote positif 0 Vote négatif
Dranreb

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
 
Vote positif 0 Vote négatif
R

richard31

XLDnaute Occasionnel
Dranreb à dit:
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
Cliquez pour agrandir...
 
Vote positif 0 Vote négatif
Dranreb

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
 
Vote positif 0 Vote négatif
R

richard31

XLDnaute Occasionnel
Dranreb à dit:
Quelle formule avec #REF! pourrait bien ne pas valoir #REF! ???
Cliquez pour agrandir...
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
 
Vote positif 0 Vote négatif
R

richard31

XLDnaute Occasionnel
Dranreb à dit:
Oui mais que vaut la cellule avec cette formule alors ?
Ah oui, vu. SIERREUR devant évidemment.
Cliquez pour agrandir...
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 ..
 
Vote positif 0 Vote négatif

Membres actuellement en ligne

19 membres et 254 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
314 848
Messages
2 113 542
Membres
111 897
dernier inscrit
LARRALDES