Formules crées via VBA suivent une plage de données

[plombiero]

Bonjour à tous,

Je suis confronté à un souci que je n'arrive pas à résoudre seul.
Via VBA, j'ai réalisé les formules suivantes :

    Range("D[color=red][b]65[/b][/color]").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(Conso!C13,""Pas de fiche"")"
.
.'J'ai volontairement tronqué le code pour plus de compréhension
.
    Range("D[color=red][b]71[/b][/color]").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-6]C[1]:R[-2]C[1])"

Les formules fonctionnent et s'intègrent dans un tableau que j'ai réalisé.
Le souci que je rencontre, c'est que si j'insère une nouvelle ligne avant la ligne 65 dans mon exemple, toutes mes formules sont décalées.

J'ai bien pensé à nommer la plage "PLAGELAMBDA" (mon tableau + cases où vont s'insérer les formules), mais je n'arrive pas à insérer les formules dans cette plage.


Je m'en remets donc à vous pour une éventuelle piste afin que mes formules suivent ma plage de données, qu'elles soient décalées en même temps que le tableau si l'on insère/supprime une ou plusieurs ligne(s)/colonne(s).

Merci d'avance pour votre aide,

Damien

ps: je n'ai pas inséré le code dans le fichier joint, juste le tableau de données

 

[pierrejean]

Re : Formules crées via VBA suivent une plage de données

bonjour plombiero

Sans au moins les formules dans les cellules , il est vraiment problèmatique de t'aider !!!!
D'autant que la formule resultant de

Range("D[COLOR=red][B]65[/B][/COLOR]").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(Conso!C13,""Pas de fiche"")"

n'a pas de raison de se modifier par l'insertion de lignes

 

[plombiero]

Re : Formules crées via VBA suivent une plage de données

Merci de t'intéresser à mon souci.

Tout le problème vient de là.

Si j'insère une ligne avant la n°65, ma formule écrasera l'entête de mon tableau quand je la ferai tourner à nouveau. Ce que je ne souhaite pas.
Ce que j'aimerai, c'est que si j'insère une ligne avant la n°65, la prochaine fois que ma macro va tourner, la formule ne s'incrément plus en 65 mais en 66.

Si je nomme une cellule, je sais que ça peut fonctionner. Mais qu'en est-il d'une plage de cellule ? Dois-je nommer toutes les cellules du tableau une à une ?

 

[plombiero]

Re : Formules crées via VBA suivent une plage de données

A toutes fins utiles, mon code se présente comme ceci mais ne supporte pas l'insertion ou la suppression de ligne ou colonnes.

Sub nombre_et_repartition()

    Range("D65").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(Conso!C13,""Pas de fiche"")"
    Range("D66").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(Conso!C13,""500"")"
    Range("D67").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(Conso!C13,""5000"")"
    Range("D68").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(Conso!C13,""10000"")"
    Range("D69").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(Conso!C13,""50000"")"
    
    Range("D70").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)"
    Range("D71").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-6]C[1]:R[-2]C[1])"
    Range("E65").Select
    ActiveCell.FormulaR1C1 = "=ABS(SUMIF(Conso!C[8],""Pas de fiche"",Conso!C[4]))"
    Range("E66").Select
    ActiveCell.FormulaR1C1 = "=ABS(SUMIF(Conso!C[8],""500"",Conso!C[4]))"
    Range("E67").Select
    ActiveCell.FormulaR1C1 = "=ABS(SUMIF(Conso!C[8],""5000"",Conso!C[4]))"
    Range("E68").Select
    ActiveCell.FormulaR1C1 = "=ABS(SUMIF(Conso!C[8],""10000"",Conso!C[4]))"
    Range("E69").Select
    ActiveCell.FormulaR1C1 = _
        "=ABS(SUMIF(Conso!C[8],""50000"",Conso!C[4]))"
    Range("F65").Select
    ActiveCell.FormulaR1C1 = "=(RC[-1]/R71C4)*100"
    Range("F66").Select
    ActiveCell.FormulaR1C1 = "=(RC[-1]/R71C4)*100"
    Range("F67").Select
    ActiveCell.FormulaR1C1 = "=(RC[-1]/R71C4)*100"
    Range("F68").Select
    ActiveCell.FormulaR1C1 = "=(RC[-1]/R71C4)*100"
    Range("F69").Select
    ActiveCell.FormulaR1C1 = "=(RC[-1]/R71C4)*100"
    Range("D71").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-6]C[1]:R[-2]C[1])"

 

[pierrejean]

Re : Formules crées via VBA suivent une plage de données

Re

je n'avais pas compris que tu repetais ta macro

Dans ce cas je prends une cellule fixe du tableau

En debut de macro je rcherche cette cellule (par son contenu) et ensuite les cellules du tableau sont reperables par offset

Vois le test de ce fichier, insere des lignes ,des colonnes et fait tourner
Bien entendu il faut adapter pour ecrire les formules
Si tu n'y parviens pas reviens on t'aidera

 

[plombiero]

Re : Formules crées via VBA suivent une plage de données

Merci Pierrejean,

C'est exactement ce dont j'avais besoin. Le code suivant m'est très utile.

Set c = ActiveSheet.Cells.Find("Distribution des erreurs par ", LookIn:=xlValues, lookat:=xlWhole)

Résolu, merci encore 🙂

Damien