Syntaxe sur un tri dans une macro

Question

Je me permets de vous soumettre le code dune macro qui plante avec ce message juste à l'initialisation des 2 tris présents dans cette macro : " la methode range de l'objet worksheet a échoué " comme vous pouvez vous en rendre compte, j'ai encore beaucoup de lacunes !!! merci pour vos éclairages! Sub ARCHIVAGE() ' cette macro effectue le transfert des écritures de POINTES vers ARCHIVES Dim i%, n%, iDisp%, wDisp As Worksheet Dim wmont, nbecr As Single Dim lheure As Date, ladate As Date Dim nr1, nrnew, nran, nrsq As String Set L = Worksheets("LISTES") 'définit l'onglet LISTES nr1 = L.Range("E9") 'on stocke le n° rlv disponible 'tri des POINTES avant traitement pour être sur d'avoir les notifs (H4) en tete With wDisp.Range("A4:K" & iDisp) .Sort key1:=.Range("H4"), order1:=xlAscending, order3:=xlAscending, Header:=xlNo End With With Worksheets("POINTES") n = .Range("A" & .Rows.Count).End(xlUp).Row 'n° de la dernière ligne renseignée de POINTES Set wDisp = Worksheets("ARCHIVES") iDisp = wDisp.Range("A" & wDisp.Rows.Count).End(xlUp).Row 'idisp = n° de la 1ere ligne dispo de ARCHIVES nbecr = 0 For i = n To 4 Step -1 'recherche sur POINTES en remontant vers le haut If .Range("H" & i) = nr1 Then 'sélection des lignes par rapport au n° relevé iDisp = iDisp + 1 With .Range("A" & i).Resize(, 11) .Copy wDisp.Range("A" & iDisp) ' copie de POINTES vers ARCHIVES .Delete xlShiftUp ' delete de POINTES nbecr = nbecr + 1 End With End If Next i End With 'MsgBox "mef nouveau rlv" If Val(Mid(nr1, 5, 2)) = 12 Then nrnew = CStr(Val(Mid(nr1, 1, 2)) + 1) & "-r01" 'nouv num rlv avec changement d'année Else nrnew = Mid(nr1, 1, 4) & Format((Val(Mid(nr1, 5, 2)) + 1), "00") 'nouv num rlv avec changement séquence End If Set L = Worksheets("LISTES") 'définit l'onglet LISTES L.Range("E9").Value = nrnew 'num du prochain rlv dispo à archiver lheure = Time ladate = Date L.Range("F10").Value = "dernier archivage effectué le :" L.Range("F11").Value = Replace((ladate & " à " & lheure), "/", "-") L.Range("G10").Value = nbecr 'tri sur ARCHIVES : notif (H4) date (A4) mont (G4) sens (J4) With wDisp.Range("A4:K" & iDisp) .Sort key1:=.Range("J4"), order1:=xlAscending, Header:=xlNo .Sort key1:=.Range("H4"), order1:=xlAscending, key2:=.Range("A4"), order2:=xlAscending, _ key3:=.Range("G4"), order3:=xlAscending, Header:=xlNo End With wDisp.Activate End Sub...

gmb · Answer

Sans ton fichier, il sera difficile de comprendre pourquoi ta macro ne marche pas.... Bye ...

MFerrand · Answer

Si tu utilises la balise Code pour le code dans un post, ce serait bien ! Et si de plus le code était systématiquement bien indenté et sans sauter de ligne, ce serait encore mieux ! Une première remarque (en attendant peut-être de pouvoir lire sans se fatiguer inutilement) : .Sort key1:=.Range("H4"), order1:=xlAscending, order3:=xlAscending, Header:=xlNo Un peu de logique ! Si tu mets un paramètre order3, c'est que tu mets aussi un paramètre key3, parce que l'ordre sera sans effet si la clé de tri n'est pas précisée, et si une 3e clé est indiquée, c'est qu'il y en a une 2e ! donc key2 et order2 !! Peut-être est-il utile de te préciser que les clés 1 2 3 correspondent aux colonnes sur lesquelles tu tries dans l'ordre de priorité... Et que la méthode n'admet que 3 clés, mais n'empêche pas de trier sur un plus grand nombre de clés. Par exemple tu veux trier sur 5 clés, tu fais d'abord un tri sur tes clés 4 et 5 (qui seront key1 et key2, puis un tri sur les clés 1, 2 et 3 et ta base sera triée sur les 5 clés dans l'ordre retenu. Cordialement...

