Tri de variables en VBA

Le 17/06/2016 à 11:58

Bonjour à tous,

J'aurais une petite question qui pourrait sembler évidente mais que je n'arrive pas à résoudre.

J'ai construit tout un fichier formulaire, pour alimenter une base de données.

J'aimerais que cette base de données se trie automatiquement quand je clique sur le bouton "Enregistrer" du formulaire.

L'ordre des colonnes de cette base de données est amenée à être modifié, c''est pourquoi je veux que le tri se base sur des variables et non des références de colonne précise.

En utilisant l'enregistreur de macro, je parviens au résultat suivant :

  
ActiveWorkbook.Worksheets("Données").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Données").Sort.SortFields.Add Key:=Range("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Données").Sort.SortFields.Add Key:=Range("C:C"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Données").Sort.SortFields.Add Key:=Range("E:E"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Données").Sort.SortFields.Add Key:=Range("H:H"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "janvier,fevrier,mars,avril,mai,juin,juillet,aout,septembre,octobre,novembre,decembre,Semestre 1,Semestre 2,Trimestre 1,Trimestre 2,Trimestre 3,Trimestre 4, Année n" _
        , DataOption:=xlSortNormal

J'ai donc cherché à le modifier de la facon suivante, en intégrant mes variables :

Sub Tri()
'
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim Column_periode As Range
Dim Column_annee As Range
Dim Column_services As Range
Dim nb_c As Integer
Dim Column_fournisseurs As Range

 nb_c = ThisWorkbook.Sheets("Données").UsedRange.SpecialCells(xlCellTypeLastCell).Column 'Nombre de colonnes remplies

        For i = 1 To nb_c
            If Cells(1, i) Like "*Fournisseurs*" Then Set Column_fournisseurs = Range("i:i")
        Next i
        For j = 1 To nb_c
            If Cells(1, j) Like "*Services*" Then Set Column_services = Range("j:j")
        Next j
        For k = 1 To nb_c
            If Cells(1, k) Like "*Année*" Then Set Column_annee = Range("k:k")
        Next k
        For l = 1 To nb_c
            If Cells(1, l) Like "*Période*" Then Set Column_periode = Range("l:l")
        Next l

    ActiveWorkbook.Worksheets("Données").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Données").Sort.SortFields.Add Key:=Column_fournisseurs, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Données").Sort.SortFields.Add Key:=Column_services, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Données").Sort.SortFields.Add Key:=Column_annee, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Données").Sort.SortFields.Add Key:=Column_periode, SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "janvier,fevrier,mars,avril,mai,juin,juillet,aout,septembre,octobre,novembre,decembre,Semestre 1,Semestre 2,Trimestre 1,Trimestre 2,Trimestre 3,Trimestre 4, Année n" _
        , DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Données").Sort
        .SetRange Range("A:AX")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

Est ce que l'un d'entre vous voit quelque chose d'évident que j'aurais oublié dans la déclaration de mes variable ? j'ai du mal à utiliser l'objet Range avec mes variables...

Je ne peux malheureusement pas joindre de fichier car il s'agit d'un fichier d'entreeprise...

Un grand merci d'avance à vous !!

Bonne après midi,

Jeanne

Le 17/06/2016 à 12:12

Bonjour,

une proposition de correction pour la sélection des colonnes de tri

    With Sheets("données")
        For i = 1 To nb_c
            If .Cells(1, i) Like "*Fournisseurs*" Then Set Column_fournisseurs = .Range(i & ":" & i)
            If .Cells(1, i) Like "*Services*" Then Set Column_services = .Range(i & ":" & i)
            If .Cells(1, i) Like "*Année*" Then Set Column_annee = .Range(i & ":" & i)
            If .Cells(1, i) Like "*Période*" Then Set Column_periode = .Range(i & ":" & i)
        Next i
    End With
Le 17/06/2016 à 12:16

Bonjour,

Merci pour ta réponse, mais il ne semble plus reconnaitre les colonnes...

Quand je clique sur Debogage, il me met en évidence le .Apply de la fin 

Sub Tri()
'
Dim i As Integer
Dim Column_periode As Range
Dim Column_annee As Range
Dim Column_services As Range
Dim nb_c As Integer
Dim Column_fournisseurs As Range

 nb_c = ThisWorkbook.Sheets("Données").UsedRange.SpecialCells(xlCellTypeLastCell).Column 'Nombre de colonnes remplies

       With Sheets("données")
        For i = 1 To nb_c
            If .Cells(1, i) Like "*Fournisseurs*" Then Set Column_fournisseurs = .Range(i & ":" & i)
            If .Cells(1, i) Like "*Services*" Then Set Column_services = .Range(i & ":" & i)
            If .Cells(1, i) Like "*Année*" Then Set Column_annee = .Range(i & ":" & i)
            If .Cells(1, i) Like "*Période*" Then Set Column_periode = .Range(i & ":" & i)
        Next i
    End With

    ActiveWorkbook.Worksheets("Données").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Données").Sort.SortFields.Add Key:=Column_fournisseurs, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Données").Sort.SortFields.Add Key:=Column_services, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Données").Sort.SortFields.Add Key:=Column_annee, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Données").Sort.SortFields.Add Key:=Column_periode, SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "janvier,fevrier,mars,avril,mai,juin,juillet,aout,septembre,octobre,novembre,decembre,Semestre 1,Semestre 2,Trimestre 1,Trimestre 2,Trimestre 3,Trimestre 4, Année n" _
        , DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Données").Sort
        .SetRange Range("A:AX")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub
