Formula - passer de référence absolue à relative

Version imprimable

01/04/2020, 20h54
Olivier8510

Formula - passer de référence absolue à relative
Bonjour,

Dans la formule suivante, je cherche à rendre la colonne B relative, de façon à ce que cette formule puisse être copiée dans les colonnes suivantes :

Code:

Range("B2").Formula = "=(" & Range("A:A").Find("Ventes").Offset(0, 1).Address & "-" & Range("A:A").Find("CMV").Offset(0, 1).Address & ")"

La formule dans la colonne B2 est maintenant :

Code:

=($B$5-$B$27)

Maintenant il ne reste qu'à déverrouiller la colonne afin de pouvoir copier la formule dans les autres colonnes et qu'elle s'adapte :
Code:

1 2 =(B$5-B$27) =(C$5-C$27)
Merci en avance pour votre aide.

Olivier
01/04/2020, 23h44
Philippe Tulliez

1 pièce(s) jointe(s)
Bonjour,
Si tu écris une formule dans une plage de cellules en une seule instruction VBA et que tu références les cellules en relatif, absolue ou mixte, c'est exactement comme on le fait manuellement en trois étapes
- Sélection de la plage (pour l'exemple C4:K12)
- Insertion de la formule dans la cellule active =$B4 * C$3
- et raccourci clavier Ctrl + Enter
Exemple de la table de multiplication que je montre lors de mes formations dans la partie "Trucs et astuces"

Pièce jointe 551988

En VBA, une ligne de code

Code:

Range("C4:K12").Formula = "=$B4*C$3"

La base d'excel est la maîtrise du référencement des cellules

A lire éventuellement Excel: Les références absolues et relatives dans vos classeurs Excel
02/04/2020, 00h59
Olivier8510

Bonjour,

Directement dans Excel, c'est quelque chose d'assez simple je comprend.

Mais si tu regardes ma formule, les 2 lignes qui sont utilisées ne sont pas fixes et c'est ce qui me bug. Avec la formule que j'utilise avec le find et le offset, le retour me donne une référence absolue et non une référence relative...
02/04/2020, 01h15
Philippe Tulliez
Bonjour,
Ce que tu affiches ressemble à une formule matricielle mais ce n'est pas ta question

Je t'ai montré comment écrire une formule simple dans Excel avec du VBA

Je te conseille la lecture de ces billets pour comprendre comment traduire facilement en VBA la syntaxe d'une formule
- Ecrire une formule dans Excel à l'aide d'une procédure VBA
- Comment écrire une formule plus complexe dans une cellule ?
Pour obtenir une adresse relative, avec la propriété Address, il faut modifier la valeur des arguments RowAbsolute et ColumnAbsolute qui sont pas défaut à True

Exemple pour obtenir $A2

Code:

MsgBox Range("A2").Address(RowAbsolute:=False)

et pour obtenir en plus la parentalité complète de la cellule

Code:

MsgBox Range("A2").Address(RowAbsolute:=False, External:=True)
02/04/2020, 07h57
Menhir

Citation:

Envoyé par Olivier8510

Dans la formule suivante, je cherche à rendre la colonne B relative, de façon à ce que cette formule puisse être copiée dans les colonnes suivantes :

Code:

Range("B2").Formula = "=(" & Range("A:A").Find("Ventes").Offset(0, 1).Address & "-" & Range("A:A").Find("CMV").Offset(0, 1).Address & ")"

La réponse est dans l'aide VBA de la propriété Address : https://msdn.microsoft.com/fr-fr/lib...5(v=office.15)
02/04/2020, 11h16
Pierre Fauconnier

Salut

Citation:

Envoyé par Olivier8510

[...]

Code:

Range("B2").Formula = "=(" & Range("A:A").Find("Ventes").Offset(0, 1).Address & "-" & Range("A:A").Find("CMV").Offset(0, 1).Address & ")"

[...]

Pourrais-tu nous donner EN EXCEL la formule que tu souhaites utiliser? J'ai l'impression que tu te compliques drôlement la vie avec ta "formule" recomposée par concaténation...

Et tant qu'à faire, si tu pouvais nous expliquer, en français et donc sans jargon Excel ou VBA ce que tu souhaites obtenir, ça nous aiderait à mieux t'aider ;)
02/04/2020, 14h58
Olivier8510

Je viens d'appliquer le row/column absolute de address et cela répond exactement à ce que je m'attendais comme résultat. Merci à Philippe et Manhir pour votre temps!

Pour Pierre, je suis convaincu qu'effectivement la formule pourrait être améliorée. Essntiellement je veux calculer une marge brute sur vente à partir de (1) les ventes - (2) le coût des marchandises vendues. Toutefois, ce qui présente un dédi vient du fait que les lignes des ventes et coût des marchandises vendues ne sont pas fixes dans la feuille. C'est pourquoi j'utilise le find dans la formule (pour le moment). Si vous avez une solution pour simplifier cette formule, je suis à l'écoute :)
02/04/2020, 15h09
Pierre Fauconnier
Sans avoir a minima la structure de tes feuilles en copie d'écran, c'est un peu jouer à Irma la voyante... ;)

