range de lignes de longueur variable

Home – Le CFO masqué › Forums › VBA › range de lignes de longueur variable

Viewing 7 posts - 1 through 7 (of 7 total)
  • Author
    Posts
  • 12 July 2020 at 15 h 25 min #71173
    MLagrange
    Participant

    Bonjour, j’ai un fichier en trois onglets (“Conversion”, “Reclassement”, “Data”). L’information m’est transmise sous le format .CSV que j’inscris à l’onglet Data puis copie à l’onglet Conversion. La conversion génère 10 colonnes; la 1ère colonne contient plusieurs doublons que je dois éliminer afin de regrouper les données des autres colonnes sous une référence unique. Cette étape se fait dans l’onglet Reclassement, dans lequel je crée un tableau pour faciliter la récupération des données. Le problème : Le fichier .CSV peut être de différentes longueurs d’une fois à l’autre. Je dois donc éliminer le tableau actuel pour en générer un nouveau. Comment faire pour inscrire 1) la dernière ligne des données converties dans le Range (“A1:A???”) pour la validation des doublons et 2) la dernière ligne des valeurs uniques dans le Range (“A1:A???”) pour la création du Tableau. Merci aussi de commenter le code du module.

    12 July 2020 at 18 h 07 min #71179
    MLagrange
    Participant

    Bonjour, j’ai un fichier en trois onglets (« Conversion », « Reclassement », « Data »). L’information m’est transmise sous le format .CSV que j’inscris à l’onglet Data puis copie à l’onglet Conversion. La conversion génère 10 colonnes; la 1ère colonne contient plusieurs doublons que je dois éliminer afin de regrouper les données des autres colonnes sous une référence unique. Cette étape se fait dans l’onglet Reclassement, dans lequel je crée un tableau pour faciliter la récupération des données. Le problème : Le fichier .CSV peut être de différentes longueurs d’une fois à l’autre. Je dois donc éliminer le tableau actuel pour en générer un nouveau. Comment faire pour inscrire 1) la dernière ligne des données converties dans le Range (« A1:A??? ») pour la validation des doublons et 2) la dernière ligne des valeurs uniques dans le Range (« A1:A??? ») pour la création du Tableau. Merci aussi de commenter le code du module.

    Attachments:
    You must be logged in to view attached files.
    15 July 2020 at 0 h 08 min #71256
    Lionel
    Participant

    Bonjour MLagrange,

    Voici le code que je te propose (un peu remanié) :

    Sub Convers()

'Nettoyage de la feuille reclassement
With Sheets("Reclassement")
    .Activate
    'Tableau structuré > Plage - Il faut évidemment qu'un tableau structuré existe avec le nom DATA
    .ListObjects("DATA").Unlist
    'Trouver la dernière ligne de la feuille Reclassement
    DernLigR = .Range("A" & Rows.Count).End(xlUp).Row
    'Suppression des anciennes lignes pour faire de la place
    'Le test '1' : pour être certain que le tableau contient des données sinon il efface la ligne des titres
    If DernLigR > 1 Then .Range("A2:J" & DernLigR).EntireRow.Delete
End With

'Copie des données Conversion > Reclassement
With Sheets("Conversion")
    .Activate
    'Trouver la dernière ligne de Conversion
    DernLigC = .Range("A" & Rows.Count).End(xlUp).Row
    'Copie des données de Conversion > Reclassement
    .Range("A2:A" & DernLigC).Copy Sheets("Reclassement").Range("A2")
    Application.CutCopyMode = False
End With
    
With Sheets("Reclassement")
    .Activate
    .Range("A2:A" & DernLigC).Select
    'Conversion de la colonne A en différentes colonnes
    Selection.TextToColumns Destination:=Sheets("Reclassement").Range("A2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1)), TrailingMinusNumbers:=True
    'Suppression des doublons
    .Range("$A$1:$J$" & DernLigC).RemoveDuplicates Columns:=1, Header:=xlYes
'Mise en forme des données
    DernLigR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("B2:B" & DernLigR).Select
    Selection.NumberFormat = "yyyy/mm/dd hh:mm:ss"
    .Range("C2:C" & DernLigR).Select
    Selection.NumberFormat = "mm/dd/yyyy"
    .Range("H2:H" & DernLigR).Select
    Selection.NumberFormat = "0.00000"
    .Range("I2:I" & DernLigR).Select
    Selection.NumberFormat = "#,##0.00 $"

'Mettre en tableau structuré
    Set myTable = .Range("A2").CurrentRegion
    .ListObjects.Add(xlSrcRange, myTable, , xlYes).Name = "DATA"
    .ListObjects("DATA").TableStyle = "TableStyleLight9"

'Tri du tableau
    With .ListObjects("DATA").Sort
        .SortFields.Clear
        'Mise en place des trois clés de tri
        .SortFields.Add Key:=Range("DATA[Date]"), SortOn:=xlSortOnValues, Order:=xlAscending _
            , DataOption:=xlSortNormal
        .SortFields.Add Key:=Range("DATA[Titre]"), SortOn:=xlSortOnValues, Order:=xlAscending _
            , DataOption:=xlSortNormal
        .SortFields.Add Key:=Range("DATA[Heure/min]"), SortOn:=xlSortOnValues, Order:= _
            xlAscending, DataOption:=xlSortNormal
        'Tri effectif
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
'Ajuster les colonnes
    .Columns("A:J").Select
    .Columns("A:J").EntireColumn.AutoFit

End With
End Sub
    15 July 2020 at 13 h 05 min #71268
    MLagrange
    Participant

    Bonjour et merci pour la réponse. Elle me sera utile à l’étape suivante. En effet, entretemps, j’ai retravaillé le modèle et j’essaie d’utiliser les fonctions VBA au lieu d’enregistrer des macros Excel; j’ai donc éliminé le tableau et j’essaie d’appliquer une fonction Do While pour insérer les formules. J’ai toujours un message d’erreur à la cellule B2 donc impossible d’aller vers les autres colonnes sans parler de descendre jusqu’à la dernière ligne. J’ai essayé avec Cells(“x,2”) et ça ne fonctionne pas non plus. Est-ce que je dois introduire For Each également ?

    La problématique c’est que le fichier initial compte 1501 lignes, réduites à 456 lignes une fois les doublons de la colonne A enlevés, mais je dois récupérer les valeurs des 1501 lignes dans les colonnes B à J d’où la série de formules.

    J’ai par ailleurs raccourci les étapes de conversion. Je vous soumets donc un nouveau jeu de documents.
    Merci
    Michel Lagrange

    Attachments:
    You must be logged in to view attached files.
    15 July 2020 at 16 h 31 min #71278
    MLagrange
    Participant

    Si on refais
    ait l’exercice en regroupant les données par numéro d’ordre au lieu d’éliminer les doublons ne serait-ce pas plus simple ?

    16 July 2020 at 5 h 02 min #71281
    Lionel
    Participant

    Re-,
    Pour éviter beaucoup de complications, tu pourrais passer par Power Query. Je te mets un exemple à partir de ton fichier csv.

    Attachments:
    You must be logged in to view attached files.
    16 July 2020 at 7 h 15 min #71286
    MLagrange
    Participant

    Merci, je n’avais pas pensé à cette avenue

  • Author
    Posts
Viewing 7 posts - 1 through 7 (of 7 total)
  • You must be logged in to reply to this topic.