Discussion :

[rossstan_]

Bonjour,

Je dispose d'un fichier excel avec l'historique de prix depuis 5 ans de plusieurs indices boursiers. Cependant, pour certains jours, certains indices n'ont pas coté (bank holiday). Je souhaiterais mettre en place un code VBA qui permettent de supprimer les cellules excel (dates et prix des indices), pour lesquelles un ou plusieurs indices à coté mais pas tous.

Exemple :
Si l'indice SPX a coté le 05/09/2017 mais que tous les autres indices n'ont pas coté, je souhaiterais supprimer les cellules (Dates & PX_LAST) de l'indice SPX au 05/09/2017.

L'idée est d'avoir pour chacune des dates depuis 5 ans, forcément tous les indices qui ont coté.

Ci-joint l'excel.

Merci beaucoup !!!

[Invité]

Bonjour,

Une solution consisterait à utiliser une variable dictionnaire pour lister les dates valides des aires correspondant à vos 7 valeurs dans un onglet cible.
Puis de balayer avec une boucle For Each Next chacune des aires des 7 valeurs pour comparer les dates avec celles de l'onglet cible, et en cas d'égalité de récupérer la côte.
Puis d'éliminer dans l'onglet cible les lignes qui ne contiennent pas les 7 valeurs.

Pour votre variable dictionnaire, référencer la DLL Microsoft Scripting Runtime.

La solution pourrait donner cet aspect.

Pièce jointe 309739

[rossstan_]

Merci Eric pour ta réponse !

Tu pourrais me donner le détail du code stp ?

[Invité]

Tu pourrais me donner le détail du code stp ?

Oui mais sous conditions.

Il est possible que d'autres te fournissent une solution VBA clé en main, pas de soucis. Dans le cas contraire, il te suffira de m'envoyer un message privé si tu tiens à avoir une solution VBA.

Si ton un fichier ne doit être produit qu'une seule fois, dans ce cas, le VBA est inutile. La solution ne prend que 5 minutes à la main en recopiant les couples et en identifiant les valeurs pour chacun dans un seul onglet puis en créant ensuite un TCD.

[PBELL]

Bonjour,
Effectivement, soit cela prend 10 minutes en direct, soit, si c'est répétitif, une macro VBA est simple à écrire.

En fait j'ai retenu la même méthode dans les 2 cas:
1) sur une nouvelle feuille, je construit une liste faite toutes les séries l'une sous l'autre, avec 3 colonnes : Date, Valeur et Nom
2) à partir de cette liste, je construit un tableau croisé dynamique par date avec les date en lignes avec le nombre d'enregistrements (count) : si il y en a 7, la date doit être retenue
3) je fait un vlookup / rechercheV dans la colonne 3 de ton tableau en cherchant la valeur de la colonne 2 du tableau dynamique. si le nombre est 7 la ligne est bonne sinon non.

Pour ce qui est de la macro, la voici :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
Sub Macro1()'
' Flitre les cotations lorsque les 7 valeurs existent pour une date
' M_thode :
' 1- copie les 7 listes (date/valeur) l'une dernière l'autre sur une nouvelle feuille avec 3 colonnes (date, nom, valeur)
' 2- cree un tableau croise dynamique avec les dates en ligne, les noms en colonne et le count des valeurs
' 3- filtre les dates pour lesquelles il y a un count total de 7
' 4- utilise cette liste de date pour un vlookup
 
 
Feuille1 = ActiveSheet.Name
Sheets.Add
ActiveSheet.Name = "Liste"
Cells(1, 1) = "Date"
Cells(1, 2) = "Valeur"
Cells(1, 3) = "Nom"
Position = 2 'prochaine position a coller dans la liste globale
 
 
For Serie = 1 To 7 ' met les 7 series en une seule colonne
    Sheets(Feuille1).Select
    Nom = Cells(1, 2 + (Serie - 1) * 3)
    NbLignes = Cells(7, 1 + (Serie - 1) * 3).CurrentRegion.Rows.Count - 2 ' je retire 2 pour l'entête en ligne 7 et le #Name en ligne 8
    Range(Cells(9, 1 + (Serie - 1) * 3), Cells(8 + NbLignes, 2 + (Serie - 1) * 3)).Copy
    Sheets("Liste").Select
    Cells(Position, 1).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Range(Cells(Position, 3), Cells(Position - 1 + NbLignes, 3)) = Nom
    Position = Position + NbLignes
Next Serie
Columns("A:A").NumberFormat = "dd/mm/yy;@" ' ˆ changer en jj/mm/aa si version francçaise !
 
 
'creation d'un tableau croise dynamique avec le nombre de valeur par date
Range("A1").CurrentRegion.Select
ActiveWorkbook.Names.Add Name:="BDATA", RefersToR1C1:=Selection
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "BDATA").CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Name = "Pivot"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
        .Orientation = xlRowField
        .Position = 1
    End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("Valeur"), "S#Valeur", xlCount
 
 
'pour chaque serie, chaque ligne, ajoute le nombre de serie pour la date donnee
Sheets(Feuille1).Select
For Serie = 1 To 7
    NbLignes = Cells(7, 1 + (Serie - 1) * 3).CurrentRegion.Rows.Count - 2 ' idem premieère boucle
    Range(Cells(9, 3 + (Serie - 1) * 3), Cells(8 + NbLignes, 3 + (Serie - 1) * 3)).FormulaR1C1 = "=VLOOKUP(RC[-2],Pivot!C1:C2,2,FALSE)"
    J = 9
    While J < (NbLignes + 9)
    If Cells(J, 3 + (Serie - 1) * 3) <> 7 Then 'la date n'a pas les 7 series, on supprime
        Range(Cells(J, 1 + (Serie - 1) * 3), Cells(J, 3 + (Serie - 1) * 3)).Delete shift:=xlUp
        NbLignes = NbLignes - 1
        Else
        J = J + 1
        End If
    Wend
    ' Efface la colonne Vlookup
    Range(Cells(9, 3 + (Serie - 1) * 3), Cells(8 + NbLignes, 3 + (Serie - 1) * 3)).ClearContents
Next Serie
Sheets("Pivot").Delete
Sheets("Liste").Delete
End Sub

Attention au format de date : comme je passe de PC format US/Excel Pro à un Mac format FR/Excel2016 (ben oui !), il se peut que le format "dd/mm/yyyy" doivent être changé en "jj/mm/aaaa".
J'ai testé la macro et elle fonctionne sur ton fichier.

Par contre, je ne saurai trop conseiller d'éviter des fichiers avec des séries de données en colonnes. Mettre sur une seule table (qui inclus le nom de l'indice boursier) est bien plus simple à manipuler.

Comme je suis nouveau sur ce forum VBA, toutes les critiques sont les bienvenues: C'est la meilleure façon d'apprendre ;-)
Cordialement