Recopie de valeurs selon un horaire correspondant à chacune

[didcac]

Bonjour,
Je souhaite, grâce à une Macro, copier dans le fichier "Total Trade" (joint) certaines données contenues dans le fichier "Spot" (joint).

Dans "Total trade", fichier cible, la colonne A indique la date, la colonne E indique un horaire. La valeur à recopier est en colonne H (Valeur Cac)

Dans "Spot" (fichier d'où on collecte les données), la colonne B indique la date, la colonne C indique un horaire (fractionné par tranches de 30 secondes). En colonne F, se trouve la donnée à copier et à coller dans le tableau "Total Trade" en colonne H.

La difficulté est qu'il faut tenir compte évidemment de la date mais aussi de l'horaire, c'est-à-dire que si la colonne E indique par exemple 14:12:27, il devra copier en colonne H la valeur (du CAC 40) correspondant à 14:12:00 (en réalité, de 14:12:00 jusqu'à 14:12:29, après c'est la valeur 14:12:30 et ce jusqu'à 14:12:59, et ainsi de suite). Donc, toutes les 29 secondes, ça passe à la valeur de la tranche au-dessus.

Merci par avance de m'aider de vos grandes compétences.

NB : comme, dans le fichier complet, il y a 50 000 lignes, merci de m'indiquer dans un Nota bene la ou les valeurs à changer (dans la Macro que vous allez, je l'espère, réussir à écrire), fin qu'elle qu'elle balaye bien tout le fichier dans son intégralité.

 

[Papou-net]

Re : Recopie de valeurs selon un horaire correspondant à chacune

Bonjour didcac,

J'ai commencé à étudier le problème et je bute sur un point.

Si j'ai bien compris la démarche, la valeur de la feuille FINAL, colonne F, doit se reporter dans la tranche horaire (en demi-heure) de la feuille Rapport1, colonne F ?

Or il se trouve que les données figurant sur tes deux fichiers ne correspondent pas à ce critère, ou bien ai-je mal interprété ta demande ?

Merci de m'en dire un peu plus.

Cordialement.

NB : Le problème de se positionner dans la bonne tranche horaire n'en est pas vraiment un : il suffit d'additionner la date (colonne A) avec l'heure (colonne E) en mettant les données au bon format.

 

[mapomme]

Re : Recopie de valeurs selon un horaire correspondant à chacune

Bonjour didcac,

Avant toute chose, on supprime les fusions dans le fichier SPOT (j'ai failli m'arracher les cheveux jusqu'à ce que découvre qu'il y avait de la 'fusion' dans le tableau)

J'avais commencé par utiliser un objet "Scripting.Dictionary" mais je crois bien qu'en version MAC la bibliothèque contenant un tel objet n'existe pas. D'où la présence d'une version PC (que j'ai laissée) dans le code du fichier 'Final'.

J'ai repris le code en le modifiant pour éviter l'objet dictionary. C'est la version MAC_Copy_valueCAC().

Suivant le nom du fichier SPOT et les noms des feuilles, il faudra modifier les constantes du module. Le code est dans le fichier 'Final'.

Option Explicit

Const SpotFileName = "Spot CAC40 2506-0207 v1.xls"
Const SpotSheetName = "Rapport1"
Const FinalSheetName = "FINAL"

Sub MAC_Copy_valueCAC()

Dim refRg As Range, RefValues(), NewValues
Dim Nrow, i, D, T, D_T, Y
Dim topN As Long, middleN As Long, bottomN As Long
   
   'tri des données du fichier SPOT
   With Workbooks(SpotFileName).Sheets(SpotSheetName)
      Set refRg = .Cells(.Rows.Count, "b").End(xlUp).Offset(, 4)
      Set refRg = .Range(.Cells(3, "b"), refRg)
      refRg.Sort key1:=refRg(1, 1), order1:=xlAscending, key2:=refRg(1, 2), _
                  order2:=xlAscending, Header:=xlYes
      'récupération des données du fichier SPOT
      Set refRg = refRg.Offset(1).Resize(refRg.Rows.Count - 1)
      RefValues = refRg.Value
   End With
   
   'Si la date ou heure est du texte, on transforme en date et heure
   For i = LBound(RefValues) To UBound(RefValues)
      If VarType(RefValues(i, 1)) = vbString Then RefValues(i, 1) = DateValue(RefValues(i, 1))
      If VarType(RefValues(i, 2)) = vbString Then RefValues(i, 2) = TimeValue(RefValues(i, 2))
      RefValues(i, 3) = RefValues(i, 1) + RefValues(i, 2)
   Next i
   
   With ThisWorkbook.Sheets(FinalSheetName)
      Nrow = .Cells(.Rows.Count, "a").End(xlUp).Row
      'tableau du résultats
      ReDim NewValues(2 To Nrow)
      For i = 2 To Nrow
         'Calcul de la clef
         D = .Cells(i, "a")
         T = .Cells(i, "e")
         If VarType(D) = vbString Then D = DateValue(D)
         If VarType(T) = vbString Then T = TimeValue(T)
         If Second(T) <= 29 Then
            D_T = D + TimeSerial(Hour(T), Minute(T), 0)
         Else
            D_T = D + TimeSerial(Hour(T), Minute(T), 30)
         End If
         'recherche par dichotomie dela clef dans RefValues
         topN = UBound(RefValues): bottomN = LBound(RefValues)
         Do
            middleN = (topN + bottomN) / 2
            If D_T > RefValues(middleN, 3) Then bottomN = middleN + 1 Else topN = middleN - 1
         Loop Until (D_T = RefValues(middleN, 3)) Or (bottomN > topN)
         If D_T = RefValues(middleN, 3) Then NewValues(i) = RefValues(middleN, 5) Else NewValues(i) = ""
      Next i
      
      'Affichage du résultat dans la colonne H
      .Cells(2, "h").Resize(Nrow - 1).Value = Application.Transpose(NewValues)
   End With
