Discussion :

[mouncefdi]

salut le forum,

Je suis un débutant en VBA, mes connaissances sont très limitées, ci-dessous je vous explique ce que je souhaite faire en espérant que quelqu'un pourra m'éclairer.

Je souhaite transposer une colonne Z d'une feuille 1 en ligne 1 sur la feuille 2 (à partir de la cellule (1,4) inclus).

Sur la feuille 1 de mon excel :

Dans un premier temps je dois automatiser la création de cette colonne Z qui correspond à la concaténation des colonnes G jusqu'à Q si la condition suivante est respectée : les cellules de la colonne X = Main.

Dans un second temps je veux classer cette colonne afin d'éliminer les doublons un à un (car dans mon cas les concaténations peuvent avoir le même nom).

Sur la feuille 2 de mon excel :

Finalement je souhaite coller les valeurs des concaténations de la colonne Z de la feuille 1 à la feuille 2 en les transposant. (coller dans la feuille 2 à partir de la cellule (1,4) inclus)

[mouncefdi]

Resalu,

Ce que je souhaite faire en fait c'est pouvoir mettre à jour la ligne de la feuille 2 qui correspond à des concaténations de la feuille 1.

En fait je fesais des concaténations à la main et je les copier coller manuellement sur la deuxieme feuille Excel, le grand problème qui s'est posé à moi c'est le fait que le contenu de la feuille 1 change quotidiennement et du coup le nombre de concaténations peut changer, si je ne réussi pas à coder cela je devrais changer le contenu de la ligne 1 de la feuille 1 tous les jours manuellement.

Voila voila
si quelqu'un à des pistes n'hésitez pas à m'écrire....

[le petit nicolas]

salut, je ne suis pas sûr de tout bien comprendre, mais j'ai peut-être une piste quand même, ce n'est pas parce qu'on à rien à dire qu'il faut se taire...

pour la concaténation, je mettrais une simple formule dans la colonne Z. charge à toi de la dupliquer manuellement s'il y a des nouvelles lignes (remarque, on pourrait aussi le faire par macro)
= IF (X1 = "Main"; G1&H1&I&...&Q1;"")

pour recopier dans feuille 2, en transposant, et en enlevant les doublons
(attention j'ai pas testé, c'est pas bien)
:

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
 
Sub RecopierColZetTransposerSansDoublons()
    Dim PlageSource As Range
    Dim FeuilleResultat As Worksheet
    Dim NumColonneResultat as integer
    Dim NumLigneResultat as integer
    Dim Cell As Range
    Dim Un As Collection
    Dim i As Long
 
    ' nommer tous les endroits sur lesquels on travaille. ca permettra de modifier plus facilement plus tard
     PlageSource = Workbooks("LeNomDeTonFichierSource").Worksheets("LeNomDeTaFeuille").Range("Z:Z")
     FeuilleResultat = Workbooks("LeNomDeTonFichierResultat").Worksheets("LeNomDeTaFeuille")
     NumColonneResultat = 4
     NumLigneResultat = 1
 
    ' creer une liste sans doublons, d'après un tuto de Silkyroad
    Set Un = New Collection
    On Error Resume Next
    'Boucle sur la plage de cellule
    For Each Cell In PlageSource
        'If Cell <> "" Permet de ne pas prendre en compte les cellules vides
        'Un.Add Cell, CStr(Cell) Ajoute le contenu de la cellule dans la collection
        'le 2ème argument Cstr(cell) est le TAG de l'élément. Le tag est unique, c'est ca qui évite les doublons
        'ici on choisi le contenu de la cellule, transformé en chaine de caractere (par Cstr)
        If Cell <> "" Then Un.Add Cell, CStr(Cell)
    Next Cell
    On Error GoTo 0
 
    'Boucle sur les éléments de la collection
    'les écrire dans feuille2, en chagneant de colonne à chaque fois
    For i = 1 To Un.Count
        FeuilleResultat.Cells(NumLigneResultat, NumColonneResultat + i - 1) = CStr(Un)
    Next i
 
    Set Un = Nothing
End Sub

[ouskel'n'or]

S'il s'agit de copier en colonne des données en ligne, pourquoi n'utilises-tu pas la fonction de la feuille de calculs "Collage spécial -> Transposer les données"

