Tester une condition sur une ligne et copier la colonne associée Le sujet est résolu

Y compris Power BI, Power Query et toute autre question en lien avec Excel
j
jeunepousse
Nouveau venu
Nouveau venu
Messages : 3
Inscrit le : 7 août 2019
Version d'Excel : 2016

Message par jeunepousse » 7 août 2019, 15:22

Bonjour,
Je suis complètement débutant en VBA donc n'hurlez pas au scandale en voyant ma macro :wink:
je vous explique ma situation:
- dans un fichier il y a en ligne 2 des intitulés de colonne avec une période type 2019.05 puis 2019.06 puis 2019.07 etc. Et le libellé de la colonne se répète plusieurs fois. Exemple, on peut imaginer qu'il y a 12 colonnes (une par période) pour le Chiffre d'affaires, puis 12 pour les coûts etc.
J'ai mis en PJ avec un extrait des colonnes qui se répètent comme ça beaucoup beaucoup de fois.
Extrait des colonnes.PNG
Extrait des colonnes.PNG (6.33 Kio) Vu 1339 fois
Ce que je veux faire c'est une macro qui va rechercher la période précédente dans la ligne 2. Quand elle trouve la valeur (par exemple 2019.06) elle copie la colonne (les 100 premières lignes ou jusqu'à la dernière ligne qui n'est pas vide) et la colle dans la colonne juste après. Enfin, la macro remplace 2019.06 par la période en cours 2019.07 dans chaque formule de chaque ligne.

J'ai réussi à faire un morceau de code (hier) qui fonctionne et fait ce que je lui demande mais il suppose que je parte de la cellule active. Ce que je veux faire c'est qu'Excel fasse l'opération tout seul à chaque fois qu'il trouve la période précédente sans que j'ai besoin de cliquer. Car c'est un truc que je fais chaque mois et c'est très long (je le fais à la main) et il faut refaire l'opération une bonne cinquantaine de fois - copier - coller - remplacer. La formule contenue dans chaque ligne fait référence à un autre classeur dont un nouveau est créé chaque mois. D'où le fait que je dois attendre le début de chaque mois avant de copier les formules et de les modifier pour que le lien se fasse bien avec le fichier de la bonne période (cf la partie remplacer dans ma macro)

En début de code j'ai mis le mode de calcul en manuel car le fichier est lourd est met du temps à s'actualiser. Il reste généralement en manuel en permanence sauf quand j'ai besoin de l'actualiser.

Je vous rajoute le morceau de code qui fonctionne et le morceau de code que j'ai essayé de faire pour commencer à automatiser la recherche de la colonne mais qui ne fonctionne pas :
Sub Copy_column_to_another_column()


Application.Calculation = xlManual
 
Range(ActiveCell, Cells(1000, ActiveCell.Column).End(xlUp)).Select
Selection.Copy
Selection.Offset(0, 1).Select
ActiveSheet.Paste

Selection.Replace What:=Range("Période_précédente"), Replacement:=Range("Période_en_cours"), LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Sub
Au-dessus le morceau de code qui fonctionne et en-dessous le morceau de code que j'essaye de rajouter pour automatiser:
For Each c In Range("C2:GG2").Cells
If c.Value = Range("Période_précédente") Then c.Address.EntireColumn.Copy Destination:=Range(c.EntireColumn.Offset(0, 1))


Next
Dans ce bout de code qui ne fonctionne pas mon problème vient sans doute (en partie) du fait que le "c" est une valeur est pas une adresse de cellule et donc je ne peux pas m'en servir pour sélectionner/copier toutes les valeurs qu'il y a en-dessous.

Si une âme charitable veut bien m'aider à automatiser et à comprendre ce truc.
Merci beaucoup :)
Avatar du membre
paritec
Passionné d'Excel
Passionné d'Excel
Messages : 3'864
Appréciations reçues : 389
Inscrit le : 7 juin 2011
Version d'Excel : W10 - 2003 FR - 2010 FR

Message par paritec » 7 août 2019, 15:56

Bonjour jeune pousse le forum
bon alors un petit fichier en pièce jointe avec les explications dedans et on va t'aider
pour le moment le faite de recopier la colonne X sur la colonne X+1 , je ne comprends pas bien, mais tu as sans doute une bonne raison, alors fichier et explications claires dans le fichier de ce que tu souhaites et si possible le pourquoi, et on va t'aider
a+
Papou :)
Des bonnes explications et des petits fichiers représentatifs vont nous aider à vous aider !!!!
j
jeunepousse
Nouveau venu
Nouveau venu
Messages : 3
Inscrit le : 7 août 2019
Version d'Excel : 2016

