Discussion :

[fabien32200]

Je souhaite automatiser une formule dans une colonne.

Voici comment se présente mes données : (les données vont de la colonne A à I, et j'ai à peu près 1000 lignes ensuite)


NumCptClient ; LibelleClient ; ValoPtf ; LibObjectif ; LibBanquier ; Performance ; Benchmark ; Perf/Benchmark ; LibGerant

1
..
[..]
..
1000




Dans la colonne "G" (Benchmark) et dans les 1000 lignes de cette colonne je souhaite intégrer une formule qui est :

"SI(D2="Perso Mixte Modérée Actions";$L$2;SI(D2="Perso Mixte Equilibre";$L$3;SI(D2="Perso Mixte Patrimoniale";$L$4;SI(D2="Perso Mixte Liberté";$L$5;SI(D2="Perso Opc Liberté";$L$5;SI(D2="Perso Mixte Vitalité";$L$6;SI(D2="Perso Mixte Dyn 60-100%";$L$6;SI(D2="Perso Mixte Audace";$L$7;SI(D2="Perso Mixte Pea Défensif";$L$8;SI(D2="Perso Mixte PEA";$L$9;SI(D2="Perso OPC Prudente Taux";$L$10;SI(D2="Perso Mixte Modérée Taux";$L$10;SI(D2="Perso Mixte Prudent Taux";$L$10;SI(D2="Perso Opc Prudent Taux";$L$10;SI(D2="Perso Opc Perf Abs";$L$11;SI(D2="Perso OPC Modérée Actions";$L$12;SI(D2="Perso OPC Equilibre";$L$13;SI(D2="Perso OPC Patrimoniale";$L$14;SI(D2="Perso OPC Vitalité";$L$15;SI(D2="Perso Opc Dyn Ethique";$L$15;SI(D2="Perso OPC Audace";$L$16;SI(D2="Perso Opc Dyn 60-100%";$L$16;SI(D2="Perso OPC PEA Défensif";$L$17;SI(D2="Perso OPC PEA";$L$18;SI(D2="Perso OPC PEA PME";$L$18))))))))))))))))))))))))"

(Désolé si je n'ai pas inséré de "balise" car cette formule n'aurait pas été lisible)

Je souhaiterai que cette formule soit intégrée automatiquement lorsque j'importe mes données de mon logiciel vers excel. Ça m'éviterait de faire C/C et de tirer ensuite cette formule jusqu’à la fin des 1000 lignes.

Il y aurait un astuce via VBA pour automatiser cela ?

[BENNASR]

Bonjour
je te propose :
1- mettre le curseur dans O2 ou se trouve ta formule insérer manuellement qui donne déjà le résultat souhaité
2- ongler developpeur : enregistrer un macro
3- faire entrer dans O2 de l'onglet Benchmark pour l'enregistreur auto enregistre votre formule
4 insérer votre nv code :

Code :

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

1
2
3
4
5
6
7
8
Sub CalculerA()
i = Sheets("Benchmark").Range("A" & Rows.Count).End(xlUp).Row
With Sheets("Benchmark")
.Range("O2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC6,'Grille 2012'!R6C4:R26C44,MATCH(RC7,'Grille 2012'!R5C5:R5C44,0)+1,FALSE)" ' à remplacer par ta formule obtenue via enregistreur automatqiue
        Selection.AutoFill Destination:=Range("O2:O" & i), Type:=xlFillDefault
End With

[joe.levrai]

Bonjour,

grosse formule qu'on peut simplifier !

1) Créer une plage nommée dans une feuille cachée (ou non, tu choisis) qui contient sur une colonne tes 17 "Perso...."
2) Utiliser la formule EQUIV pour retrouver le numéro de ligne, dans une formule INDIRECTE pour renvoyer la position Lx voulue


Exemple :

1) dans une feuille cachée, j'ai crée de A1 à A17 la liste alphabétique allant de A à Q
2) j'ai nommé cette plage "MATRICE"
3) en E2 de la feuille où sont situées les données et formules, j'ai inséré la formule

=INDIRECT("$L$" & EQUIV(D2;MATRICE;0)+1)