J'ai de très gros doutes qu'il faille passer par du VBA et des FIND (Beurk, au passage) pour réaliser ce que tu souhaites obtenir;
- La plupart des questions posées le sont suite à une mauvaise conception de classeur (ce n'est pas une critique mais un simple constat);
- 95% du vba qui est pondu sur ce forum est inutile lorsque l'on maîtrise correctement Excel;
- Le VBA ne devrait être là que pour automatiser ce que l'on réalise à la main;
- il est souvent plus utile de regarder ce qui se cache derrière la question plutôt que de répondre sur un problème de ; ou de $ mal placé.
:?
02/04/2020, 15h25
Philippe Tulliez

Bonjour Pierre,

Citation:

95% du vba qui est pondu sur ce forum est inutile lorsque l'on maîtrise correctement Excel,

:plusser:
C'est un point sur lequel j'insiste lors de mes formations VBA.
Plus on maîtrise Excel, mieux on programme en VBA pour excel et dans un projet, un gros pourcentage peut être construit avec ce qui est nativement présent dans l'application et surtout utiliser les méthodes des fonctionnalités natives d'excel.
02/04/2020, 15h50
Olivier8510

1 pièce(s) jointe(s)

Voici mon fichier Excel en pièce jointe.
02/04/2020, 16h05
Pierre Fauconnier

1 pièce(s) jointe(s)

Voilà TOUT ce qu'il y a dans ton classeur...

Pièce jointe 552316

Oserais-je dire que je suis perplexe? C'est un poisson d'avril à retardement? Où est la caméra cachée?

Sérieusement, que veux-tu que l'on fasse avec ça et comment veux-tu que l'on comprenne ton besoin?
02/04/2020, 16h58
Olivier8510

En fait j'ai simplifié la feuille pour que ce soit plus visuel, car il y a plus de 300 lignes dans l'originale.

Pour résumer, il y a plusieurs feuilles dans le classeur dont une qui se nomme disons "FT Master". Cette feuille contient beaucoup de lignes et constitue la case départ pour d'autres feuilles qui seront éventuellement créées.

Ici, la feuille "FT Master" a été copiée dans une nouvelle feuille qui se nomme "FT État des résultats", dans laquelle on voudra calculer la "marge brute", et ce à partir des lignes "Ventes" et "Coût des marchandises vendues".

"Ventes" et "Coût des marchandises vendues" provenant de la "FT Master" qui a été copiée, elles ne se situent donc pas toujours sur les même lignes de la feuille "FT État des résultats".

Je recherche donc la façon la plus simple d'identifier les lignes "Ventes" et "Coût des marchandises vendues" dans la colonne A de la feuille "FT État des résultats" pour ensuite utiliser les montants de leur colonne B respective et ainsi calculer la "Marge brute".

Voilà. En espérant que cela puisse vous éclairer!
02/04/2020, 17h09
Pierre Fauconnier

Citation:

Envoyé par Olivier8510

En fait j'ai simplifié la feuille pour que ce soit plus visuel, car il y a plus de 300 lignes dans l'originale.[...]

La simplifier en deçà de trois cellules, tu reconnaitrais que c'est difficile. Pour avoir simplifié, tu as simplifié... :ptdr:

Donne-nous des copies d'écran de ce que tu as pour que l'on puisse imaginer comment tu as organisé tes données. Comment veux-tu que l'on puisse s'imaginer la conception de ton classeur sur base de ce que tu dis?

Un classeur de ce type s'articule autour des tableaux structurés (voir mon tuto). C'est comme cela qu'Excel, qui je le rappelle est un outil d'analyse, travaille le mieux.

Sans visuel sur la conception de tes feuilles, on perd juste son temps. Tu risques juste de te retrouver avec autant de propositions qu'il y aura de personnes qui te répondent, chacune ayant compris à sa sauce ce que tu as essayé d'expliquer.
02/04/2020, 17h41
Olivier8510

1 pièce(s) jointe(s)

Je suis nouveau dans cette communauté. Ne connaissant pas encore les faits et aboutissants, je tentais simplement de vous simplifier la vie. Vous trouverez ci-joint le fichier Excel, encore en version simplifié, mais qui je crois vous permettra de comprendre l'idée de la structure. Merci encore une fois pour votre temps et votre patience!
02/04/2020, 18h55
Pierre Fauconnier

1 pièce(s) jointe(s)
Pas de soucis d'être nouveau, juste prendre le temps de te mettre à la place de ceux qui prennent du temps pour t'aider et, avant de poster, de relire ton message pour voir s'il est compréhensible... ;)

Je vais te donner deux réponses:

1. Réponse technique au problème posé. En colonne D, les formules utilisées en B

Pièce jointe 552436

2. Evidemment, cette réponse ne me convient pas, car c'est du truc de bourrin et ça ne tient pas la route.
1. Questions:
2. Pourquoi les données sont-elles organisées ainsi en FT Master? C'est une façon d'organiser les données qui permet très peu, voire pas du tout, d'utiliser les outils d'Excel
3. Comme je suppose que ce n'est pas sur une seule feuille que tu vas travailler... Comment les infos arrivent-elles dans la feuille FT Master?
4. Devras-tu compiler les infos reçues sur chaque FT Master dans une seule et même feuille?
J'en ai probablement d'autres, mais pour l'instant, vois déjà tes données et donne-nous les réponses à mes questions. En français et sans jargon Excel et encore moins VBA, explique-nous ta démarche, la source de tes données, ton besoin, un peu comme si tu remplissais un cahier des charges pour un programmeur qui devrait te développer le truc.