Discussion :

[halaster08]

Bonjour,

Dans des fichiers que je fourni il y a des contrôles (cellules qui renvoient "ok" ou "pb" suivant un test), sur certains fichiers il y a beaucoup de feuilles avec des contrôles sur chaque feuilles. J'aurai aimé faire un controle maître qui vérifient tout les autres contrôles.
Pour ça je dois parcourir toutes mes cellules de contrôles sur toutes mes feuilles, j'ai essayé le code ci-dessous mais il ne fonctionne pas.
J'obtient le message "méthode union fail", que je n'obtient pas si je ne travaille que sur une seule feuille.
Suis-je obligé faire une boucle par feuille? Ou existe-t-il un moyen de grouper des cellules de différentes feuilles?

Merci d'avance.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim cell As Range
Dim test As Boolean
test = False
For Each cell In Union(Sheets("Sh1").Range("F139,H139"), Sheets("Sh2").Range("F178,H178"), Sheets("Sh3").Range("R2:W2"))
    If Not (cell = "ok") Then test = True
Next cell
If test Then
    Range("ctrlM") = "pb"
    MsgBox "Attention les contrôles ne sont pas bons"
Else
    Range("ctrlM") = "ok"
End If
End Sub

[joe.levrai]

Bonjour,

il est impossible de réunir des plages de feuilles différentes

le mieux à faire est de créer une procédure munie d'un paramètre "Worksheet" qui fait le boulot sur une feuille, et que tu appelles dans une boucle qui appelle la procédure sur chaque feuille souhaitée

Pourquoi pas d'ailleurs plutôt en faire une fonction perso, qui renvoie un Booléen (True = pas de PB, False = anomalie), tu pourras même, ainsi, t'offrir le luxe de dire à l'utilisateur la/les feuille(s) où il y a une anomalie à corriger.


Ps : pas besoin de parcourir chaque cellule, utilises un NB.SI pour calculer directement si c'est ok sur l'ensemble de la plage

[Menhir]

Je n'ai pas testé, il se peut donc qu'il y ait un peu de débuggage à faire.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Zone(3) As Range, ZZ As Integer
Dim test As Boolean
Set Zone(1) = Sheets("Sh1").Range("F139,H139")
Set Zone(2) = Sheets("Sh2").Range("F178,H178")
Set Zone(3) = Sheets("Sh3").Range("R2:W2"))
 
test = False
For ZZ = 1 To 3
    If Not Zone(ZZ).Find("Pb") Is Nothing Then test = True
Next ZZ
 
Range("ctrlM") = "ok"
If test Then
    Range("ctrlM") = "pb"
    MsgBox "Attention les contrôles ne sont pas bons"
End If
End Sub

[halaster08]

Merci pour les réponses, j'avance mais du coup j'ai d'autres question.
J'ai nommé mes plages "ctrl_i" où i est l'index de la feuille
Et j'essaye de créer des zones comme dans le code de Menhir. Mais toutes les zones sont a Nothing
Sachant que ctrl_i n'existe pas pour toute les valeurs de i, je met on error resume next pour passer ces cas, mais j'ai l'impression qu'il passe toute la boucle. j'ai essayé avec range("ctrl_10") qui existe ça marche.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Dim Zone() As Range, ZZ As Integer
ReDim Zone(ThisWorkbook.Sheets.Count)
For ZZ = 1 To ThisWorkbook.Sheets.Count
On Error Resume Next
    Set Zone(i) = Range("crtl_" & i)
On Error GoTo 0
Next

Comment pourrais-je enlever le on error resume next? j'ai essayé de tester Range("crtl_" & i) is nothing mais ça me met quand meme une erreur.

Ps : pas besoin de parcourir chaque cellule, utilises un NB.SI pour calculer directement si c'est ok sur l'ensemble de la plage

oui j'ai utiliser ceci une fois,

=IF(COUNTIF(R2:W2;"ok")/COUNTA(R2:W2)=1;"ok";"pb")

Autre question: est-il possible de boucler sur toutes mes plages nommés?

[joe.levrai]

Une plage nommée dispose d'une propriété Name appartenant à la collection Names
de là, boucler dessus, c'est comme boucler sur les objets Worksheet de la collection Worksheets

[halaster08]

