[XL-2007] TRI EXCEL Personnalisé [Résolu]

[orsanofear]

Bonsoir à tous, j ai un petit souci de tri sur ma feuille excel.

En fait j aimerais trier par date +recente a +ancienne (colonne J) sur le screenshot. Mais je veux que pour la ligne 2 par exemple avec date 16.02.2012, (M2 à M5,N2 à N5,O2 à O5,P2 à P5,Q2 à Q5,R2 à R5,S2 à S5) reste en place. Donc en fait que tout le bloc reste avec la date que je veux trier..

Si ce n est pas clair m hesitez pas a me le faire savoir"! Merci d avance pour vos propositions

[78chris]

Bonjour

Dans ce cas il faut dupliquer les données des blocs quitte à les rendre non visibles par une mise en forme conditionnelle sinon il faut une macro si tu ne veux pas le faire manuellement en sélectionnant bloc par bloc.

[orsanofear]

D'accord Chris merci pour ta réponse ! Aurais tu une idée de macro sous la main?

[casefayere]

Après bien des efforts, voici un résultat provisoire qui comporte encore des défauts car je n'arrive pas à bien régler le problème si deux dates sont identiques :

le principe :
1 - copier les données sur une feuille provisoire en intégrant par ordre de date
2- recopier le resultat obtenu dans la feuille originale
3 - supprimer la feuille provisoire

il existera (à coup sur) des retouches à faire :

Code :

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
38
39
Private Sub CommandButton1_Click()
Dim Ch As Integer, deb As Range, derlg As Integer, x As Integer, repere As Integer, fin As Integer
Dim dertab As Integer
Application.ScreenUpdating = False
With Sheets("feuil1")
  dertab = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
End With
 
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "copie"
derlg = 0: Set deb = Nothing: fin = 0
Sheets("feuil1").Range("A1:S1").Copy Destination:=Worksheets("copie").Range("A1")
Sheets("copie").Range("J2") = CDate(Application.WorksheetFunction.Large(Sheets("feuil1").Range("J2:J" & dertab), 1))
Set deb = Sheets("feuil1").Range("J2:J" & dertab).Find(Sheets("copie").Range("J2"))
For x = 3 To dertab
  If Sheets("feuil1").Range("J" & x) <> "" Then x = x - 1: Exit For
Next x
Sheets("feuil1").Range("A" & deb.Row, "S" & deb.Row + x - 2).Copy Destination:=Worksheets("copie").Range("A2")
 
derlg = x + 1
For repere = 2 To Application.WorksheetFunction.CountIf(Sheets("feuil1").Range("J2:J" & dertab), "<" & deb) + 1
  Set deb = Sheets("feuil1").Range("J2:J2" & dertab).Find _
        (CDate(Application.WorksheetFunction.Large(Sheets("feuil1").Range("J2:J" & dertab), repere)))
  For x = deb.Row + 1 To dertab + 1
    fin = fin + 1
    If Sheets("feuil1").Range("J" & x) <> "" Then Exit For
  Next x
  With Worksheets("copie")
    Sheets("feuil1").Range("A" & deb.Row, "S" & x - 1).Copy Destination:=.Range("A" & derlg)
    derlg = derlg + fin
    fin = 0
  End With
Next repere
Worksheets("copie").Range("A1:S" & dertab).Copy Destination:=Worksheets("feuil1").Range("A1")
Application.DisplayAlerts = False
Sheets("copie").Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

[orsanofear]

Un gros gros merci !! Je vais voir si a partir de ton travail je peux arriver exactement au resultat escompté..

Edit : Après quelques tests, je note deux gros problèmes :

- Les mises en formes conditionnelles (couleur rouge/vert si Q< ou >P) dans la colonne P sautent toutes. une fois la macro terminée.

plus embettant..

-sur les 200 lignes de mon fichier original, seulement 76 subsistent après avoir lancé la macro. Les dates les plus recentes ne sont plus sur la feuille.

Si quelqu un a une idée pour ces deux problèmes..merci

A bientot

[casefayere]

Bonjour orsanofear, le forum,
il faudrait que tu envoies un fichier si possible afin de me faire une idée, car je répète que je n'arrive toujours pas à gérer la solution quand tu as des dates identiques en "J"

Quant à la mise en forme conditionnelle, je ne savais pas qu'elle existait et n'en ai pas tenu compte.

Je ne pourrai pas regarder ton code ce matin.