Code :

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

1
2
    Range("A5:I5").Copy
    Range("A7").PasteSpecial Paste:=xlPasteAll, Transpose:=True

Tu as une autre façon consistant à utiliser WorksheetFunction.transpose

Code :

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

Range("C7:C15") = WorksheetFunction.Transpose(Range("A5:I5"))

Ces deux codes font la même chose.
Si c'est bien ta demande

[mouncefdi]

Je te remercie le petit nicolas pour ton intervention...
je suis en train d'essayer ca pour l'instant..
pour plus de détails concernant ce que je souhaite faire je vous mets ci joint un bout de mon tableau...
mon objectif est de créer les clés qui se trouve dans sur la 1ere ligne de la feuille 2 automatiquement à partir de la feuille 1. ( en utilisant biensur la condition les cellules de la colonne X=main)
La feuille 1 variant tout le temps, ceci me permettra de mettre à jour mon tableau.

[mouncefdi]

Bonsoir ouskel'n'or,

merci pour ton intervention, mais le problème c'est que j'ai des conditions sur le collage (colonne X feuille1).
si tu lis le message que je viens de poster avant, il y a plus de détails.

Merci d'avance

[ouskel'n'or]

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Sub test()
Dim i As Long, NoCol As Integer, laClef As String, tablo As Variant
    For i = 2 To Split(Worksheets("feuille1").UsedRange.Address, "$")(4)
        tablo = Range(Cells(i, 1), Cells(i, 17)).Value '
        For NoCol = 7 To 17
            laClef = laClef & tablo(1, NoCol)
            'là tu colles la clé où tu veux
        Next
        laClef = ""
    Next
End Sub

Ne reste plus qu'à les coller dans l'autre feuille.
Aucune transposition dans ce cas, seulement une orientation du texte à 90°

[mouncefdi]

salut ouskel'n'or,

je ne comprends pas très bien cette partie du code :

Code :

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

For i = 2 To Split(Worksheets("feuille1").UsedRange.Address, "$")(4)

si j'ai bien compris ca permet de parcourir toute les lignes de ma feuille jusqu'à la dernière ligne vide??

[ouskel'n'or]

si j'ai bien compris ca permet de parcourir toutes les lignes de ma feuille jusqu'à la dernière ligne vide??

Non, jusqu'à la dernière ligne renseignée. Mais je pense que c'est ce que tu voulais dire...
Pour une explication, regarde ici.

[mouncefdi]

okay merci pour les explications ouskel'n'or.
avec ton code j'arrive pas à éliminer les doublons de clès?
J'ai essayé avec la proposition de code de " le petit nicolas" mais je pense que la collection n'est pas définie....en + avec le nombre de ligne et de colonnes qu'il faut préciser au début du code, je pense que je ne suis pas sorti de l'auberge, puisque mon tableau va changer tout le temps...

[ouskel'n'or]

Pour les doublons, il est normal qu'ils ne soient pas supprimés puisque le pb n'est pas traité.
La solution est simple : Avant de coller la donnée, tu vérifies son absence ligne 1 de la feuille feuille2_main

Pour ça tu utilises find

Code :

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

1
2
3
4
5
6
7
With Worksheets("feuille2_main").range("A1:" & Split(Worksheets("feuille2_main").Usedrange.address, ":")(2))
     Set c = .find(LaClef)
     if c is nothing then
          'Tu colles ta clé Colonne... (en lettre)
          NoCol = Split(Worksheets("feuille2_main").Usedrange.address, "$")(3) 'A REMPLACER ********
     endif
End with