Résultats :

- si je saisie "A" en D2, alors j'obtiens la valeur de la cellule L2
- si je saisie "B" en D2, alors j'obtiens la valeur de la cellule L3
- etc....



Voici pour simplifier ta formule.

Ensuite, pour la réutiliser automatiquement, tu as plusieurs méthodes, mais comme je ne sais pas comment tu "importes les données" (par VBA ou non), je te conseille de convertir ta base de donnée en tableau structuré Excel

Dans un tableau structuré, si une colonne comporte une formule sur ses cellules, alors toute insertion d'une nouvelle ligne reprendra la même formule dans la colonne

voir exemples par ici : http://silkyroad.developpez.com/excel/tableau/

[joe.levrai]

BENNASR,

il ne pourra pas manipuler cette formule en VBA ... elle est trop longue !
On est limité en nombre de caractères, je crois que c'est 255

[joe.levrai]

BENNASR,

il ne pourra pas manipuler cette formule en VBA ... elle est trop longue !
On est limité en nombre de caractères, je crois que c'est 255

[fabien32200]

On atteint les limites de VBA...
Merci de vos réponses, je vais travailler sur tes recommandations joe.levrai.

[Igloobel]

Bonjour,
tu n'es pas obligé de faire du VBA pour si peu

si tu transforme tes données en tableau (ruban Insertion - Tableaux ) tout ce que tu ajoutes en fin de tableau incrémente le tableau et par la même occasion la formule aussi (rien à faire).

le petit bémol c'est comment tu ajoutes tes données il y a peut-être des choses à faire à ce sujet.

De plus,
Apparemment tu connais pas l'astuce du "double clic sur la poignée" cette astuce permet de recopier en un instant ( de l'ordre de la seconde ) la recopie de la cellule vers le bas jusqu'à la première ligne vide

A+

[fabien32200]

Merci de vos réponses !!

J'ai essayé avec ce code :

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
Sub Macro2()
'
' Macro2 Macro
'
 
'
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-3]=""Perso Mixte Modérée Actions"",R2C12,IF(RC[-3]=""Perso Mixte Equilibre"",R3C12,IF(RC[-3]=""Perso Mixte Patrimoniale"",R4C12,IF(RC[-3]=""Perso Mixte Liberté"",R5C12,IF(RC[-3]=""Perso Opc Liberté"",R5C12,IF(RC[-3]=""Perso Mixte Vitalité"",R6C12,IF(RC[-3]=""Perso Mixte Dyn 60-100%"",R6C12,IF(RC[-3]=""Perso Mixte Audace"",R7C12,IF(RC[-3]=""Perso Mixte P" & _
        "nsif"",R8C12,IF(RC[-3]=""Perso Mixte PEA"",R9C12,IF(RC[-3]=""Perso OPC Prudente Taux"",R10C12,IF(RC[-3]=""Perso Mixte Modérée Taux"",R10C12,IF(RC[-3]=""Perso Mixte Prudent Taux"",R10C12,IF(RC[-3]=""Perso Opc Prudent Taux"",R10C12,IF(RC[-3]=""Perso Opc Perf Abs"",R11C12,IF(RC[-3]=""Perso OPC Modérée Actions"",R12C12,IF(RC[-3]=""Perso OPC Equilibre"",R13C12,IF(RC[" & _
        "so OPC Patrimoniale"",R14C12,IF(RC[-3]=""Perso OPC Vitalité"",R15C12,IF(RC[-3]=""Perso Opc  Dyn Ethique"",R15C12,IF(RC[-3]=""Perso OPC Audace"",R16C12,IF(RC[-3]=""Perso Opc Dyn 60-100%"",R16C12,IF(RC[-3]=""Perso OPC PEA Défensif"",R17C12,IF(RC[-3]=""Perso OPC PEA"",R18C12,IF(RC[-3]=""Perso OPC PEA PME"",R18C12,)))))))))))))))))))))))))"
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G520"), Type:=xlFillDefault
    Range("G2:G520").Select
    Range("E4").Select
End Sub

Mais malheureusement ça ne marche pas, je ne comprends pas !