Le 17/06/2016 à 12:32

re-bonjour,

voici une correction.

 With Sheets("données")
        For i = 1 To nb_c
            If .Cells(1, i) Like "*Fournisseurs*" Then Set Column_fournisseurs = .Columns(i)
            If .Cells(1, i) Like "*Services*" Then Set Column_services = .Columns(i)
            If .Cells(1, i) Like "*Année*" Then Set Column_annee = .Columns(i)
            If .Cells(1, i) Like "*Période*" Then Set Column_periode = .Columns(i)
        Next i
    End With
Le 17/06/2016 à 14:53

Un grand merci H2SO4

Bon j'ai l'impression que maintenant, la variable est bien définie, et le tri se fait, mais qu'uniquement si il n'y a aucun filtre déjà en place, or la formule initialement en place me supprimait tout les filtres puis me triait les données.

As tu une idée de la raison ?

Je pense que c'est quelque chose d'énorme car il y a un gros beug (excel ferme brusquement) quand j'actionne la formule alors qu'il y a des tris, mais je ne parviens pas à l'identifier...

Un grand grand merci d'avance à toi !!!

Sub Tri()
'
Dim i As Integer
Dim Column_periode As Range
Dim Column_annee As Range
Dim Column_services As Range
Dim nb_c As Integer
Dim Column_fournisseurs As Range

 nb_c = ThisWorkbook.Sheets("Données").UsedRange.SpecialCells(xlCellTypeLastCell).Column 'Nombre de colonnes remplies

        With Sheets("données")
        For i = 1 To nb_c
            If .Cells(1, i) Like "*Fournisseurs*" Then Set Column_fournisseurs = .Columns(i)
            If .Cells(1, i) Like "*Services*" Then Set Column_services = .Columns(i)
            If .Cells(1, i) Like "*Année*" Then Set Column_annee = .Columns(i)
            If .Cells(1, i) Like "*Période*" Then Set Column_periode = .Columns(i)
        Next i
    End With

    ActiveWorkbook.Worksheets("Données").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Données").Sort.SortFields.Add Key:=Column_fournisseurs, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Données").Sort.SortFields.Add Key:=Column_services, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Données").Sort.SortFields.Add Key:=Column_annee, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Données").Sort.SortFields.Add Key:=Column_periode, SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "janvier,fevrier,mars,avril,mai,juin,juillet,aout,septembre,octobre,novembre,decembre,Semestre 1,Semestre 2,Trimestre 1,Trimestre 2,Trimestre 3,Trimestre 4, Année n" _
        , DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Données").Sort
        .SetRange Range("A:AX")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub
Le 17/06/2016 à 15:40

Je pense que le problème vient de la dernière partie, la partie suivante :

With ActiveWorkbook.Worksheets("Données").Sort
        .SetRange Range("A:BW")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Mais cette partie vient de l'enregistreur de Macro et je ne la comprend pas à 100%

Le 17/06/2016 à 15:58

Bon en fait j'ai trouvé, j'ai rajouté une petite partie à mon code pour effacer les filtres tout simplement.

Pour information :

Sub Tri()
'
Dim i As Integer
Dim Column_periode As Range
Dim Column_annee As Range
Dim Column_services As Range
Dim nb_c As Integer
Dim Column_fournisseurs As Range

 nb_c = ThisWorkbook.Sheets("Données").UsedRange.SpecialCells(xlCellTypeLastCell).Column 'Nombre de colonnes remplies

        With Sheets("données")
        For i = 1 To nb_c
            If .Cells(1, i) Like "*Fournisseurs*" Then Set Column_fournisseurs = .Columns(i)
            If .Cells(1, i) Like "*Services*" Then Set Column_services = .Columns(i)
            If .Cells(1, i) Like "*Année*" Then Set Column_annee = .Columns(i)
            If .Cells(1, i) Like "*Période*" Then Set Column_periode = .Columns(i)
        Next i
    End With

    On Error Resume Next
    If ActiveWorkbook.Worksheets("Données").AutoFilterMode Then ActiveWorkbook.Worksheets("Données").ShowAllData

    ActiveWorkbook.Worksheets("Données").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Données").Sort.SortFields.Add Key:=Column_fournisseurs, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Données").Sort.SortFields.Add Key:=Column_services, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Données").Sort.SortFields.Add Key:=Column_annee, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Données").Sort.SortFields.Add Key:=Column_periode, SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "janvier,fevrier,mars,avril,mai,juin,juillet,aout,septembre,octobre,novembre,decembre,Semestre 1,Semestre 2,Trimestre 1,Trimestre 2,Trimestre 3,Trimestre 4, Année n" _
        , DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Données").Sort
        .SetRange Range("A:BW")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

Un grand merci pour ton aide

Bonne fin de journée

Rechercher des sujets similaires à "tri variables vba"