End Sub

Sub PC_Copy_valueCAC()

Dim Dict, RefValues(), NewValues
Dim Nrow, i, D, T, D_T

   Set Dict = CreateObject("Scripting.Dictionary")
   
   With Workbooks(SpotFileName).Sheets(SpotSheetName)
      RefValues = .Range(.Cells(5, "b"), .Cells(Rows.Count, "b").End(xlUp)).Resize(, 5).Value
      For i = LBound(RefValues) To UBound(RefValues)
         Dict.Add RefValues(i, 1) + TimeValue(RefValues(i, 2)), RefValues(i, 5)
      Next i
   End With
   
   With ThisWorkbook.Sheets(FinalSheetName)
      Nrow = .Cells(.Rows.Count, "a").End(xlUp).Row
      ReDim NewValues(2 To Nrow)
      For i = 2 To Nrow
         D = .Cells(i, "a")
         T = .Cells(i, "e")
         If Second(T) <= 29 Then
            D_T = D + TimeSerial(Hour(T), Minute(T), 0)
         Else
            D_T = D + TimeSerial(Hour(T), Minute(T), 30)
         End If
         If Dict.Exists(D_T) Then NewValues(i) = Dict.Item(D_T)
      Next i
      .Cells(2, "h").Resize(Nrow - 1).Value = Application.Transpose(NewValues)
   End With
End Sub

Edit: Bonjour BBT Papou-net 🙂

 

[Papou-net]

Re : Recopie de valeurs selon un horaire correspondant à chacune

Re : didcac

Je n'avais pas vu que tu exerçais sur MAC. N'ayant aucune connaissance dans cet environnement, je ne me sens pas le plus compétent pour poursuivre la discussion. D'autant que mapomme (que je salue) a, je pense, répondu à ton attente.

Cordialement.

 

[didcac]

Re : Recopie de valeurs selon un horaire correspondant à chacune

Bonjour à vous 2, Papou-net et mapomme,
Et merci de vous être aimablement penchés sur mon cas...
En effet, Papou-net, inutile de poursuivre car mapomme a effectivement admirablement trouvé la solution...
Bravo donc à toi, mapomme, c'est super fort comme développement.
Mille mercis !!!
Mais quelques petites explications s'imposent afin que je puisse utiliser cette Macro pour d'autres fichiers :
Déjà, pour commencer, je me positionne dans le fichier cible (appelé ici "Total trade 197364 etc").
Puis, si je comprends bien, au tout début, dans la zone Option Explicit, c'est uniquement là que je dois (au besoin) "agir" et modifier des données qui serviront au processus de réaffectation des valeurs ?
Et je dois donc définir :
1 fichier d'où on extrait les données (ici Spot CAC40),
et 2 "noms de feuilles" qui vont participer à la manoeuvre : Le premier (Rapport1) dont on copie des données, et le second (FINAL) qui les accueille.
Et selon mes besoins, je pourrai renommer ces NOMS si d'autres fichiers (que je devrais retraiter) portent des noms différents. La formule restera telle quelle dans son écriture et sera identique son fonctionnement.
Et je ne toucherai donc RIEN à la formule développée après (autrement dit, on se moque qu'il y ait 50 000 ou 70 000 lignes à recopier dans le fichier cible ?) Il balaiera exhaustivement TOUTE la feuille).
C'est bien cela ?

 

[mapomme]

Re : Recopie de valeurs selon un horaire correspondant à chacune

(re)Bonjour didcac,

C'est effectivement comme tu l'as décrit que ça devrait fonctionner.

La structure des fichiers doit être identiques aux fichiers de l'exemple: ligne de début de chaque tableau; nombre et emplacement des colonnes et surtout pas de cellules fusionnées.

 

[didcac]

Re : Recopie de valeurs selon un horaire correspondant à chacune

Bonsoir,
Me revoici, mapomme...
Effectivement, j'ai un souci de "cellules fusionnées", quand j'essaie d'utiliser un autre fichier du même genre. En fait, elles sont présentes dans le fichier, mais ce n'est pas moi qui les ai introduites (on m'a donné le fichier). Comment faire pour les "enlever" ? Ou du moins pour se débarrasser de ce problème technique ?
Merci de ton aide complémentaire.

 

[mapomme]

Re : Recopie de valeurs selon un horaire correspondant à chacune

Bonjour didcac,

Bonsoir,
Me revoici, mapomme...
Effectivement, j'ai un souci de "cellules fusionnées", quand j'essaie d'utiliser un autre fichier du même genre. En fait, elles sont présentes dans le fichier, mais ce n'est pas moi qui les ai introduites (on m'a donné le fichier). Comment faire pour les "enlever" ? Ou du moins pour se débarrasser de ce problème technique ?
Merci de ton aide complémentaire.

Voir version 2.


On peut aussi le faire "à la main". Sur PC: Sélectionner tout le tableau (en-têtes compris) et clique-droit / Format des cellules /Alignement/ => décocher "Fusion des cellules (on ne devrait pas en être loin même sur MAC)

 

[didcac]

Re : Recopie de valeurs selon un horaire correspondant à chacune

Bonsoir mapomme,
Merci bien.
Qu'il y a-t-il de "différent" entre la version v1 et v2, au juste ??
Sinon, j'ai pu aisément supprimer les cellules fusionnées. En fait, je pensais qu'elle étaient sur le fichier cible mais c'était le contraire, d'où le fait que je ne trouvais initialement rien.
J'ai pu me débrouiller.
Encore merci à toi.