XL 2013 Valeur relative dans formule VBA avec variable

laopein · 16 Février 2016

Bonjour,

J'ai une macro qui écrit la formule suivante dans une cellule (variable) :

Range(POSITION2).Formula = "=SUMPRODUCT((" & POSITION1 & ":" & POSITION3 & "=""X"")*(" & POSITIONA & ":" & POSITIONB & "))"

Ce qui me donne une formule de ce type (dans la cellule H20 par exemple) :
=SOMMEPROD(($F$22:$F$917="X")*($H$22:$H$917))

Mais je souhaiterais pouvoir copier ensuite cette formule en I20, J20, K20, etc. et que H22:H917 devienne I22:I917, J22:J917, etc.

Or les valeurs sont absolues.

Comment faire pour avoir une formule de ce type :
=SOMMEPROD(($F$22:$F$917="X")*(H$22:H$917))

J'aimerai en effet que la colonne "H" dans cette formule soit une valeur relative, mais que les autres valeurs restent absolues. Est-ce possible ?

Merci par avance pour votre aide.

herve62 · 16 Février 2016

Re : Valeur relative dans formule VBA avec variable

bonsoir
par moment vous cherchez "compliqué" !! en écrivant des macros barbares !
or dans VBA il y a BASIC ( ou basique) , certes il faudra détailler un peu plus , mais en passant par des plages indexées ou des .cells(x,y) il y aura une solution avec un code facile
En plus là on a même pas une idée ( ton fichier joint) pour y réfléchir
juste un exemple BASIQUE entre Excel et VBA , cela m'a pris 3 min !

mapomme · 16 Février 2016

Re : Valeur relative dans formule VBA avec variable

bonsoir laopein, herve62,

Avec cette écriture ?:

Code:

Range("h20").Formula = "=SUMPRODUCT((" & Range("f22").Address & ":" & _
  Range("f917").Address & "=""X"")*(" & _
  Range("h22").Address(, False) & ":" & Range("h917").Address(, False) & "))"

herve62 · 16 Février 2016

Re : Valeur relative dans formule VBA avec variable

Re
Luc : tu t'y mets aussi à écrire des grossièretés ?

Peut être un jour sous la torture j'y arriverai aussi ?
Bonne soirée

laopein · 17 Février 2016

Re : Valeur relative dans formule VBA avec variable

Bonjour,

Merci pour vos réponses. La solution de Mapomme fonctionne très bien sur une cellule en particulier, mais pas sur une cellule variable.
Herve62 oui ton code est beaucoup plus simple, c'est sûr.
Je joins le fichier pour que cela soit plus compréhensible.

Mon but est que quand je clique sur le bouton "ajouter conduc."
Cela ajoute une ligne en-dessous de la n°19 (en-dessous du conduc. précédent) et une colonne à droite de la E (à droite du conduc. précédent).
Ensuite la formule indiquée plus haut vient s'insérer en H20 (variable) puis devra être copiée jusqu'à la cellule CR20 (variable). Or ma formule est absolue, je ne peux donc pas copier la formule en H20 jusqu'en CR20.

Voilà pourquoi j'ai une formule avec toutes ces variables... En fait je recherche les cellules vides adjacentes au conduc. précédent pour la formule SUMPRODUCT.

Vous connaissez probablement une manière plus simple d'opérer.

herve62 · 17 Février 2016

Re : Valeur relative dans formule VBA avec variable

Bonjour
pourrais tu écrire un petit exemple concret dans la partie que je met en capture car
ligne 19 puis droite E ?????? je ne vois pas le rapport , ce sont des cases noires ??
Mieux vaut mettre à la paluche ( en rouge par exemple) le résultat attendu avec un peu d'explications
ensuite c'est à nous de voir comment on peut faire