A+

[orsanofear]

Bonjour casefayere,

voici le fichier en question en pj

Brève explication concernant ce fichier :

Chaque ligne correspond à un produit

-avec en A date d emission, en B le client, en F le code ISIN du produit (la carte d identité du produit) en I date de valuation (maturité/expiration du produit), et en J date de la prochaine observation (date à laquelle l investisseur touche son coupon).

-en M on a les actions qui composent le produit et en P on a le prix spot du produit (dernier prix connu), ces prix se trouvent dans les colonnes X à AI et cest dans cette colonne qu il y a des mises en formes conditionnelles, qui dépendent de la colonne Q. En fait si P<Q = rouge si P>Q = vert.

J espère que le fichier et ces quelques infos pourront t aider, si tu as besoin de plus de précision dis moi

A+

[casefayere]

Je travaillais gentiment sur ton fichier quand je me suis aperçu qu'il existait un autre tableau, à partir de la colonne "X", ce qui remet tout mon travail en cause,

je vais essayer de continuer mais je ne promets vraiment rien.

[orsanofear]

Je peux le virer le tableau si ca te facilite la tache !

Donc ne recommence pas tout a cause de ca je m arrange apres

[casefayere]

Peut-être une solution qui ne tient pas compte de tes MFC et des calculs dans les cellules référents aux colonnes X et suivantes

Code :

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
38
39
40
41
42
43
44
 