Message par jeunepousse » 7 août 2019, 16:49

Bonjour Paritec,

je ne peux pas mettre le fichier ici car il est beaucoup trop gros et plein de lien. Ce que j'ai fais c'est que j'ai créé une version simplifiée en ne gardant qu'un extrait de la feuille concernée. il faut imaginer que toutes les colonnes du passé sont pleines de formules avec des liens allant chercher les informations dans le fichier source du mois en question.

Dans mon exemple, la formule qui apparait dans la colonne jaune est du style : =SOMMEPROD(--($C4=[1 - Source 2019.06.xlsm]EXTRACTION'!$E$3:$E$98);INDEX([1 - Source 2019.06.xlsm]EXTRACTION'!$E$3:$BB$98;0;EQUIV($H$1;[1 - Source 2019.06.xlsm]EXTRACTION'!$E$3:$BB$3;0)))

Sauf que chaque mois nous devons créer un nouveau fichier source. La seule différence c'est le nom du dossier qui devient 2019.07 à la place de 2019.06 et le nom du fichier qui devient 2019.07 à la place de 2019.06. Evidemment les données changent aussi :lol:

Donc dans ma colonne verte il n'y a pas de formule avant que le fichier source du mois soit créé (je dois attendre d'avoir accès à la nouvelle source pour créer un nouveau lien). Ce que je fais chaque mois c'est que je récupère les formules de la colonne du mois précédent (colonne jaune) puis je les colle dans la colonne du mois en cours (colonne verte) et dans la formule je remplace 2019.06 par 2019.07. Dans l'exemple je me suis arrêté à la colonne CG mais dans la réalité je dois faire cette opération environ une cinquantaine de fois.

Pour l'instant dans la macro que j'ai réalisée (premier message) je me positionne sur H7 puis je lance la macro qui va copier toutes les formules de la colonne H jusqu'à la dernière ligne qui n'est pas vide. Puis elle va coller à partir de la la cellule I7 et enfin finir en remplaçant dans la formule la période précédente par la période en cours (pour faire le lien avec le nouveau dossier et le nouveau fichier source). Avec cette première macro c'est toujours plus rapide que faire copier-coller-remplacer à la main mais ça m'oblige à me positionner à chaque fois dans la bonne cellule et à lancer la macro autant de fois qu'il y a de colonne jaune dans le classeur.

Ce que je cherche à faire c'est créer une macro qui va chercher la période précédente dans la ligne 6 (ici 2019.06) et à chaque fois que la macro va trouver la période précédente (2019.06) elle va copier toutes les formules de la colonne vers la colonne 2019.07 qui se trouve juste à côté. Et enfin, la macro va modifier chaque formule de la colonne 2019.07 pour changer le chemin d'accès au fichier source en remplaçant 2019.06 par 2019.07.

Et ainsi grâce à la macro, quand le mois prochain je vais vouloir mettre à jour le classeur, je lance la macro et les formules de la colonne 2019.07 seront copiées dans la colonne 2019.08 et le lien avec le nouveau fichier source se fera aussi automatiquement.

J'espère qu'avec cet exemple de fichier et quelques explications en plus c'est plus clair. Mais comme le fichier sur lequel je veux faire la macro est plein de lien vers d'autres fichiers ce n'est pas évident à expliquer. :)
Exemple fichier simplifié.xlsx
(13.8 Kio) Téléchargé 8 fois
Avatar du membre
paritec
Passionné d'Excel
Passionné d'Excel
Messages : 3'864
Appréciations reçues : 389
Inscrit le : 7 juin 2011
Version d'Excel : W10 - 2003 FR - 2010 FR

Message par paritec » 8 août 2019, 07:33

Bonjour JeunePousse le forum
Bon alors ton fichier avec tes liens doit être une jolie usine à gaz
Mais pour ce qui est de la macro qui ne marche pas, tu peux essayer si tu as la même structure donc ta ligne avec tes 2019.01 etc sur la ligne 6 , donc pour le fichier que tu as joint.
Maintenant est-ce que tu as besoin de copier toute la colonne ou seulement de la ligne 7 à la dernière ligne??? Là aussi il faudrait savoir, car moi j'ai copié la colonne complète, ce qui impose de renommer la cellule de la ligne6 de la colonne copiée.
Pour le reste bah avec un fichier sans rien à pouvoir tester !!!! ??? enfin je pense qu'un "replace" pourra faire l'affaire mais encore une fois sans fichier avec les formules à modifier!!!!
a+
Papou :)
Sub cop()
    Dim c As Range, mem$
    With Feuil1
        For Each c In .Range("C6:GG6")
            If c.Value = .Range("A1") Then mem = c.Offset(0, 1).Value: .Columns(c.Column).Copy .Columns(c.Column + 1): c.Offset(0, 1) = "'" & mem
        Next c
    End With
End Sub
Jeune Pousse V1.xlsm
(29.15 Kio) Téléchargé 4 fois
Des bonnes explications et des petits fichiers représentatifs vont nous aider à vous aider !!!!
Avatar du membre
paritec
Passionné d'Excel
Passionné d'Excel
Messages : 3'864
Appréciations reçues : 389
Inscrit le : 7 juin 2011
Version d'Excel : W10 - 2003 FR - 2010 FR

Message par paritec » 8 août 2019, 08:29

Re JeunePousse le forum
après avoir relu ta demande, je pense que ce que tu veux est dans la version 2 avec la copie de seulement la ligne 7 à la fin, dans le fichier exemple la colonne B ligne 86
Mais à toi de tester, si le replace que tu avais fonctionnait je l'ai remis idem j'ai juste raccourci PP et PC car vos variables à rallonges !!!
Maintenant si le nombre de colonnes doit être supérieur à CG il faudra me le dire je rajouterai la recherche en automatique
a+
Papou :)
Jeune Pousse V2.xlsm
(32.3 Kio) Téléchargé 5 fois
Des bonnes explications et des petits fichiers représentatifs vont nous aider à vous aider !!!!
j
jeunepousse
Nouveau venu
Nouveau venu
Messages : 3
Inscrit le : 7 août 2019
Version d'Excel : 2016

