Discussion :

[Hasqueilz]

Bonjour à tous,

Tout d'abord, bonne année 2024 !

J'ai un problème concernant VBA, étant nul, je suis parvenu à résoudre la majorité (90%) de mes problématiques grâce aux fonctionnalités de base d'Excel (à savoir les formules), cependant il me reste une chose que j'aimerais réaliser, cependant cela est impossible avec les formules, et peut être même avec VBA, mais je préfère tout de même poser la question sur un forum d'expert en VBA.

J'aimerais savoir si il était possible de créer une macro VBA, qui, lorsqu'on clique sur un bouton, complète automatiquement certaines colonnes (tirer la formule vers le bas) avec la bonne formule ? Ce sont des formules à base de RECHERCHEV, cependant, chaque mois, le fichier utilisé pour la rechercheV est différente (ce mois-ci, j'utilise le mois de janvier, le mois prochain, j'utilise le mois de février).

J'avais une petite idée du raisonnement à avoir : uniformiser le nom des fichiers (i.e pour le mois de janvier, le fichier se nomme ... janv_01; pour le mois de février, le fichier se nomme ... fev_01) sachant que dans mon fichier/tableau excel, il y a une colonne attribuée pour chaque mois.

En gros, chaque mois, je créé une nouvelle colonne et j'écris le mois actuel dans la cellule L3, exemple : pour le mois de février, je vais insérer une nouvelle colonne, puis je vais écrire "février" (mais je peux écrire un nom similaire au fichier à savoir fev_01 pour faciliter le code VBA peut être), suite à ça, je vais écrire ma fonction rechercheV puis tirer la fonction jusqu'en bas du tableau. Après avoir effectuer ça, il me reste qu'à mettre à jour 1 ou 2 autres colonnes du tableau, à savoir la colonne AH, qu'on va nommer ici "poids ACTUEL", donc je supprime la fonction rechercheV qui est basé sur le mois de janvier, et j'écris la nouvelle fonction rechercheV qui est basé sur le fichier de février. Grossomodo, c'est ça. En somme, c'est un suivi mensuel que j'effectue en me basant sur un fichier que je reçois mensuellement.

En espérant avoir été clair,

Bonne journée à tous !

[BENNASR]

Bonjour
As tu essayé avec Powerquery??
Avec zéro codage, tu peux faire toutes les jointures et comparaisons possibles
il y a beaucoup de tuto sur le net pour initier l'usage de cet outil

[Hasqueilz]

Bonjour
As tu essayé avec Powerquery??
Avec zéro codage, tu peux faire toutes les jointures et comparaisons possibles
il y a beaucoup de tuto sur le net pour initier l'usage de cet outil

Bonjour,
Non, j'ai un peu regardé sur internet mais je ne pense pas que c'est ce que je recherche. De ce que je comprendrais, powerquery permet d'importer des données.
Moi ce que je recherche, c'est : cliquer sur un bouton qui permet d'écrire la formule suivante dans 3 colonnes du tableau : rechercheV(....................;janv) pour le mois de janvier, rechercheV(...............;février) quand on sera en février, sachant que les différents mois sont attribués à une colonne du tableau.

Cordialement.

[Pierre Fauconnier]

Bonjour


Power Query permet l'acquisition de données, mais également des liens de type "RECHERCHEV" entre 2 tables.

Il faudrait une copie d'écran de l'input et de l'output souhaité, ainsi qu'une explication claire de ce qui est attendu, pour pouvoir se faire une idée et voir si le VBA est vraiment nécessaire. J'ai des doutes sur le fait de devoir créer des RECHERCHEV par VBA et souvent, ce besoin nait d'une conception fragile de son classeur.

[Hasqueilz]

Bonjour


Power Query permet l'acquisition de données, mais également des liens de type "RECHERCHEV" entre 2 tables.

Il faudrait une copie d'écran de l'input et de l'output souhaité, ainsi qu'une explication claire de ce qui est attendu, pour pouvoir se faire une idée et voir si le VBA est vraiment nécessaire. J'ai des doutes sur le fait de devoir créer des RECHERCHEV par VBA et souvent, ce besoin nait d'une conception fragile de son classeur.

Bonjour,

effectivement;