MFerrand · Answer

Pour les cheveux, je n'en ai plus beaucoup en réserve, alors j'évite !

Ceci dit s'il y a des remarques à faire, ton code demeure assez présentable...

Je dois m'interrompre (en étant arrivé à peu près à la moitié), je peux noter que toutes tes variables ne sont pas typées (pour celles qui devraient l'être, ex. : nr1) [les variables non typées individuellement ne sont pas typées, on ne type pas en groupe...]

Lors de ta première utilisation de iDisp, cette variable n'a pas été initialisée (donc = 0) et il est assez probable que l'appel de la plage A4:K0 provoque l'erreur que tu signales...

Ton code doit par ailleurs pouvoir être amélioré... (mais faut d'abord que je le lise jusqu'au bout).

MFerrand · Answer

OK ! Je vais essayer d'ordonner à peu près les remarques :

Tu as un certain nombre de variables déclarée et inutilisées. On pourra les supprimer. L n'est pas déclarée (à faire).

Ton acrobatie avec wDisp... [Je ne vais pas la critiquer, je suis coutumier de la réutilisation de variable dans une même procédure ], mais je pense que tu n'en as pas besoin car on peut traiter chaque feuille tour à tour sans recoupement en exploitant mieux la logique de ton code...

Tu fais un premier tri de la feuille POINTES, sur la colonne "notif." et c'est sur la valeur de cette colonne que tu définis les lignes à archiver. En triant, tu vas donc nécessairement regrouper les lignes à archiver. Il suffira donc de déterminer le début et la fin de cette plage à archiver et de l'affecter à une variable Range (ce qui sera plus productif que les variables feuilles...). La plage peut être collée globalement (NB: collage justifié pour reproduire les formats avec les valeurs, sinon l'affectation des valeurs resterait préférable) et pourra être supprimée ensuite...

Le nombre sera fourni par le nombre de ligne de la plage...

Pour la modification des éléments de LISTES, tu peux te contenter de variables String pour la date et l'heure. F10 ne varie pas...

Je suppose aussi que le tri d'ARCHIVES, non opéré car un Exit Sub précède est tout de même à faire, mais tu n'as à trier que la plage que tu viens de coller, et la colonne H peut être laissée de côté puisqu'il s'agit du critère d'archivage...

Je modifie pour te proposer ta procédure modifiée.

MFerrand · Answer

Et voilà la proposition :

Sub ARCHIVAGE()
    ' cette macro effectue le transfert des écritures de POINTES vers ARCHIVES
    Dim Disp As Range, L As Worksheet
    Dim nr1$, nrnew$, ladate$, lheure$, nbecr%, i%, n%, ld%, lf%
    MsgBox "ARCHIVAGE démarré ! tapez OK et patienter jusqu'au message de fin d'archivage"
    'on mémorise les élements du dernier archivage
    Set L = Worksheets("LISTES")         'définit l'onglet LISTES
    nr1 = L.Range("E9")                  'on stocke le n° rlv disponible
    With Worksheets("POINTES")
        n = .Range("H" & .Rows.Count).End(xlUp).Row
        ' tri sur la colonne H
        .Range("A4:K" & n).Sort key1:=.Range("H4"), order1:=xlAscending, Header:=xlNo
        ' on définit le début et la fin de la plage à archiver
        For i = 4 To n
            If .Range("H" & i) = nr1 Then
                ld = i: Exit For
            End If
        Next i
        If ld = 0 Then Exit Sub
        For i = n To ld Step -1
            If .Range("H" & i) = nr1 Then
                lf = i: Exit For
            End If
        Next i
        Set Disp = .Range("A" & ld & ":K" & lf) 'plage à archiver
    End With
    nbecr = Disp.Rows.Count
    Application.ScreenUpdating = False
    With Worksheets("ARCHIVES")
        n = .Range("A" & .Rows.Count).End(xlUp).Row + 1 'ligne où coller l'archivage
        Disp.Copy .Range("A" & n) 'copie et collage
        ' Tri de la plage collée
        .Range("A" & n).Resize(nbecr, 11).Sort key1:=.Range("A" & n), order1:=xlAscending, _
          key2:=.Range("G" & n), order2:=xlAscending, key3:=.Range("J" & n), order3:=xlAscending, _
          Header:=xlNo
        .Activate
    End With
    Application.ScreenUpdating = True
    Disp.Delete xlShiftUp 'Suppression plage copiée
    ' mise à jour LISTES
    i = Val(Mid(nr1, 5, 2)): n = Val(nr1)
    n = IIf(i = 12, n + 1, n): i = i Mod 12 + 1
    nrnew = n & "-r" & Format(i, "00")                      'nouv num rlv avec changement d'année
    ladate = Format(Date, "dd-mm-yyyy") & " à "
    lheure = Format(Time, "hh:mm:ss")
    L.Range("E9").Value = nrnew              'num du prochain rlv dispo à archiver
    L.Range("F11").Value = ladate & lheure
    L.Range("G10").Value = nbecr
    MsgBox "ARCHIVAGE terminé (" & nbecr & " écritures transférées) pour RLV = " & nr1 & " - prochain = " & nrnew
    Exit Sub
End Sub

Avec les explications précédentes, tu ne devrais pas avoir de problèmes à t'y retrouver... Je n'ai pas modifié fondamentalement ta méthode, j'ai juste exploité complètement ce que tu avais commencé. Et quelques petits détails sur la fin...

Cordialement.

MFerrand · Answer

IIf c'est la fonction SI en VBA... Si i = 12 (condition vraie), elle renvoie n + 1, sinon n Mod est un opérateur en VBA, il renvoie le modulo (mais il convient de ne l'utiliser qu'avec des entiers, de même que l'opérateur \ qui renvoie un quotient entier...) i Mod 12 pour i = 1 à 11 va renvoyer 1 à 11, et 0 pour i = 12 donc i Mod 12 + 1 renverra 2 à 12 pour i = 1 à 11 et 1 pour i = 12 &#91;NB- Pas besoin de parenthèses, Mod est prioritaire par rapport à +&#93; Pour citer une partie de message, pour ma part je copie et colle, sélectionne et clic sur la balise Quote. Je ne sais s'il y a une meilleure méthode... Pour le code, pareil, tu sélectionnes ton code dans le post et tu cliques sur la balise Code. Il sera plus lisible, et l'indentation sera conservée pour l'essentiel (ton code étant à peu près correctement indenté, dommage de la perdre...) Ah ! également : si le trait de soulignement précédé d'une espace en bout de ligne a pour effet de ne pas interrompre une ligne de code .Range("A" &amp; n).Resize(nbecr, 11).Sort key1:=.Range("A" &amp; n), order1:=xlAscending, _ key2:=.Range("G" &amp; n), order2:=xlAscending, key3:=.Range("J" &amp; n), order3:=xlAscending, _ Header:=xlNo ci-dessus une seule ligne de code... Le caractère deux-points (:) est un séparateur qui permet d'écrire plusieurs lignes de code (constituant des commandes distinctes) sur la même ligne i = Val(Mid(nr1, 5, 2)): n = Val(nr1) n = IIf(i = 12, n + 1, n): i = i Mod 12 + 1 là, il y a 4 lignes de code sur ces deux lignes. Cordialement...

MFerrand · Answer

Bon courage... A+...