2 petits soucis

Bonjour à tous,

Je suis sur un (très) (trop) gros fichier excel de 130.000KB et j'ai régulierement un message d'erreur m'expliquant d'Excel n'est pas capable de mettre à jour les formules. Pour éviter de me pourrir la vie et avancer dans mon boulot j'ai desactivé le mode calcul auto, et j'ai fait une macro qui fait: Range(...).calculate pour faire les calculs progressivement, mais ca va pas durer. C'est un soucis de mémoire vive je pense, ça vous est deja arrivé ?

J'ai deux petits soucis:

Le 1er soucis sur lequel je souhaite votre aide:

For i = 3 To 31 '31 = dernière feuille de données'
    Sheets(i).Calculate
        DernLigne = Sheets(i).Range("A" & Rows.Count).End(xlUp).Row

        Sheets(i).Columns("A:Q").Sort.SortFields.Add Key:=Range("Q2:Q" & DernLigne), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
           With ActiveWorkbook.Sheets(i).Sort
           .SetRange Range("A1:Q" & DernLigne)
           .Header = xlYes
           .MatchCase = False
           .Orientation = xlTopToBottom
           .SortMethod = xlPinYin
           .Apply
           End With
Next i

Je souhaite faire un tri de chaque feuille de 3 à 31 au niveau de la colonne Q (ascending, en gardant des headers). Petite précision, je ne connaissais pas le code des tri et j'ai donc adapté l'enregistreur... Si qlq a une piste

Erreur de type 1004 "Sort non compatible avec Range" sur la ligne

   Sheets(i).Columns("A:Q").Sort.SortFields.Add Key:=Range("Q2:Q" & DernLigne), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

Le second:

Je cherche à remplir un tableau avec le top 4 et le flop 4 des valeurs d'un autre tableau que j'ai préalablement trié. Petit subtilité, j'ai créer un menu déroulant pour que l'on puisse choisir la feuille ou aller chercher le top/flop. Sur ce sujet je n'arrive pas à:

• trouver l'expression pour dire "Range des 4 dernière lignes de la colonne ..." sachant que j'ai définit la dernière ligne.
• Faire comprendre à ma macro dans quelle feuille aller choisir les infos (mais l'ordre ne change pas, toutes mes macro ont été écrites en appellant la sheet(i) du coup surement en refaisant pareil

En vous remerciant

Bonjour,

Un essai : Worksheets(i).Range("A2:Q" & DernLigne).Sort [Q2], 1

1 pour tri ascendant = tri croissant

2 pour tri descendant = tri décroissant

Je précise cela car tu as écrit « au niveau de la colonne Q (ascending...) »

mais dans le code VBA il y a : Order:=xlDescending

dhany

dhany a écrit :

Bonjour,

Un essai : Worksheets(i).Range("A2:Q" & DernLigne).Sort [Q2], 1

1 pour tri ascendant = tri croissant

2 pour tri descendant = tri décroissant

Je précise cela car tu as écrit « au niveau de la colonne Q (ascending...) »

mais dans le code VBA il y a : Order:=xlDescending

dhany

Merci pour la petite précision en effet j'ai ecrit trop vite. Non ça ne fonctionne pas, il me dit que .sort ne peut pas être associé à .range

Bonjour,

Tu dois pouvoir utiliser Récupérer et Transformer (Power Query) pour traiter tes données.

Cdlt.

@Quentin87,

Désolé, j'ai oublié une chose ! nouvelle version :

Worksheets(i).Range("A2:Q" & DernLigne).Sort Worksheets(i).[Q2], 1

ou :

With Worksheets(i)
  .Range("A2:Q" & DernLigne).Sort .[Q2], 1
End With

dhany

Hello,

j'ai utilisé une fonction de sioux, j'ai mis une petite cellule à côté de mon volet déroulant en =FEUILLE(TEXTE(D2;0)) Ce qui me permet d'avoir le numero de feuille "en dur" et ensuite dans la macro j'appelle cette cellule i et il va me chercher mes petits.

Range("L" & DernLigne -3:"L" & DernLigne).Value

En quoi est ce incorrect ? meme si c'est moche ahah

Merci Jen-Eric !

@Quentin87

Range("L" & DernLigne - 3 & ":L" & DernLigne).Value

dhany

Query est un complement excel, hors je suis sur un fichier extrement lourd deja (extraction bloomberg, une 60 aine de feuilles avec environ 4000 lignes chacunes). Si j'ajoute un complément l'ordi va crash ^^

Dis toi que lorsque j'enregistre mon doc avec les macros, je perds 5 min de ma vie ...

Pour te répondre Dhany non je ne l'avais pas vu car j'ai édit mon post et j'avais pas la notif'. Merci pour ta réponse (trop) rapide !

@Quentin87

Nos messages se sont croisés !

Pour mes 2 messages de 10:48 et de 11 :17, ça a marché ou pas ?

(je parle de l'instruction avec .Sort ; puis celle du Range colonne L)

dhany

dhany a écrit :

@Quentin87

Nos messages se sont croisés !

Pour mes 2 messages de 10:48 et de 11 :17, ça a marché ou pas ?

(je parle de l'instruction avec .Sort ; puis celle du Range colonne L)

dhany

En effet on s'est croisé ahah. Oui j'ai réussi à faire mon tri.

Il ne me reste plus qu'à trouver un moyen de corriger ça et je pense que ça sera bon. Car non le Range L que tu proposes ne fonctionne pas :/

For i = 3 To 24
DernLigne = Sheets(i).Range("A" & Rows.Count).End(xlUp).Row
    If Cells(2, 5).Value = i Then _
            wk_table.Range("A4:A7").Value = Sheets(i).Range("L2:L7").Value And _
            wk_table.Range("B4:B7").Value = Sheets(i).Range("D2:D7").Value And _
            wk_table.Range("C4:C7").Value = Sheets(i).Range("Q2:Q7").Value And _
            wk_table.Range("D4:D7").Value = Sheets(i).Range("E2:E7").Value And _
            wk_table.Range("A8:A11").Value = Sheets(i).Range("L" & DernLigne -3:"L" & DernLigne).Value And _
            wk_table.Range("B8:B11").Value = Sheets(i).Range("D" & DernLigne -3:"D" & DernLigne).Value And _
            wk_table.Range("C8:C11").Value = Sheets(i).Range("Q" & DernLigne -3:"Q" & DernLigne).Value And _
            wk_table.Range("D8:D11").Value = Sheets(i).Range("E" & DernLigne -3:"E" & DernLigne)).Value

        End If
    Next i

Quentin a écrit :

Oui j'ai réussi à faire mon tri.

Bonne nouvelle : ce côté-là est réglé, j'suis content d'avoir pu t'aider !

Pour le Range, tu n'as pas bien lu ce que j'avais indiqué :

Range("L" & DernLigne - 3 & ":L" & DernLigne).Value

donc :

wk_table.Range("A8:A11").Value = Sheets(i).Range("L" & DernLigne - 3 & ":L" & DernLigne).Value

à toi de faire de même pour les autres !

dhany

J'ai recopié mon message mais j'ai teste avec ta formulation et il en veut pas :/

Re,

Tu sous estimes les fonctionnalités de Power query.

Bon maintenant, si tu as besoin de l"ensemble des données soit 4.000 lignes et x colonnes x 60 feuilles, tu as raison de privilégier VBA.

Cdlt.

@Quentin87

Je te laisse essayer ce code VBA :

Option Explicit

Sub Essai()
  Dim i%, DernLigne&
  For i = 3 To 24
    With Worksheets(i)
      DernLigne = .Range("A" & Rows.Count).End(xlUp).Row
      If .[E2] = i Then
        Range("wk_table").[A4:A7] = .[L2:L7]
        Range("wk_table").[B4:B7] = .[D2:D7]
        Range("wk_table").[C4:C7] = .[Q2:Q7]
        Range("wk_table").[D4:D7] = .[E2:E7]
        Range("wk_table").[A8:A11] = .Range("L" & DernLigne - 3 & ":L" & DernLigne)
        Range("wk_table").[B8:B11] = .Range("D" & DernLigne - 3 & ":D" & DernLigne)
        Range("wk_table").[C8:C11] = .Range("Q" & DernLigne - 3 & ":Q" & DernLigne)
        Range("wk_table").[D8:D11] = .Range("E" & DernLigne - 3 & ":E" & DernLigne)
      End If
    End With
  Next i
End Sub

Je pense que ça devrait aller ; sinon, ça serait le côté gauche de l'égalité à modifier.

Sans ton vrai fichier, c'est pas facile ; ce serait mieux de le joindre !

Sinon, tu peux toujours essayer ce que te propose Jean-Eric avec PowerQuery.

dhany

Au temps pour moi les amis j'avais oublié le "&" dans '& ":L"

Du coup plus de bug en pas à pas, mais dès que le If est pris en compte, j'ai une incompatibilité de type. Erreur 13.

Je vais gratter la dessus.

Encore merci

EDIT: Question con, si je veux chopper du texte et pas des valeurs numériques, c'est quand même .value non ?

Avec le code VBA que je t'ai indiqué, le contenu (texte ou autre) de la cellule destination (à droite du signe égal) sera mis dans la cellule source (à gauche du signe égal).

For i = 3 To 24 et If .[E2] = i Then ➯ pour régler l'incompatibilité de type, c'est entre i qui contient un nombre de 3 à 24 et la cellule E2 de chaque feuille ; si elle contient du texte, corrige-le ou essaye avec : If Val(.[E2]) = i Then

dhany

Je ne sais pas pourquoi Dhany, ton code ne bug pas. Mais il incrémente pas du tout :/

Quentin a écrit :

Je ne sais pas pourquoi Dhany, ton code ne bug pas.

C'est parfait !!! j'espère bien que mon code ne bug pas !!! car c'est quand ça bug que ça plante !

Quentin a écrit :

Mais il incrémente pas du tout :/

Comme la boucle est For i = 3 To 24 ... Next i je peux t'assurer formellement que i est incrémenté de 3 à 24 ; sinon, c'est que tes macros sont désactivées !

dhany

Nop les macros sont pas désactivés vu que les autres macro du classeur fonctionnent.

Juste qu'il fait bien les boucles i, mais quand il arrive au moment ou il devrait changer les valeurs il le fait pas.

Je verrais ça demain, pas grave

J'viens d'penser à ceci : j'croyais que wk_table était un nom défini, mais si c'est le nom de ta feuille, tu dois utiliser ce code :

Option Explicit

Sub Essai()
  Dim ws As Worksheet, i%, DernLigne&: Set ws = Worksheets("wk_table")
  For i = 3 To 24
    With Worksheets(i)
      DernLigne = .Range("A" & Rows.Count).End(xlUp).Row
      If .[E2] = i Then
        ws.[A4:A7] = .[L2:L7]
        ws.[B4:B7] = .[D2:D7]
        ws.[C4:C7] = .[Q2:Q7]
        ws.[D4:D7] = .[E2:E7]
        ws.[A8:A11] = .Range("L" & DernLigne - 3 & ":L" & DernLigne)
        ws.[B8:B11] = .Range("D" & DernLigne - 3 & ":D" & DernLigne)
        ws.[C8:C11] = .Range("Q" & DernLigne - 3 & ":Q" & DernLigne)
        ws.[D8:D11] = .Range("E" & DernLigne - 3 & ":E" & DernLigne)
      End If
    End With
  Next i
End Sub

mais comme je t'ai déjà dit : c'est difficile de savoir sans ton fichier...

dhany