Edit
Je corrige la dernière ligne (tu as besoin de la première colonne vide, non de la dernière colonne renseignée

Code :

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

NoCol = Worksheets("feuille2_main").Cells(1, Columns.Count).End(xlToLeft).Column + 1

Bon app'

[mouncefdi]

Ouskel'n'or, je te remercie pour ton idée qui est très intéressante pour filtrer les concaténations.

Je me suis appuyer finalement sur le code de "le petit nicolas", ile code marche bien, mais il y a un autre problème : les clés que je dois avoir sur la premiere ligne de la feuille 2 correspondent en fait à la concaténation des colonnes G jusqu'à Q de la feuille 1 si la condition suivante est respectée : les cellules de la colonne X ( de la feuille 1)= Main.

Je ne sais pas comment je pourrais introduire dans le code une condition sur le fait de ne « copier/coller transposé » les concaténations que si sur la colonne X de la feuille 1 il y a écris Main.

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
 
Sub test67()
    Dim PlageSource As Range
    Dim FeuilleResultat As Worksheet
    Dim NumColonneResultat As Integer
    Dim NumLigneResultat As Integer
    Dim Cell As Range
    Dim Un As Collection
    Dim i As Long
 
    ' nommer tous les endroits sur lesquels on travaille
     Set PlageSource = Worksheets("EMR").Range("Z:Z")
     Set FeuilleResultat = Worksheets("Main Thresh")
     NumColonneResultat = 256
     NumLigneResultat = 1
 
    ' creer une liste sans doublons, d'après un tuto de Silkyroad
    Set Un = New Collection
    On Error Resume Next
    'Boucle sur la plage de cellule
    For Each Cell In PlageSource
        'If Cell <> "" Permet de ne pas prendre en compte les cellules vides
        'Un.Add Cell, CStr(Cell) Ajoute le contenu de la cellule dans la collection
        'le 2ème argument Cstr(cell) est le TAG de l'élément. Le tag est unique, c'est ca qui évite les doublons
        'ici on choisi le contenu de la cellule, transformé en chaine de caractere (par Cstr)
        If Cell <> "" Then Un.Add Cell, CStr(Cell)
    Next Cell
    On Error GoTo 0
 
    'Boucle sur les éléments de la collection
    'les écrire dans feuille2, en chagneant de colonne à chaque fois
    For i = 2 To Un.Count
        FeuilleResultat.Cells(NumLigneResultat, 2 + i) = CStr(Un(i).Value)
    Next i
 
    Set Un = Nothing
End Sub

Merci d'avance pour l'aide

[mouncefdi]

Je te remercie beaucoup le petit nicolas, Je me suis appuyer sur ton code, il est très bien il me reste juste à introduire la condition sur la colonne X=Main....
si vous avez des idées n'hésitez pas.
merci d'avance

[le petit nicolas]

salut à tous
merci pour les compliments

je vois que dans ton code tu as paramétré

Code :

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

NumColonneResultat = 256

mais qu'après tu ne te sers plus de cette variable, qui donc ne sert à rien
dans le code que j'avais proposé ca servait à paramètrer à partir de quelle colonne on veut écrire dans la feuille Main Thresh. tu as remplacé ça par

Code :

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

2 + i

.
Je trouve que c'est moins facile pour des modifs ultérieures. En utilisant

Code :

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

1
2
3
4
NumColonneResultat = 2
 
'et plus loin :
FeuilleResultat.Cells(NumLigneResultat, NumColonneResultat + i)

ca permet plus tard de modifier sa macro plus facilement : tous les éléments variables sont au début de la macro (le nom des feuilles, les plages, etc)

je remarque aussi que tu fais la boucle sur la collection à partir de 2. C'est exprès ? pour ne pas prendre la ligne de titre ?

pour la condition X1 = "Main"
- soit écrire dans la colonne Z la formule que j'avais proposé dans le premier post
- ou bien au moment de la construction de la collection :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
    'et toujours pour la facilité de maintenance je te recommande de définir 
    'en début de macro tous ces éléments, plutot que les coder en dur, sinon 
    'dans deux ans quand ta feuille changera... ici définir la valeur de l'offset 
    'entre Z et X (-2), et le critère "Main"
    For Each Cell In PlageSource
         ValeurX = cell.offset(0,-2).value
         If (Cell <> "" and ValeurX = "Main") Then Un.Add Cell, CStr(Cell)
    Next Cell
    On Error GoTo 0

[ouskel'n'or]

les clés que je dois avoir sur la premiere ligne de la feuille 2 correspondent en fait à la concaténation des colonnes G jusqu'à Q de la feuille 1

Effectivement, c'est pourquoi j'avais mis

Code :

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

1
2
3
4
        For NoCol = 7 To 17
            laClef = laClef & tablo(1, NoCol)
            'là tu colles la clé où tu veux
        Next

[mouncefdi]

Merci le petit nicolas pour ton aide précieuse et tes remarques pertinentes.

je remarque aussi que tu fais la boucle sur la collection à partir de 2. C'est exprès ? pour ne pas prendre la ligne de titre ?

Oui effectivement, c'est pour éviter le titre.

Sinon pour le code, j'ai fait les modifications que tu m'as dit est ca a l air de bien marcher.

[mouncefdi]

Une dernière question pour toi "le petit nicolas"

Je suis en fait dans une vieille version d'excel, le nombre de colonne étant limité, je peux avoir un nombre de clés (sans doublons) qui dépasse le nombre de colonne, donc il faudra créer une autre feuille pour faire une continuité de mon tableau.

Comment je peux traduire ca en code :

Tant que tous les objets de ma collection ne sont pas tous afficher (sur la 1ère ligne de la sheet2 à partir de la colonne4), il faut créer une autre feuille.

Si le nombre d'objets de la collection (filtrés/sans doublons)>250(étant le nombre de colonnes Excel), il faut créer un autre onglet où l'on colle les clès à partir de la colonne 4 (ligne1).

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
Sub test1()
    Dim PlageSource As Range
    Dim FeuilleResultat As Worksheet
    Dim NumColonneResultat As Integer
    Dim NumLigneResultat As Integer
    Dim Cell As Range
    Dim Un As Collection
    Dim i As Long
    Dim ValeurX As String
 
    ' nommer tous les endroits sur lesquels on travaille
     Set PlageSource = Worksheets("sheet1").Range("Z:Z")
     Set FeuilleResultat = Worksheets("sheet2")
    NumLigneResultat = 1
 
    ' creer une liste sans doublons, d'après un tutorial de Silkyroad
    Set Un = New Collection
    On Error Resume Next
    'Boucle sur la plage de cellule
    'entre Z et X (-2), et le critère "Main"
    For Each Cell In PlageSource
         ValeurX = Cell.Offset(0, -2).Value
         If (Cell <> "" And ValeurX = "Main") Then Un.Add Cell, CStr(Cell)
    Next Cell
    On Error GoTo 0
 
 
    'Boucle sur les éléments de la collection
    'les écrire dans feuille2, en chagneant de colonne à chaque fois
    For i = 1 To Un.Count
        FeuilleResultat.Cells(NumLigneResultat, 3 + i) = CStr(Un(i).Value)
    Next i
 
    Set Un = Nothing
End Sub

Merci d'avance

NB : tout le monde peux participer biensur, si vous avez un autre raisonnement, il sera le bienvenue

[le petit nicolas]

la manière dont tu l'as exprimé est la bonne, il ne te reste plus directement qu'à l'écrire en code...

Si le nombre d'objets de la collection (filtrés/sans doublons)>250(étant le nombre de colonnes Excel), il faut créer un autre onglet où l'on colle les clès à partir de la colonne 4 (ligne1)

c'est au moment d'écrire dans ta FeuilleResultat que tu peux agir
je n'ai pas testé le code ci-dessous, il peut y avoir des erreurs de frappe ou même de conception, mais dans la structure c'est ca
si tu as des anomalies, essayer de déboguer en suivant l'exécution, et en utilisant la touche F1

on pourrais aussi prévoir de répeter le processus plusieurs fois, au cas ou il y ait plusieurs fois 250 éléments... je te laisse un peu chercher, juste le temps d'aller manger un peu de poudreuse.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
   if  Un.Count > 250 then NouvFeuille = worksheets.add   ' creer une nouvelle feuille si plus de 250 éléments
 
   'Boucle sur les éléments de la collection
    'les écrire dans feuille2, en chagneant de colonne à chaque fois
    For i = 1 To Un.Count
        if  i < 250 then
             FeuilleResultat.Cells(NumLigneResultat, 3 + i) = CStr(Un(i).Value)
        else
             NouvFeuille.Cells(NumLigneResultat, 3 + i - 250) = CStr(Un(i).Value)
       end if
    Next i

[mouncefdi]

Merci le petit nicolas.
je vais réfléchir pour répeter le processus plusieurs fois, au cas ou il y ait plusieurs fois 250 éléments..( je dois le faire puisque j'ai plusieurs fois 250 éléments).
Merci pour tout.