Etirer une formule jusqu'en bas du tableau avec colonne variable

Bonjour La communauté.

Je cherche une solution pour mon tableau. En effet je chercher à étirer une formule vers le bas. Jusque là je sais faire sauf que 'aimerais déjà qu'elle ne descende que jusqu'en bas. Bon mais à la rigueur ce n'est pas mon problème principale. En effet mon gros soucis c'est surtout que la colonne se décale tous les mois.

Dans l'exemple en pj c'est le colonne BQ. Le mois d'aout. Mais le mois prochain cela sera la colonne BR (septembre) et ainsi de suite. Donc ma formule pour étendre sur BQ ne sera pas bonne le mois prochain puisqu'il faudra remplacer dans la formule BQ par BR.

J'imagine bien qu'il faut mettre une variable mais alors laquelle et comment là c'est vraiment trop dur pour moi. Si une personne peut m'aider, cela serait chouette.

Merci

Bonne journée

Bonjour,

Votre demande fait immédiatement penser aux tables structurées, qui permettent justement d'adapter automatiquement les plages.

Présentation des tableaux Excel - Support Microsoft

Oui. Merci .En effet d'ailleurs j'utilise beaucoup la formule FormulaR1C1. Cela fonctionne bien pour la première case de la colonne qui se décale.

Cependant mon problème est pour étirer ensuite vers le bas car il reprend la formulation classique et du coup on perd le bénéfice des tableaux structurés

en fait il faudrait la fonction autofill mais en formulaR1C1 je ne sais pas si elle existe

j'ai cherché et je ne trouve pas donc à votre bon coeur....

si j'ai trouvé cela

Selection.AutoFill Destination:=Range("RC[0]:RC[-10]")

mais cela ne fonctionne pas

Bonjour LouiDF, saboh12617

La propriété resize de l'objet Range sert à redimensionner une plage, en l'occurrence vers le bas dans ton cas, autant t'en servir 😌

Sinon, si tu as un nombre de colonnes défini pour y introduire une formule, tu n'as qu'à boucler dessus.

Envoie nous un exemple succinct de ce que tu souhaites faire, épure ton fichier, tu l'adapteras ensuite à ton fichier original.

klin89

Effectivement, la méthode autofill est mal utilisée, mais au vu du code proposé je pense comme Klin qu'il serait plus simple que vous joignez un fichier.

En attendant, la syntaxe correcte de la méthode autofill est :

sourceRange.AutoFill Destination:=fillRange

cf. Méthode Range.AutoFill (Excel) | Microsoft Learn

bonjour LouisDF, salut saboh12617,

si c'est sans tableau structuré, on peut utiliser ceci (le msgbox sert à rien, on peut le supprimer et les fomules ne sont que des exemples)

Sub suivi_conso()
     Dim N

     With Sheets("base")                     'votre feuille
          N = .Range("A1").CurrentRegion.Rows.Count - 1     'comme ça on connait le nombre de lignes pour la formule
          MsgBox "Currentregion est " & .Range("A1").CurrentRegion.Address(0, 0) & vbLf & "nombre de lignes pour les formules : " & N
          With .Range("BQ2").Resize(N)       '2eme cellule + autant de cellules dans la colonne voulue (BQ)
               .FormulaR1C1 = "=RC[-1]+31"   'formule pour cette colonne
               .Offset(, 1).FormulaR1C1 = "=AVERAGE(RC[-12]:RC[-1])"     'moyenne année dans la colonne suivante
               .Offset(, 2).FormulaR1C1 = "=AVERAGE(RC[-3]:RC[-1])"     'moyenne 3 mois dans la colonne suivante
          End With
     End With

End Sub

Bonjour à tous,

avec un tableau structuré dont le nom est "Tableau4" dont l'entête de la colonne est "b" et la première cellule de cette colonne est B2, alors on pourrait avoir pour une formule de type =(A2+3)*10, où le nom de l'entête en colonne A est "a" :

