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

[halaster08]

Bon là j'y comprends plus rien, cette macro:

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

postée il y a quelques jours fonctionnait très bien, je l'ai utilisé plusieurs fois sans soucis, j'ai même fais une démo devant mon responsable avant de l'implémenté dans tous les fichiers, et là aujourd'hui non ça ne va plus.
J'ai une erreur ici:

Code :

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

sh.Range(ctrl).Name = "ctrl_" & sh.Index

Que j'ai réussi a résoudre en changeant "," en ";" ici

Code :

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

ctrl = ctrl & "," & cell.Address

Comment c'est possible qu'un jour ça marche et pas le lendemain?

[Philippe Tulliez]

Bonjour,
Sauf si je n'ai rien compris à la lecture de ton post, je suis très étonné que tu aies pu nommer des cellules avec des , ou ;

[halaster08]

Tu as mal compris le nom est "ctrl_1" par exemple (ou 1 est le numéro de la feuille)
Les , ou ; c'est le séparateur que j'utilise pour définir la plage, je cherche toutes cellules qui vérifient une condition, je concatène leurs adresses avec entre chaque un séparateur "," ou ";" et je défini la plage de "ctrl_1" = le range des adresses que j'ai récupérée.

[Menhir]

Tu mets une "," en début de liste ?
A ta place, j'aurais mis un test pour l'éviter :

Code :

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

            If cell.Text = "ok" Or cell.Text = "pb" Then ctrl = ctrl & IIf(Len(ctrl)=0,"",",") & cell.Address

[halaster08]

Tu mets une "," en début de liste ?
A ta place, j'aurais mis un test pour l'éviter :

Je le suprime a la fin, ça évite de faire un test a chaque tour de boucle, et comme je teste de toute façon la longeur pour créer ou pas le nom ça me parraissait logique de le faire là.
Mais le problème n'est pas là (enfin je crois).

[Philippe Tulliez]

Bonjour,

Tu as mal compris le nom est "ctrl_1" par exemple (ou 1 est le numéro de la feuille)

Au temps pour moi, j'ai relu le code et j'ai compris ce que tu souhaitais faire.
Question : Qu'est-ce qui ne fonctionne pas ?
Toutes les cellules contenant les chaînes cherchées ne sont pas référencées ?
Dans l'affirmative et de surcroît sii les chaînes ok et pb sont encodées manuellement il est possible que certaines ont un caractère espacé à droite non visible.
J'ai pour habitude lorsque je teste l'existence d'une chaîne de caractères de la transformer en minuscule et de supprimer les caractères espacés avant et arrière. Voir les fonctions Trim et LCase

[EDIT]
Autre hypothèse.
Comme tu testes la propriété Text de la cellule, il serait possible de passer à côté de cellule dont format personnalisé serait "Le contrôle "@
La propriété Text renvoyant alors "Le contrôle ok" ou "Le contrôle pb", l'adresse de ces cellules ne serait pas prise en compte
Il est plus logique d'utiliser la propriété Value de la cellule qui renvoie comme son nom l'indique la valeur de la cellule.

[halaster08]

Les "ok" et "pb" sont des résultat de formules, il n'y a pas d'espace ou de majuscule ou rien d'autres, ce n'est pas ça qui me pose problème.
Mon problème c'est un problème de syntaxe qui marchait il y a deux jours et qui refuse de marcher aujourd'hui.
Je récupère les adresses des cellules sans souci, je les concatène et a la fin, je définit le nom, exemple

Code :

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

sh.Range("A1,B5").Name = "ctrl_1"

Or depuis tout a l'heure il refuse (erreur sur le range), j'ai tenté de modifié en

Code :

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

sh.Range("A1;B5").Name = "ctrl_1"

Et là ça remarche, je ne comprend pas pourquoi j'ai du changé mes "," en ";"

[Philippe Tulliez]

Bonjour,
Je ne sais pas alors ce qui ne fonctionne pas car j'ai testé ta procédure chez moi et elle fonctionne parfaitement et c'est bien le caractère "," qu'il faut mettre.
Il serait intéressant de nous indiquer quelle ligne se met en erreur.

[halaster08]

Je l'avais indiqué c'est celle-ci:

Code :

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

sh.Range(ctrl).Name = "ctrl_" & sh.Index

avec sh.index = 8 ce qui est normal
et ctrl= "$F$139,$H$139"
edit: avec le message 1004 methode range fail
edit2: dans une autre macro qui marchait aussi avant:

Code :

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

.Range("B:B,E:E,H:H,K:K").ColumnWidth = 11

avec le même message d'erreur ...

[Philippe Tulliez]

Bonjour,
Y a-t-il eu es déplacement ou suppression de feuilles ?
As-tu regardé la liste des cellules nommées ?
Te semble t'elle normale ?
Je crois qu'en début de procédure tu devrais supprimer tous les noms préfixés "Ctrl_"

[halaster08]

Oui en début je supprime les anciennes que je recrée si elles existent encore.
Rien n'avait été modifié.
Mais comme le problème semblait se propagé a d'autres fichiers, j'ai redamaré le PC, et ça marche.
Désolé pour la perte de temps.