je vous met les screens ci dessous, je pense que ce sera plus clair, en gros pour le mois de janvier par exemple, au lieu d’inserer une colonne à la colonne L puis de reecrire la formule recherche V en modifiant le lien d’accès du fichier (12_2023 qui se transforme en 01_2024 puis pour février 02_2024 etc…) j’aimerais cliquer sur un bouton qui réalise ces étapes (l’insertion de la colonne et l’écriture du mois est facultative dans la programmation, si cela pose des soucis).

Cordialement.

[Alex020181]

Bonjour,

Je n'utilise pas PQ alors je laisse cette option aux mains d'autres personnes.

En VBA par contre il est tout à fait possible d'insérer des formules dans des cellules d'une feuille d'un classeur Excel.
Ces formules peuvent être construites de toutes pièces. Donc tu peux créer des formules personnalisées en incluant automatiquement des changements de colonnes, de zones, ou même changer complètement une formule selon des critères.
Et tu peux également soit mettre ta formule sur l'ensemble d'une zone précise soit la mettre en une seule cellule et la décaler ensuite.

[Hasqueilz]

Bonjour,

Je n'utilise pas PQ alors je laisse cette option aux mains d'autres personnes.

En VBA par contre il est tout à fait possible d'insérer des formules dans des cellules d'une feuille d'un classeur Excel.
Ces formules peuvent être construites de toutes pièces. Donc tu peux créer des formules personnalisées en incluant automatiquement des changements de colonnes, de zones, ou même changer complètement une formule selon des critères.
Et tu peux également soit mettre ta formule sur l'ensemble d'une zone précise soit la mettre en une seule cellule et la décaler ensuite.

Bonjour,

Merci de votre réponse, donc vous êtes dites qu'il est possible de faire ça avec VBA (Insertion d'une colonne, qui suit la chronologie des colonnes existantes, donc si on a décembre, on insert janvier, et on ré écrit les formules pour s'adapter à ce nouveau mois/année.) ?
Si oui, je ne vois pas du tout comment faire étant donné que je ne connais pas bien VBA,
est-ce une code long/dur ? Avez-vous la possibilité de m'aider ou de m'indiquer la direction que je dois prendre ou autre ? Je vous en serais très reconnaissant.

Bonne journée!

[Tête de chat]

Bonjour,

ça pourrait ressembler à ça :

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
With ActiveSheet
    ' insertion de la colonne
    .Range("L:L").Insert
    ' copie de la précédente (donc de la suivante)
    .Range("M:M").Copy
    .Range("L:L").Paste xlPasteAll
    ' calcul du nouveau mois
    .Range("L3").Value = DateSerial(Year(.Range("M3").Value), Month(.Range("M3").Value) + 1, 1)
    ' calcul de la première formule
    .Range("L4").Formula = Replace(Replace(Replace(.Range("M4").Formula _
                                   , Format(Range("M3").Value, "MM_AAAA"), Format(Range("L3").Value, "MM_AAAA")) _
                                   , Format(Range("M3").Value, "AAAA"), Format(Range("L3").Value, "AAAA")) _
                                   , Format(Range("M3").Value, "MMMM"), Format(Range("L3").Value, "MMMM"))
    'copie dans toute la colonne
    .Range("L4").Copy
    .Range("L5:L600").PasteSpecial xlPasteFormulas
End With

avec quelques bémols :
1. dans cet exemple, tout est câblé en dur. Il vaudrait mieux par exemple utiliser des zones nommées, voire remplacer ton tableau par un tableau structuré. Bref préparer l'avenir en rendant tout ça plus maintenable et robuste.
2. idem le 600 dans m5:M600 est au pif ; soit c'est raisonnablement fixe et idem ci dessus, soit implémenter une recherche de la dernière ligne...
3. les replace dans la formule sont un peu sauvages ; déjà il est important de faire le MM_AAAA avant le AAAA, ça marchera mieux, et ensuite il y a toujours un risque si par exemple un truc qui ressemble à une année se retrouve ailleurs dans la formule.

[Pierre Fauconnier]

Franchement, vouloir faire cela quand on est à ce point débutant qu'on ne met pas "Sub..." et "End Sub", en plus avec une macro codée en dur qui est illisible et non maintenable.

Pourquoi n'expliquez-vous pas ce que vous voulez réaliser, SANS VBA!!, juste avec vos mots et des copies d'écran?

