[résolu] question pour des plages (noms) définies avec une condition

[stephsteph]

Bonjour à tous,
J'ai aujourd'hui un souci qui dépasse mes compétences même si cela ne semble pas très sioux pour des experts.
C'est facile à expliquer, pas besoin d'un fichier exemple (mais j'en joins un pour le test quand même avec 7 colonnes et 41 lignes avec les plages crées manuellement appelées Gabon, Sénégal, Mali, mais en vrai, le fichier est bien plus volumineux, et il y aura au moins 100 plages).
Dans un fichier excel [2007], j'ai des colonnes remplies de diverses informations de texte dans chaque cellule, entre la colonne 1 et 7.
Je voudrais une macro VBA qui me définisse un nom de plage en fonction de la condition que la colonne 6 contienne le chiffre 1 et alors le nom de la plage est le contenu de la colonne 7 pour la même ligne et la définition de la plage est entre la colonne 1 et 5 pour toutes les lignes avec le chiffre 1 en colonne 6.
Et ainsi de suite pour les lignes avec 2, 3, 4, en colonne 6 avec respectivement pour chacune le nom repris du contenu de la colonne 7.
Bien sûr les lignes (j'ai fait un tri au préalable) avec le même contenu en colonne 6 (et aussi 7) sont contiguës, mais ce n'est pas une succession ininterrompue (la première occurrence de 1 peut commencer en ligne, par exemple 11, puis cela se poursuit, par exemple, pendant 6 lignes, puis rien pendant 4 lignes, puis commence les 2 durant 3 lignes, puis rien pendant 6 lignes, puis commence les 3, etc.)
Bien sûr mon fichier évolue et je ne peux créer les plages de nom manuellement car après ajout de nouvelles lignes les définitions seraient fausses.
Donc il faut que la macro commence par effacer toutes les définitions de plages précédentes de cette feuille, avant de créer les nouvelles.
J'espère que ce petit casse-tête matheux va titiller les méninges des experts du site.
En tout cas merci d'avance.
Steph (avec une bizz à Pierre-Jean qui, je vois, est sur le forum en ce moment)

 

[Dranreb]

Re : question pour des plages (noms) définies avec une condition

Bonsoir.

je ne peux créer les plages de nom manuellement car après ajout de nouvelles lignes les définitions seraient fausses

Partiellement faux, ça. Si vous insérez derrière la plage nommée d'accord. Mais si pour ajouter une ligne à une plage nommée vous copiez la dernière, l'insérez devant elle même puis utilisez l'original repoussé d'une ligne vers le bas comme nouvelle ligne, il n'y a pas de problème. Toutes les références seront corrigées en conséquence. Mais vraiment toutes les références, pas seulement celles des noms, celles dans des formules aussi, celle de la zone d'impression, tout ! Même celle d'un objet Range que vous auriez défini dans une variable !

Bonsoir Robert.

 

[Robert]

Re : question pour des plages (noms) définies avec une condition

Bonsoir Stephsteph, bonsoir le forum,

Peut-être comme ça :

Sub Macro1()
Dim o As Object 'déclare la variable o (Onglet)
Dim dl As Long 'déclare la variable dl (Dernière Ligne)
Dim pl As Range 'déclare la variable pl (PLage)
Dim d As Object 'déclare la variable d (Dictionnaire)
Dim cel As Range 'déclare la variable cel (CELlule)
Dim tmp As Variant 'déclare la variable tmp (tableau TeMPoraire)
Dim tp() As Range 'déclare le tableau de variables indéxées tp (Tableau des Plages)
Dim n() As String 'déclare le tableau de variables indéxées n (tableau des Noms)

Set o = Sheets("Feuil1") 'définit l'onglet o
dl = o.Cells(Application.Rows.Count, 1).End(xlUp).Row 'définit la dernière ligne éditée dl de la colonne 1 (=A)
Set pl = o.Range("F1:F" & dl) 'définit la plage pl
Set d = CreateObject("Scripting.Dictionary") 'définit le dictionnaire d
For Each cel In pl 'boucle sur toutes les cellules cel de la plage pl
    If cel.Value <> "" Then d(cel.Value) = "" 'alimente le dictionnaire des cellules non vides
Next cel 'prochaine cellule cel de la plage pl
tmp = d.keys 'récupère la liste sans doublons dans le tableau temporaire tmp
ReDim tp(d.Count) 'redimensionne le tableau des plages tp
ReDim n(d.Count) 'redimensionne le tableau des nom n
For x = 0 To UBound(tmp) 'boucles sur toutes les valeurs uniques du tableau tmp
    Set tp(x) = Range("A1") 'pré définit la plage tp(x)
    For Each cel In pl 'boucle 2 sur toutes les cellules de la plage pl
        If cel.Value = tmp(x) Then 'condition : si la valeur de la cellule est égale à la valeur unique du tableau tmp
            'définit la plage tp(x)
            Set tp(x) = IIf(tp(x).Cells.Count = 1, o.Cells(cel.Row, 1).Resize(1, 5), Application.Union(tp(x), o.Cells(cel.Row, 1).Resize(1, 5)))
            n(x) = cel.Offset(0, 1).Value 'définit le nom n(x)
        End If 'fin de la condition
    Next cel 'prochaine cellule de la plage
    tp(x).Name = n(x) 'attribue le nom n(x) à la plage tp(x)
Next x 'prochaine valeur unique du tableau tmp
End Sub

[Édition]
Bonsoir Bernard on s'est croisé...

 

[stephsteph]

Re : question pour des plages (noms) définies avec une condition

Bonjour et merci à tous les 2.

Pour DranReb, je suis un peu cachottière et je n'ai pas dit que j'ai encore une autre colonne pour un autre tri. Donc tu aurais raison, s'il n'y avait qu'un seul tri et si je plaçais mes nouvelles lignes à l'intérieur de la bonne plage nommée.
Pour Robert, c'est nickel à un détail près que j'ai soupçonné donc corrigé, peut-être dû à la version d'Excel (je ne sais pas): il faut définir la variable x comme:

Dim x As Variant

Je m'aperçois que je n'ai pas été à 100% claire.
Il me faudrait, en plus, une boucle qui remouline automatiquement la macro en passant de n=1 jusqu'à la valeur la plus élevée de n dans la colonne, avec un incrément de 1 (soit 1, 2, 3, ici).
J'ai essayé en tâtonnant, mais cela ne marche pas (pas placé le code au bon endroit???)...

    Dim z As Integer
    For z = 1 To 3 '(en fait 84)
    Next z

A+ pour finaliser, Steph

PS: dans mon gros fichier, pour cette macro précise, les plages sont entre le colonne 3 et 14, donc il faut que je remplace A1, par B1 dans la macro? Et la colonne 6 est en fait en colonne 24, pas contiguë à la dernière colonne définie dans les plages (en l’occurrence, 14)... que dois-je modifier?
PPS: DranReb, vous voyez, j'aurais une 2ème macro identique après un autre tri, et là la colonne 6 sera la colonne par exemple 27, suivie d'info de noms de plage en colonne 28!

 

[Robert]

Re : question pour des plages (noms) définies avec une condition

Bonjour le fil, bonjour le forum,

Donner un exemple qui ne correspond pas à la réalité du fichier est une perte de temps et d'énergie !
Je n'ai pas compris non plus ta nouvelle demande d'une boucle qui remouline...
Le code modifié et adapté :

Sub Macro1()
Dim o As Object 'déclare la variable o (Onglet)
Dim dl As Long 'déclare la variable dl (Dernière Ligne)
Dim pl As Range 'déclare la variable pl (PLage)
Dim d As Object 'déclare la variable d (Dictionnaire)
Dim cel As Range 'déclare la variable cel (CELlule)
Dim tmp As Variant 'déclare la variable tmp (tableau TeMPoraire)
Dim tp() As Range 'déclare le tableau de variables indéxées tp (Tableau des Plages)
Dim n() As String 'déclare le tableau de variables indéxées n (tableau des Noms)
Dim x As Integer 'déclare la variable x

Set o = Sheets("Feuil1") 'définit l'onglet o
dl = o.Cells(Application.Rows.Count, 2).End(xlUp).Row 'définit la dernière ligne éditée dl de la colonne 2 (=B)
Set pl = o.Range("X1:X" & dl) 'définit la plage pl
Set d = CreateObject("Scripting.Dictionary") 'définit le dictionnaire d
For Each cel In pl 'boucle sur toutes les cellules cel de la plage pl
    If cel.Value <> "" Then d(cel.Value) = "" 'alimente le dictionnaire des cellules non vides
Next cel 'prochaine cellule cel de la plage pl
tmp = d.keys 'récupère la liste sans doublons dans le tableau temporaire tmp
ReDim tp(d.Count) 'redimensionne le tableau des plages tp
ReDim n(d.Count) 'redimensionne le tableau des nom n
For x = 0 To UBound(tmp) 'boucles sur toutes les valeurs uniques du tableau tmp
    Set tp(x) = Range("A1") 'pré définit la plage tp(x)
    For Each cel In pl 'boucle 2 sur toutes les cellules de la plage pl
        If cel.Value = tmp(x) Then 'condition : si la valeur de la cellule est égale à la valeur unique du tableau tmp
            'définit la plage tp(x)
            Set tp(x) = IIf(tp(x).Cells.Count = 1, o.Cells(cel.Row, 2).Resize(1, 13), Application.Union(tp(x), o.Cells(cel.Row, 2).Resize(1, 13)))
            n(x) = o.Cells(cel.Row, 7).Value 'définit le nom n(x) (à adapter à la colonne qui contient le nom)
        End If 'fin de la condition
    Next cel 'prochaine cellule de la plage
    tp(x).Name = n(x) 'attribue le nom n(x) à la plage tp(x)
Next x 'prochaine valeur unique du tableau tmp
End Sub

Il te faudra certainement adapter la ligne :

n(x) = o.Cells(cel.Row, 7).Value

 

[stephsteph]

Re : question pour des plages (noms) définies avec une condition

Bonjour Robert,
J'avais très mal dormi cette nuit, mais je n'aurais pas dû me lever ou en tout cas pas travailler sur la macro.
1000 excuses. Ta macro est nickel... elle fait bien les 3 champs comme je le souhaitais, donc pas de "remouline".
Je me fais toute petite et je te remercie de ne pas m'avoir enfoncée pour mes bêtises!!!
Pour ce qui est des petits blocages pour l'adaptation, je te comprend mais j'avais préféré prendre un cas général qui puisse servir aux autres visiteurs du forum.
Je ne savais pas interpréter le code :

Set tp(x) = IIf(tp(x).Cells.Count = 1, o.Cells(cel.Row, 1).Resize(1, 5), Application.Union(tp(x), o.Cells(cel.Row, 1).Resize(1, 5)))

Mais avec ta seconde macro, en regardant ce que tu avais changé, tout est devenu limpide.

Donc, maintenant tout est parfait.

Merci de tes lumières, de ta patience et de ta gentillesse.

Et je crois que cette nuit je vais rêver d'une grande plage, une vraie (peut-être comme celle de Sète que je ne connais pas, sauf par la chanson), pas d'une plage Excel, mais sans grain de sable (!!!)


Steph