Sub TabStructuré()
    Range("B2").FormulaR1C1 = "=([@a]+3)*10"
    Range("B2").AutoFill Destination:=Range("Tableau4[b]")
End Sub

plus besoin de connaitre le nombre de ligne Excel s'en charge et cela "efface" les données inscrites "en dur" qui bloquent "l'autofill" des ce type de tableau.

@ bientôt

LouReeD

Bonjour et merci saboh12617.

ci joint un tableau ultra simplifié avec une formule très simple. Dans le cas en pj il s'agit de dupliquer la formule en G2 sur la plage G2 à G17.

Je sais le faire avec un autofill mais le mois prochain nous serons sur la case d'octobre et donc la formule d'autofill ne sera plus la bonne car elle sera sur la colonne G et non H (la formule se décale). et ainsi de suite tous les mois. D'où mon problème pour adapter cette formule.

Je me disais aussi que passer par le numéro des colonnes peut-être

@louReed @bsalv en effet cependant même problème il y a une référence à une case donc le mois prochain je suis bloqué

merci

Bonjour,

un essai par rapport au fichier fourni :

Sub MiseAJourMois()
    Dim DerLig As Long, DerCol As Long
    DerCol = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
    If DerCol + 2 >= Month(Date) Then Exit Sub
    DerLig = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    Cells(2, DerCol + 1).FormulaR1C1 = "=RC[-2]+RC[-1]"
    Cells(2, DerCol + 1).AutoFill Destination:=Range(Cells(2, DerCol + 1), Cells(DerLig, DerCol + 1))
End Sub

Une petite vérification afin de ne pas pouvoir lancer le code si déjà en place sur le mois en cours. Si vous avez deux mois de retard alors il faudra le lancer deux fois ou bien mettre une boucle automatique...

@ bientôt

LouReeD

Version avec la dite boucle toujours par rapport à votre fichier :

Sub MiseAJourMois()
    Dim DerLig As Long, DerCol As Long, I As Integer
    DerCol = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
    If DerCol + 2 >= Month(Date) Then Exit Sub
    DerLig = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    For I = 1 To ((Month(Date) - 2) - DerCol)
        Cells(2, DerCol + I).FormulaR1C1 = "=RC[-2]+RC[-1]"
        Cells(2, DerCol + I).AutoFill Destination:=Range(Cells(2, DerCol + I), Cells(DerLig, DerCol + I))
    Next I
End Sub

Le fichier avec trois mois manquants :

@ bientôt

LouReeD

Merci. La macro ne fonctionne pas. il ne se passe rien. Peut-être est ce possible d'avoir une explication du code. En effet quand je l'applique sur mon vrai fichier rien ne se passe. Idem sur le fichier que j'ai mis en ligne sur le forum. Ou elle fonctionne le premiers 3 mois mais ensuite plus rien.

D'ailleurs merci pour l'anticipation si je loupe un mois. Cependant cela n'arrive pas.

Aussi si possible si je peux avoir une explication un peu détaillé je peux essayer de l'adapter. Dans l'exemple ci dessous c'est mon fichier. Bon la formule est plus longue mais ce n'est pas le point centrale. Car en effet quand je prend votre macro il ne met même pas la simple opération. Donc il doit y a voir un truc.

Merci

et autre remarque j'ai l'impression que si j'ai plusieurs formules à dupliquer la macro ne va pas fonctionner à chaque fois ? car elle compte la dernière colonne active non ?

je suis un peu perdu...

En rapport avec le fichier fourni...

Forcément en voyant votre fichier...

@ bientôt

LouReeD

Si je comprends bien, tous les mois vous insérez une colonne, c'est vraiment du bidouillage ce que vous nous pondez.

Et c'est quoi cette ligne qui apparaît sous la ligne d'en-têtes ? 😵

Bonjour

alors pour vous répondre la ligne en dessous c'est juste le numéro du mois mais je ne suis pas encore sur de m'en servir. et oui tous les mois j'insère une colonne avant moyenne et je met le mois.

