Discussion :

[fonzzie31]

Bonjour,

Je commence mon apprentissage dans la programmation de macros sous Excel 2010.
Mais je me heurte à quelques soucis sur une macro que je souhaiterais mettre en place sur un fichier excel.

Le fichier excel en question contient un certain nombre de feuilles, dont une appelée "== RECAP ==". Des feuilles sont rajoutées au fur et à mesure dans le classeur.
Sur toutes les feuilles hors "== RECAP ==", la structure du tableau est la même : 4 Colonnes A,B,C,D au format texte (intitulées 'Collection','Titre','Date','Commentaires'). La ligne 1 correspond aux intitulés. La ligne 2 marque le début des données
Les colonnes sont au même endroit, d'une feuille à l'autre. La seule différence est la longueur du tableau qui varie d'une feuille à l'autre.
Sur chaque page, hors "== RECAP ==", une valeur texte "Liste Complète" est positionnée sur la cellule F2

Ce qui donne une feuille type qui ressemble à ça :


Sur la page '== RECAP ==', actuellement vide, je souhaiterais effectuer les choses suivantes :

Au changement d'une feuille hors "== RECAP ==" ayant la valeur "Liste Complète" en F2 :

1). Sur la colonne A de la page "== RECAP ==", afficher à partir cellule A2 (1 colonne, xx lignes), par ordre alphabétique, la liste des valeurs uniques de la colonne "Collection" des autres feuilles. L'intitulé de colonne (ligne 1) ne doit pas faire partie des valeurs.
2). Sur la colonne B de la page "== RECAP ==", afficher à partir cellule B2, le nombre d'occurrences de chaque valeur listée en colonne A, sur toutes les feuilles du classeur (hormis "== RECAP ==").

Pour arriver à mes fins, j'ai commencé à pondre la macro suivante :

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 
'Suspensions temporaires pour la macro
Application.ScreenUpdating = False
 
'Déclaration des variables
Dim TempColl() As String
 
Dim ChekCell As String
Dim Cellule As Range
Dim CollNames() As String
Dim a, b, c, d, e As Integer
 
 
' ****** Programme Principal ******
a = 0
ChekCell = ""
Oldchek = ""
 
' ==== Etape 1 : Récupérer valeurs uniques
 
If (Sh.Name <> "== RECAP ==") Then
 
 For Each Sh In ThisWorkbook.Worksheets
  If (Sh.Name <> "== RECAP ==") Then
   For Each Cellule In Sh.Range("A2:A50")
    If Not (IsError(Cellule.Value)) And (Oldchek <> Cellule.Value) Then
     If (InStr(1, ChekCell, Cellule.Value) = 0) Then
      If (a > 0) Then ChekCell = ChekCell + "-"
      'TempColl(a, 1) = Cellule.Value
      ChekCell = ChekCell + Cellule.Value
      Oldchek = Cellule.Value
      a = a + 1
     End If
    End If
   Next Cellule
  End If
 Next Sh
 
CollNames = Split(ChekCell, "-")
 
' ==== Etape 2 : Tri ordre alphabétique
' ==== Etape 3 : Affichage liste (1 colonne, xx lignes)
' ==== Etape 4 : Affichage des occurrence
 
End If
'Reactivations pour la macro
 
Application.ScreenUpdating = True
 
End Sub

Pour la recherche multifeuilles, j'ai créé une fonction qui hélas ne s'actualise pas automatiquement, quand je l'insère en tant que formule dans la cellule :

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
Public Function MultiSheets_Find(A_Chercher As String)
 
Dim Sh As Worksheet
Dim Cellule As Range
Dim occurrence As Integer
 
occurrence = 0
 
For Each Sh In ThisWorkbook.Worksheets
 If (Sh.Name <> "== RECAP ==") Then
  For Each Cellule In Sh.Range("A2:A40")
    If Not (IsError(Cellule.Value)) Then
     If (Cellule.Value = A_Chercher) Then
      occurrence = occurrence + 1
     End If
    End If
  Next Cellule
 End If
Next Sh
 
MultiSheets_Find = occurrence
 
End Function

Plusieurs questions me viennent alors :

- Comment restreindre Workbook_SheetChange aux feuilles hors "== RECAP ==" ayant la valeur texte "Liste Complète" en F2 ?

- Sur la Private Sub, j'ai du mettre en commentaire la ligne TempColl(a, 1) = Cellule.Value. Cette ligne me renvoie l'erreur "L'indice n'appartient pas à la sélection".
Je n'ai pas réussi à déterminer l'origine du problème. Du coup, je l'ai contourné en utilisant la commande Split.

- Comment classer par ordre alphabétique la variable tableau CollNames avant de l'afficher ?

- Comment afficher le contenu de la variable CollName sur la feuille "== RECAP ==" à partir de la cellule A22 (1 colonne, xx lignes) ?

Si vous pouviez m'aider à résoudre ces divers points, cela serait génial.
De plus, si vous voyez des optimisations à effectuer sur mon code, je serai ravi de les connaitre.

D'avance merci

[Menhir]

- Comment restreindre Workbook_SheetChange aux feuilles hors "== RECAP ==" ayant la valeur texte "Liste Complète" en F2 ?

Code :

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

If Sh.Range("F2").Value = "Liste Complète" Then

- Sur la Private Sub, j'ai du mettre en commentaire la ligne TempColl(a, 1) = Cellule.Value. Cette ligne me renvoie l'erreur "L'indice n'appartient pas à la sélection".
Je n'ai pas réussi à déterminer l'origine du problème. Du coup, je l'ai contourné en utilisant la commande Split.

Code :

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

Dim TempColl(50, 1) As String

Et remplace ton a = 0 par a = 1.

- Comment classer par ordre alphabétique la variable tableau CollNames avant de l'afficher ?

Si ton idée est de l'afficher dans des cellules, il serait beaucoup plus simple de faire le tri après affichage avec la méthode Sort de l'objet Range.

- Comment afficher le contenu de la variable CollName sur la feuille "== RECAP ==" à partir de la cellule A22 (1 colonne, xx lignes) ?

Code :

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

1
2
3
For b = 1 To a
    Worksheets("== RECAP ==").Cells(21 + b, 1).Value = CollName(b)
Next b