Private Sub CommandButton1_Click()
Dim deb As Range, derlg As Integer, Rech As Date, z As Integer
Dim dertab As Integer, plage As Range, cel As Range, Bloc As Range, y As Integer
Application.ScreenUpdating = False
With Sheets("feuil1")
  dertab = .Range("M" & .Rows.Count).End(xlUp).Row
  derlg = 0: Set deb = Nothing: Set Bloc = Nothing: Rech = Empty
  Set plage = .Range("J2:J" & dertab)
  Sheets.Add After:=Sheets(Sheets.Count)
  ActiveSheet.Name = "copie"
  .Rows("1:1").Copy
  Sheets("copie").Rows("1:1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
  '********************************************************************************************
  'copie des blocs et suppression dans feuil1
For z = 1 To WorksheetFunction.CountIf(plage, ">1")
  Rech = CDate(Application.WorksheetFunction.Large(plage, 1))
  Set deb = plage.Find(Rech)
  For Each cel In .Range("J" & deb.Row + 1, "j" & dertab)
    If cel = deb Then Set deb = cel
  Next cel
  For y = deb.Row + 1 To dertab
    If .Range("M" & y) = "" Then y = y - 1: Exit For
  Next y
  derlg = Sheets("copie").Range("M" & Sheets("copie").Rows.Count).End(xlUp).Row
  Set Bloc = .Range("A" & deb.Row, "S" & y)
  Bloc.Copy
  Sheets("copie").Range("A" & derlg + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
      False, Transpose:=False
  Bloc.EntireRow.Delete
  dertab = .Range("M" & .Rows.Count).End(xlUp).Row
  Set plage = .Range("J2:J" & dertab)
Next z
Sheets("copie").Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
End With
Application.DisplayAlerts = False
Sheets("copie").Delete
Application.DisplayAlerts = True
 
Application.ScreenUpdating = True
End Sub

et le fichier :

[orsanofear]

Super merci Dominique!!!! ta dernière macro permet de resoudre le 2eme problème. Pense tu qu'il est possible de garder le MFC dans la colonne P?

A+ bonne soirée

[casefayere]

tant mieux,
pour la MFC, je ne sais pas car quand on regarde tes regles, il y a des références à "REF", certainement à cause de la suppression des colonnes => X, donc pour l'instant je ne peux te répondre.

PS : tu remarqueras que je n'ai pas utilisé "SpecialCells(xlCellTypeLastCell" ou "currentregion" pour trouver la dernière ligne car ces commandes gardent en mémoire les cellules effacées jusqu'à l'enregistrement, je me suis donc fié à la col "M" qui, je pense sera toujours remplie au maximum (mais fais-y attention)

A+ bonne soirée également

[orsanofear]

Tu as tout à fait raison, la M sera toujours remplie et la colonne P dépendait des colonnes X et suivantes..

MAIS

..je vais changer cela en mettant une formule dans les cellules de la colonne P. Cette formule fera un link entre mon fichier excel et bloomberg avec la fonction BDP().

Donc il n'y aura plus de colonne après S. Le prix spot de chaque action se mettra à jour automatiquement avec la fonction de bloomberg spécifique à chaque action biensur.

Ma question est la suivante : Avec ce changement, pense tu que la MFC pourra rester en place si on modifie en ce sens ta dernière macro?

A+ Bon week et encore merci pour le temps que tu me consacre

[casefayere]

Je réfléchissai encore à ce problème auquel on peut pallier, je m'explique :

- si dans le code, je prévois au départ de réintégrer les colonnes > X....

après :

Code :

1
2
  Sheets("copie").Rows("1:1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

tu colles :

Code :

1
2
3
4
  'coller les colonnes X...>
  .Columns("X:AI").Copy
  Sheets("copie").Range("X1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

- sur ton fichier original, il faut transformer les cellules relatives de J en absolu, c'est le plus long à faire car opération manuelle avec F4 (tu connais ?)

je n'ai pas le courage de faire cette manip moi-meme

- tu provoques le tri après avoir fait ça

je pense que ça devrait fonctionner

je n'ai pas le courage de faire cette manip moi-meme

d'autre part, j'ai remarqué que tu as des cellules vides à l'intérieur de la 1ère plage, perso, je les ai fait sauter (A48:S49 et A180:S181)

Comme je suis gentil, je l'ai fait

[casefayere]

as-tu vu mon dernier post ? avec le fichier, la MFC est présente

Alors je dois mettre en absolu tout J, colonne qui contient les dates..Mais je ne comprend pas l'impact que ca aura sur la MFC de la colonne P..

Pour ce qui est du code pour réintégrer les colonnes > X à quoi sert il si je modifie le fichier avec la fonction bdp() ?

je ne connais pas

[orsanofear]

Dominique je te dois une fière chandelle !

Pour ce qui est de la fonction bdp() c'est une fonction qui va chercher le dernier prix d'une action/indice/matière première..

Par exemple je rentre dans une cellule P la fontion suivante : BDP(“IBM US Equity”,“Px_Last”).
Cela me donnera le dernier prix de l'action IBM directement dans la cellule sans avoir besoin d'aller chercher dans les colonnes >X.
Mais pour cela tu dois avoir Bloomberg sur le poste que tu utilise..

Tu pense que ca va faire tout faire foirer si je fais ca? (supprimer les colonnes >X et mettre la fonction dans les cellules de la colonne P). Parceque les données dans P ne changeront pas. Seulement la manière de les obtenir sera différente et bien plus simple puisque je n'aurai plus besoin d'aller chercher les prix dans les autres colonnes >X

[casefayere]

Quand je regarde tes règles de MFC, je pense qu'il n'y aura pas de problème par rapport à Q mais 2 règles ne s'appliquent plus car la référence est "= #REF!" et je ne sais pas quelle était cette référence.

(quand tu auras fini, tu vas pouvoir arroser ça)

[orsanofear]

D'accord bon super je vais essayer Lundi (je n'ai pas Bloomberg chez moi). En effet tu ne peux pas connaitre =REF puisque c'est le dernier prix coté de l'action, ces cellules sont updatées toutes les 30secondes.

Oui je vais arroser ca demain soir ahah..si seulement je pouvais accompagner mon repas avec les cèpes que tu as en avatar..



Bon Week end Dominique..Je te MP Lundi pour te dire si ta création fonctionne bien

[casefayere]

Quand je n'ai rien de spécial à faire, je pense encore à ce qui est entamé et me souviens de ton premier post :

En fait j aimerais trier par date +recente a +ancienne (colonne J)

es-tu sur que ma proposition est la bonne, exemple :
le 13/02/2012 est plus près que le 03/06/2012 (au hasard), ne fallait-il pas trier avec les dates les plus près d'aujourd'hui, quitte à mettre les dates dépassées en fin de colonne ?

Mais tu dois avoir tes raisons.

A lundi, bon WE Thony

[orsanofear]

Non tu as tout a fait raison une fois de plus ! Mais je n'ai pas voulu te le dire parce que tu as fait énormément de boulot et je voulais essayer de trouver comment faire ce tri tout seul..sans te solliciter une nouvelle fois. Donc oui je veux faire du plus proche au plus lointain.

Vraiment c'est assez impressionnant, j'aimerais avoir un centième de ton savoir et de ta curiosité!