Super, merci comme ça je peux nommer mes zones sans erreur

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
Dim Zone() As Range, ZZ As Integer
ReDim Zone(ThisWorkbook.Sheets.Count)
ZZ = 1
For Each pln In ThisWorkbook.Names
     If Left(pln.Name, 5) = "ctrl_" Then
     Set Zone(ZZ) = Range(pln.Name)
     ZZ = ZZ + 1
     End If
Next

[Menhir]

Sachant que ctrl_i n'existe pas pour toute les valeurs de i, je met on error resume next pour passer ces cas

Dans 99% des cas, mettre un On Error est une mauvaise idée.
C'est masquer un problème sans le résoudre. Et généralement, le problème finit par te rattraper.
Autant faire quelque chose de propre.

[halaster08]

Je sais, mais quand j'ai pas d'autres idées je tente, le temps de trouver un vrai solution.

Merci encore pour votre aide, j'ai pu réalisé ce que je voulais
Pour nommer mes plages de contrôles (a ne relancer que si il y a eu des modif dans le fichiers)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sub Nommer_zone_controle()
Dim cell As Range
Dim ctrl As String
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If Not sh.Name = "TO DO" Then
    ctrl = ""
    sh.Activate
        For Each cell In sh.UsedRange
            If cell.Text = "ok" Or cell.Text = "pb" Then ctrl = ctrl & "," & cell.Address
        Next cell
    If Len(ctrl) > 1 Then
    ctrl = Right(ctrl, Len(ctrl) - 1)
    sh.Range(ctrl).Name = "ctrl_" & sh.Index
    End If
End If
Next sh
 
End Sub

Test global:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Sub verif_globale()
Dim Zone() As Range, ZZ As Integer, i As Integer
Dim test As Boolean
ReDim Zone(ThisWorkbook.Sheets.Count)
test = False
ZZ = 1
For Each pln In ThisWorkbook.Names
     If Left(pln.Name, 5) = "ctrl_" Then
     Set Zone(ZZ) = Range(pln.Name)
     ZZ = ZZ + 1
     End If
Next
For i = 1 To ZZ - 1
    If Not Zone(i) Is Nothing Then
        If Not Zone(i).Find(What:="pb", LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
        , SearchFormat:=False) Is Nothing Then test = True
    End If
Next
 
If test Then
    Range("ctrlM") = "pb"
    MsgBox "Attention les contrôles ne sont pas bons"
Else
    Range("ctrlM") = "ok"
End If
End Sub

Test par feuille:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Function verif(sh As Worksheet) As Boolean
Dim test As Boolean
Dim pln As Name
 
test = False
 
For Each pln In ThisWorkbook.Names
    If pln.Name = "ctrl_" & sh.Index Then
        If Not Range("ctrl_" & sh.Index) Is Nothing Then
            If Not Range("ctrl_" & sh.Index).Find(What:="pb", LookIn:=xlValues, LookAt:= _
            xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
            , SearchFormat:=False) Is Nothing Then test = True
        End If
    End If
Next pln
 
 
verif = test
End Function
 
Sub test_par_feuille()
Dim sh As Worksheet, msg As String
For Each sh In ThisWorkbook.Sheets
    If verif(sh) Then msg = msg & "Attention les contrôles de la feuille " & sh.Name & " ne sont pas bons" & Chr(10)
Next sh
If Len(msg) > 1 Then
    MsgBox msg
    Range("ctrlM") = "pb"
Else
    Range("ctrlM") = "ok"
End If
End Sub

[halaster08]

Une version 2 du test où je compte le nombre de "ok" plutot que de chercher les "pb", je n'ai malheureusement pas réussi a utiliser countif, a cause (je crois) de la discontinuité de mes plages de contrôles. Du coup je parcours cellule par cellule mais c'est quand même rapide.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Function verif2(sh As Worksheet) As Boolean
Dim test As Boolean
Dim pln As Name
Dim cell As Range, ct As Long
 
test = False
 
For Each pln In ThisWorkbook.Names
    If pln.Name = "ctrl_" & sh.Index Then
        ct = 0
        For Each cell In Range("ctrl_" & sh.Index)
            If cell.Text = "ok" Then ct = ct + 1
        Next cell
        If Not (ct = Range("ctrl_" & sh.Index).Count) Then test = True
    End If
Next pln
 
 
verif2 = test
End Function