PS : certains Barbatruc ici, ont comme adage de signature " Un Pb bien posé donne pratiquement une solution" ( ou du même style, j'ai plus en tête)

laopein · 17 Février 2016

Re : Valeur relative dans formule VBA avec variable

Oui ce n'est pas facile à expliquer, je mets en pièce jointe un tableau avec des explications.

En jaune (cellules) et en rouge (texte) sont les résultats que j'essaie d'atteindre.

Merci beaucoup pour ton aide

herve62 · 17 Février 2016

Re : Valeur relative dans formule VBA avec variable

Oui , là c'est nettement plus clair et compréhensible
MAIS ..... cela n'est pas évident ( vu les insertions de colonnes & lignes) mélange d'absolu et variable !
Je vais essayer de te donner le squelette de base en VBA mais pas avant fin de semaine ( il y a du boulot !) rien que pour l'étape 1
La 2 : il faudra certainement créer ( en gros) une fonction avec paramètres car moi et les formules EXCEL transcrites VBA .. non Merci , chacun son truc !!!
A+

mapomme · 18 Février 2016

Re : Valeur relative dans formule VBA avec variable

Bonsoir à laopein, herve62,

Un essai dans le fichier joint. Je n'ai traité que la partie rouge -> introduction des formules SOMMEPROD dans la ligne du dernier conducteur inséré.

La macro est basée sur le fait qu'une des cellules du tableau a été nommée avec le nom "maBASE". Cette cellule a été colorée en orange pour la repérer. C'est en fait la première cellule de la colonne en noire. Au gré de l'insertion de conducteur, cette cellule est déplacée tout en gardant son nom. C'est sur l'existence de cette cellule nommée que sont définies tous les autres cellules ou plages qui nous intéressent.
En utilisant la cellule nommée maBASE, le code d'insertion d'un nouveau conducteur doit pouvoir se simplifier grandement.

Pour le test, il faut d'abord ajouter un conducteur.

Une petite chose me chiffonne. En fonction des ajouts d'opérations, en fonction d'un éventuel rajout de colonnes à droite du tableau, les formules SOMMEPROD déjà existantes ne sont pas modifiées. Ce qui signifie qu'au fur et à mesure des ajouts d'opérations, le bloc des formules SOMMEPROD n'est pas homogène. Mais la valeur 895 utilisée pour le nombre de lignes des formules SOMMEPROD suffit peut-être à englober la réalité de vos données ?

Edit: version 002 -> avec modif du code d’insertion de conducteur.

Code du bouton CommandButton3 :

VB:

Private Sub CommandButton3_Click() 'insérer les formules "SOMMEPROD"
' Basée sur la cellule nommée "maBASE"
Dim rgPremCellFormule As Range, rgPremcellCol As Range, rgDerCellcol As Range
Dim rgValeursX As Range, rgValeursColonnes As Range

  Set rgPremCellFormule = Range("mabase").Offset(-2, 1)
  Set rgPremcellCol = Range("mabase").Offset(, 1)
  Set rgDerCellcol = Range("mabase").End(xlToRight).End(xlToRight)
  Set rgValeursX = Range("mabase").Offset(1, -1).Resize(895)
  Set rgValeursColonnes = Range("mabase").Offset(1, 1).Resize(895)
  rgPremCellFormule.Formula = "=SUMPRODUCT((" & _
      rgValeursX.Address & "=""X"")*(" & rgValeursColonnes.Address(, False) & "))"
  rgPremCellFormule.Resize(, rgDerCellcol.Column - rgPremcellCol.Column + 1).FillRight
  Application.Goto Range("A1"), True
End Sub

laopein · 18 Février 2016

Re : Valeur relative dans formule VBA avec variable (Résolu)

Bonjour Mapomme et Hervé62,

Excellent idée Mapomme de renommer directement la cellule maBASE sur le tableau !

Normalement il n'y aura pas de valeurs au-delà de la ligne 895 puisque je supprimerai au fur et à mesure les colonnes de dates dépassées et les opérations clôturées. Normalement je suis bon jusqu'en 2023 !

Du coup j'ai bien tout testé et ça fonctionne très bien, un grand merci pour votre aide ! Je n'aurais jamais trouvé cette solution tout seul...

Merci, Merci

XL 2013 Valeur relative dans formule VBA avec variable

laopein

XLDnaute Nouveau

herve62

XLDnaute Barbatruc

Pièces jointes

mapomme

XLDnaute Barbatruc

herve62

XLDnaute Barbatruc

laopein

XLDnaute Nouveau

Pièces jointes

herve62

XLDnaute Barbatruc

Pièces jointes

laopein

XLDnaute Nouveau

Pièces jointes

herve62

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

Pièces jointes

laopein

XLDnaute Nouveau

Discussions similaires