Vous allez droit au casse-pipe, là.

[Hasqueilz]

Franchement, vouloir faire cela quand on est à ce point débutant qu'on ne met pas "Sub..." et "End Sub", en plus avec une macro codée en dur qui est illisible et non maintenable.

Pourquoi n'expliquez-vous pas ce que vous voulez réaliser, SANS VBA!!, juste avec vos mots et des copies d'écran?

Vous allez droit au casse-pipe, là.

Je pense avoir assez bien expliqué ce que je veux réaliser, étant donné que Tête de chat a bien compris ma requête.

Bien à vous

[Pierre Fauconnier]

Ben, continuez à vous casser la tête avec le VBA mal torché que l'on vous propose et que vous ne maîtrisez pas, alors.

[Hasqueilz]

Ben, continuez à vous casser la tête avec le VBA mal torché que l'on vous propose et que vous ne maîtrisez pas, alors.

Je ne vois pas pourquoi vous vous emportez, vous me demandez d'expliquer ma requête : je l'ai expliqué un peu plus haut dans la conversation et je vous le souligne, puis vous me répondez "continuez à vous casser la tête avec le VBA [...]", que voulez vous que je vous réponde concrètement ? J'ai fais ce que vous m'avez dis de faire, et vous vous énervez, cela n'a pas de sens.

[Tête de chat]

Bonjour Pierre

avec le VBA mal torché que l'on vous propose

je ne sais pas ce que j'ai fait pour mériter ça ? Je suis le premier a voir écrit en dessous du code justement qu'il était très perfectible...

Quand on a 6 étoiles rouges ça autorise à être malpoli ou bien ?
Quand je sera grand, je veux 6 étoiles rouges

[Pierre Fauconnier]

Bonjour Pierre


je ne sais pas ce que j'ai fait pour mériter ça ? Je suis le premier a voir écrit en dessous du code justement qu'il était très perfectible...

Quand on a 6 étoiles rouges ça autorise à être malpoli ou bien ?
Quand je sera grand, je veux 6 étoiles rouges

Le code est mal torché, et les étoiles n'ont rien à voir avec cela. Désolé, j'ai mon franc-parler, parce que le but de la démarche, c'est d'aider, et que le code proposé n'aide pas du tout. C'est gentil d'aider, mais lorsque c'est mal torché, c'est mal torché. Des Replace(...) en cascade, c'est juste non maintenable, surtout dans une formule qui doit passer de VBA à Excel avec des changements qui se font au coup par coup. Vous partez sur M alors que l'insertion devrait se faire sur K. vous recopiez sur 600 lignes, vous recherchez le mois sur M3 alors que la ligne d'entête est en ligne 6, ...

Désolé pour ma franchise, mais tant qu'à aider, autant réfléchir à la problématique dans son ensemble, surtout avec quelqu'un qui ne maîtrise pas du tout VBA.

On est à 22 messages dans la discussion, là. Et ce n'est pas prêt de s'arrêter, visiblement.

[Pierre Fauconnier]

Je ne m'emporte pas. Je veux juste que vous compreniez que faire cela par VBA avec la macro qui vous est proposée, c'est aller au casse-pipe.

Déjà, on n'utilise pas RECHERCHEV en intreclasseur. C'est un "péché capital d'Excel" et rien que cela, avec ou sans VBA, c'est la cata assurée. Il ne faut JAMAIS utiliser de formules interclasseur. Il faut importer les données avec Power Query et travailler en local.

Dans votre copie d'écran, il y a des déc 2023 puis des août... du coup, on ne sait pas si ce sont des dates ou du texte. Comment deviner "quel mois" on doit insérer en colonne L?

Vous dites que vous avez une ligne jaune sur Sub Test, mais je n'ai pas vu de ligne Sub Test. On n'a pas le code de la macro que VOUS utilisez.


Comment voulez-vous que l'on vous aide dans ces conditions?

Perso, plutôt que de repartir d'une formule présente dans la feuille et que l'on essaie de modifier à coup de remplacements hasardeux, je travaillerais avec une formule générique dans laquelle je remplace le texte (toujours le même) par ce que je dois modifier.