Mon problème réside dans le fait de faire dérouler la formule actuellement placé en BU2 mais qui dans la logique - on est bien d'accord - devrait être en BU3 puis déroule en bas. Tous les mois j'insère une colonne avec la moyenne je viens mettre la formule en ligne 3 dans l'exemple en BU3 puis je tire ma formule.

Ensuite mes moyennes elle je vais compléter ma macro avec du RC et cela va le faire tout seul

Donc désolé si je n'es pas été clair depuis le début. Pas toujours facile d'expliquer à l'écrit. Ma recherche est donc de pouvoir étirer la formule mais de manière variable puisque la colonne va augmenter de 1. LA formule reste la même on est bien d'accord mais l'étirement lu avec un autofill va forcément changer. Par exemple sur septembre cela va être BU 3 : BU 200 mais le mois prochain donc octobre cela sera BV3 BV : 200 puisque tout va se décaler d'un cran sur la droite. LA formule reste la même c'est la macro qui doit changer. Or l'idée de la macro c'est qu'elle marche tous les mois pareil.

Ci joint mon code en cours en travail bien sur

Range("A1").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, -10).Select
Selection.EntireColumn.Select
Selection.Insert

ActiveCell.FormulaR1C1 = "=RC[-1]+31"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaLocal = "=SI(D2=""KG"";(SOMME.SI([1.xlsx]A!$A$6:$N$4000;A2;[1.xlsx]A!$N$6:$N$4000)-SOMME.SI([2.xlsx]A!$A$6:$N$4000;A2;[2.xlsx]A!$N$6:$N$4000))+SOMME.SI([receptions.xlsx]A!$G$2:$L$65536;A2;[receptions.xlsx]A!$L$2:$L$500);(SOMME.SI([1.xlsx]A!$A$6:$N$4000;A2;[1.xlsx]A!$L$6:$L$4000)-SOMME.SI([2.xlsx]A!$A$6:$N$4000;A2;[2.xlsx]A!$L$6:$L$4000))+SOMME.SI([receptions.xlsx]A!$G$2:$L$65536;A2;[receptions.xlsx]A!$K$2:$K$500))"
'dérouler la formule ?

ActiveCell.Offset(1, 1).Select
'moyenne annuelle ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-12]:RC[-1])"


'moyenne 3 mois ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-4]:RC[-2])"
'moyenne 3 mois n-1 ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-17]:RC[-15])"

' Range("BY3").Select
' ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(IF(OR(RC[-1]>=50%,RC[-1]<=-50%),RC[-3],(RC[-16]*(1+RC[-1]))),RC[-3]),0.001)"
' Range("BZ3").Select
' ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(IF(OR(RC[-2]>=50%,RC[-2]<=-50%),RC[-4],(RC[-16]*(1+RC[-2]))),RC[-4]),0.001)"
' Range("CA3").Select
' ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(IF(OR(RC[-3]>=50%,RC[-3]<=-50%),RC[-5],(RC[-16]*(1+RC[-3]))),RC[-5]),0.001)"
' Range("CB3").Select
'ActiveCell.FormulaR1C1 = _
"=IFERROR(IFERROR(IF(OR(RC[-4]>=50%,RC[-4]<=-50%),RC[-6],(RC[-16]*(1+RC[-4]))),RC[-6]),0.001)"

Merci @LouReed pour le code. Seulement il fonctionne à mon avis pour la dernière colonne enfin si j'ai bien compris

Si vous avez des idées pour m'aider. Merci et Bonne journée à tous

Re LouisDF,

Pour insérer la nouvelle colonne, utilisez Application.Match sur la ligne d'en-têtes pour déterminer la position de l'élément recherché, ici "montant 1 an", puis insérez la formule souhaitée.

Je suis sur mon téléphone portable, je ne peux pas vous aider.

klin89

oui merci cependant mon sujet n'est pas le problème de l'insertion de la colonne car cela fonctionne. C'est le fait d'étirer ma formule.