Message par jeunepousse » 8 août 2019, 09:45

Bonjour Paritec,

C'est exactement ce dont j'ai besoin (fichier V2). Merci beaucoup.
J'ai adapté pour ça colle au bon nombre de colonne que j'ai dans le fichier. Je viens juste de faire le test et tout fonctionne très bien. C'est au top :mrgreen:
Pas besoin de vous embêter à faire la recherche en automatique pour le nombre de colonne. Je prends un nombre de colonne un peu plus long que ce dont j'ai besoin et tout va bien.

Pour ma compréhension du code et si tu as le temps, est-ce que tu pourrais m'expliquer comment fonctionne quelques trucs ?
Par exemple à quoi correspond le "mem$" ou comment se fait le collage sans que le Paste apparaisse ? Aussi, à quoi correspond le .End(3).Row ?

Le code en soit ne doit pas être très compliqué mais pour moi qui commence tout juste le VBA c'est une langue complètement étrangère.

Encore merci. :)
Avatar du membre
paritec
Passionné d'Excel
Passionné d'Excel
Messages : 3'864
Appréciations reçues : 389
Inscrit le : 7 juin 2011
Version d'Excel : W10 - 2003 FR - 2010 FR

Message par paritec » 8 août 2019, 09:57

Re JeunePousse le forum
avec la recherche pour les colonnes
le (3) correspond à xlup, c'est pour chercher la dernière ligne remplie en partant de la dernière ligne du fichier et en remontant
a+
Papou :)
Jeune Pousse V3.xlsm
(32.37 Kio) Téléchargé 2 fois
Des bonnes explications et des petits fichiers représentatifs vont nous aider à vous aider !!!!
Avatar du membre
paritec
Passionné d'Excel
Passionné d'Excel
Messages : 3'864
Appréciations reçues : 389
Inscrit le : 7 juin 2011
Version d'Excel : W10 - 2003 FR - 2010 FR

Message par paritec » 9 août 2019, 06:56

Bonjour Jeune pousse le forum
voilà pour tes questions, la macro commentée dans la fichier joint
a+
Papou :)
Jeune Pousse V3.xlsm
(32.25 Kio) Téléchargé 1 fois
Des bonnes explications et des petits fichiers représentatifs vont nous aider à vous aider !!!!
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message