Mais comme vous êtes vraiment très débutant en VBA (ce n'est pas une tare, ni une injure de ma part, c'est juste un constat), ça revient à vous pondre tout le code que vous allez copier-coller sans rien y comprendre... Comment voulez-vous que cela fonctionne?

Je vais vous faire un pas à pas de ce qu'il faut faire. Mais pas sûr que cela vous aidera beaucoup.

[Pierre Fauconnier]

Dans la formule

Code excel :

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

=RECHERCHEV(A7;'M:\____DRIQ\DRI\ALTER\BV\2023\Decembre\[grid_details (12_2023).xlsx]PTF (pilotage EF360)'!$D$2:$I$600;6;FAUX)

, Décembre est sans accent aigu. C'est voulu? Déjà là, c'est mal barré! Février est aussi sans accent? Comment sont écrits les mois dans l'explorateur?

Le RECHERCHEV va de D2 à I600 => pourquoi I600? On ne dépassera jamais les 600 lignes?


Voici un code qui pourrait fonctionner, à mettre dans un module standard, et en considérant qu'il est lancé à partir de la bonne feuille. Il faut l'adapter à l'orthographe utilisée pour les noms des mois dans l'arborescence des fichiers. Je n'ai pas pu tester, n'ayant évidemment pas reproduit l'arborescence. Tel qu'écrit, il se passe normalement de commentaires. Pour ce code, je suis parti de l'idée qu'en ligne 6, à partir de L, on a bien des dates (ce qui n'est pas le cas dans le fichier qui a été transmis => un peu de rigueur dans le fichier, ça ne fait pas de mal). Je considère aussi qu'en colonne A, on n'a pas de rupture dans les codes (pas de lignes vides) et que l'on n'a pas de ligne de total ou autre.

Code vba :

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
 
Sub InsererMois()
  Dim DerniereDate As Date
  Dim NouvelleDate As Date
  Dim Formula As String
  Dim ListeMois
  Dim Annee As String, Mois As String, Periode As String
  Dim DerniereLigne As Long
 
 
  ListeMois = Array("Janvier", "Fevrier", "Mars", "Avril", "Mai", "Juin", "Juillet", "Aout", "Septembre", "Octobre", "Novembre", "Decembre")
 
  Formula = "=VLOOKUP(A7,'M:\____DRIQ\DRI\ALTER\BV\{annee}\{mois}\[grid_details ({periode}).xlsx]PTF (pilotage EF360)'!$D$2:$I$600,6,FALSE)"
  DerniereDate = Range("k6").Value
  NouvelleDate = Application.EDate(DerniereDate, 1)
  Annee = Format(NouvelleDate, "yyyy")
  Mois = ListeMois(Month(NouvelleDate) - 1)
  Periode = Format(NouvelleDate, "mm_yyyy")
  DerniereLigne = Range("a1048576").End(xlUp).Row
  RemplacerTexte Formula, Array("{annee}", Annee, "{mois}", Mois, "{periode}", Periode)
  Range("k:k").Insert xlToRight
  Range("k7:k" & DerniereLigne).Formula = Formula
  Range("K6").Value = NouvelleDate
  Range("k6").NumberFormat = "mmm-yy"
End Sub
 
Function RemplacerTexte(Texte As String, Remplacements) As String
  Dim i As Long
 
  For i = LBound(Remplacements) To UBound(Remplacements) Step 2
    Texte = Replace(Texte, Remplacements(i), Remplacements(i + 1), , , vbTextCompare)
  Next i
End Function

Je persiste cependant à dire que ce n'est pas une bonne façon de faire. Power Query est normalement capable de réaliser sans code et sans formules inter-fichiers.

[Pierre Fauconnier]

Cela dit...

Je suppose que vous n'allez pas ajouter chaque mois une nouvelle colonne, car ça deviendra vite ingérable et illisible.

Souvent, dans des tableaux de ce genre, on garde x mois (ici, 12 apparemment). Du coup, on n'a pas besoin de VBA pour ajouter les colonnes et recréer la formule. On importe les données du mois courant avec Power Query, on a trois lignes de VBA pour ajouter le résultat de la requête à la table de données dans le classeur, et on analyse les données dans un tableau statique ou un TCD. Dans le cas du tableau statique, il pourrait être restitué par une autre requête Power Query, en se contentant de 2 lignes de VBA pour actualiser les requêtes dans le bon ordre.