Nom d'une plage défini par VBA intégré dans une formule

[Daniel38]

Bonjour
Voilà je souhaiterai copier avec VBA une formule intégrant une plage nommée variable selon le nombre :
définition des plages
If NBEntreprise = 2 Then Range(Cells(LgRef, 10), Cells(LgRef, 15)).Select
If NBEntreprise = 3 Then Range(Cells(LgRef, 10), Cells(LgRef, 20)).Select
If NBEntreprise = 4 Then Range(Cells(LgRef, 10), Cells(LgRef, 25)).Select
If NBEntreprise = 5 Then Range(Cells(LgRef, 10), Cells(LgRef, 30)).Select

'nomme la plage des prix totaux (cela fonctionne très bien)
ActiveWorkbook.Names.Add Name:=Plage_Ref_Montant_HT, RefersToR1C1:=Selection

If NBEntreprise = 2 Then Range(Cells(LgRef, 7), Cells(LgRef, 12)).Select
If NBEntreprise = 3 Then Range(Cells(LgRef, 7), Cells(LgRef, 17)).Select
If NBEntreprise = 4 Then Range(Cells(LgRef, 7), Cells(LgRef, 22)).Select
If NBEntreprise = 5 Then Range(Cells(LgRef, 7), Cells(LgRef, 27)).Select

'nomme la plage des noms des entreprises (cela fonctionne très bien)
ActiveWorkbook.Names.Add Name:=Plage_Ref_Nom_Entreprise, RefersToR1C1:=Selection

' dans les dernières colonnes
For A = 1 To NBEntreprise
ActiveCell.Offset(0, 5).Select 'atteint la colonne pour insérer la formule d'extraction
Next A
'formules pour l'extraction des noms d'entreprises PU Tot Mini, PU Tot Moyen, PU Tot Median et PU Tot Maxi
ActiveCell.FormulaR1C1 = "=INDEX(!Plage_Ref_Nom_Entreprise,MATCH(R[-9]C,!Plage_Ref_Montant_HT))"
ActiveCell.Offset(0, 1).FormulaR1C1 = "=INDEX(!Plage_Ref_Nom_Entreprise,MATCH(R[-9]C,!Plage_Ref_Montant_HT))"
ActiveCell.Offset(0, 2).FormulaR1C1 = "=INDEX(!Plage_Ref_Nom_Entreprise,MATCH(R[-9]C,!Plage_Ref_Montant_HT))"
ActiveCell.Offset(0, 3).FormulaR1C1 = "=INDEX(!Plage_Ref_Nom_Entreprise,MATCH(R[-9]C,!Plage_Ref_Montant_HT))"
(Là est le problème)
Je ne connais pas la syntaxe pour intégrer dans la formule la variable qui sert de référence pour les plages.
En fait je ne peux pas inclure le nom directement vu qu'il y a plusieurs feuilles pour les lots (noms différents) et plusieurs entreprises par lot (variables de 2 à 30) exemple ici avec 5 ...

Excel 2007 et Excel 2016

je continue de chercher ...

A bientôt
Daniel38

 

[Daniel38]

Oops je ne comprends pas trop resize ?
en fait si tu regardes la cellule qui est sélectionnée en dernier est la position de la 1ere formule
les noms des plages fonctionnent déjà (tu peux effacer les noms et cliquer sur le bouton pour vérifier)
ce qui m’intéresse c'est les formules avec ces noms variables en fonction des plages et du nom du lot ...
là je bucheronne avec une formule conventionnelle en fonction du nombre d'entreprises ...

 

[Daniel38]

Pour 2 entreprises la formule est : ActiveCell.FormulaR1C1 = "=INDEX(RC7:RC12,MATCH(R[-9]C,R[-9]C10:R[-9]C15))"
Pour 3 entreprises la formule est : ActiveCell.FormulaR1C1 = "=INDEX(RC7:RC17,MATCH(R[-9]C,R[-9]C10:R[-9]C20))"

il y a toujours 5 colonnes entre les formules (au moins ça c'est figé)

 

[Staple1600]

Re

Tu n'as pas répondu à la question de mon précédent message?

PS: je remanie ton code selon une syntaxe qui me parle et surtout qui évite tous ces affreux ActiveCell, Select et autre Activate.
Si cela ne réponds pas pour le moment à ta question, tant pis
Mais au moins, je comprends ce que j'écris dans mon VBE.

 

[Daniel38]

La dernière cellule sélectionnée est celle ou il doit y avoir la formule
je ne peux pas intégrer ton Vbe car il ne correspond pas au problème
pas facile en fait peut-être impossible ?
activecell n'est pas horrible lol je continue avec

 

[Staple1600]

Re

1) la question était celle-ci

Tu peux me dire, si en procédant ainsi le nommage est identique au niveau des adresses des cellules

Et sauf erreur, tu n'y a pas répondu

2) J'ai testé ton code
Aucune formule avec INDEX n'est inséré
(en tout cas, pas trouvé où lors de mon test)

3) Je ne t'ai jamais dit d'intégrer mes tests dans ton classeur
(juste de tester puisque nous ne sommes qu'en période de test)

5) Surtout les forums dédiés à Excel, tu liras qu'il est fortement déconseillé de sélectionner les cellules en VBA
Cela ralentit considérablement l’exécution du code.
(Bonc basta ActiveCell et tutti quanti )

 

[Dranreb]

Bonsoir.

Je ne connais pas la syntaxe pour intégrer dans la formule la variable qui sert de référence pour les plages.

Vous voulez dire la variable valant le nom, pas sa référence, je suppose.

ActiveCell.Offset(0, 3).FormulaR1C1 = "=INDEX(" & Plage_Ref_Nom_Entreprise & ",MATCH(R[-9]C," & Plage_Ref_Montant_HT & "))"

Mais à part ça, votre programmation est illisible à cause des Select et Selection et aussi des noms trop longs, tant ceux des variables que ceux dans le classeur.

 

[Daniel38]

Merci pour cette réponse, c'est parfait
je ne vois pas où est-ce illisible ?
Les noms sont longs parce qu'ils se nomment en fonction des lots, ...
En ce qui concerne les select et sélection, je suis preneur pour une autre façon sachant que celle-ci fonctionne très bien et rapidement

encore merci, bravo, c'était donc le &

 

[Staple1600]

Bonsoir le fil, Daniel38, Dranreb

Nous étions donc au moins deux à conseiller de se passer des Select et cie

Daniel38
Toujours pas de réponse à ma question (cf message#20) ni à mon observation concernant les formules INDEX...
Sinon

En ce qui concerne les select et sélection, je suis preneur pour une autre façon

N'est-ce pas ce qu'on peut lire dans le message#15 ?

 

[Daniel38]

Hello Staple
Pourquoi Dranreb ?
Entre-temps je suis passé à autre chose vu que la syntaxe Index fonctionne et je n'ai pas testé ton dernier code.
C'est sur que faire sélectionner des cellules ralentit l'exécution mais comment faire quand tu as des tableaux différents à chaque fois (nb ligne et nb de colonnes ?
Donc chacun sa façon de faire comme tu l'avais dit avant...
Merci pour tes conseils
A bientôt

 

[Dranreb]

Hello Staple
Pourquoi Dranreb ?

Ben c'est peut être plutôt à moi qu'il eu été plus logique de le demander, non ?
Pal mal l'écorchent en mettant notamment Dranred, mais une bonne moitié on compris que c'était Bernard écrit à l'envers.

comment faire quand tu as des tableaux différents

On préfère utiliser des variables As Workbook, Worksheet ou Range plutôt que Select et Selection.

 

[Staple1600]

Re

Daniel38
Comme je l'ai dit précédemment, j'ai pris le temps de tester ton code VBA (sans le modifier) sur ton classeur exemple
Et je reste toujours sans réponse de ta part sur cette observation

2) J'ai testé ton code
Aucune formule avec INDEX n'est inséré
(en tout cas, pas trouvé où lors de mon test)

[aparté désabusé]
N'est-ce pas un peu cavalier de venir sur un forum d'entraide, demander assistance, sans prendre le temps de tester les réponses que "les répondeurs" (qui eux prennent le temps de la cogiter, tester etc...) ?
[/aparté désabusé]

 

[Dranreb]

En dehors de ce je disais au poste #25, je crois qu'il vaudrait mieur travailler avec les collections Name des objets Worksheet plutôt que celle de l'objet Workbook, en prenant toujours les mêmes noms (de grâce, courts !) dans toutes les feuilles. Mais je n'en suis pas sûr tant votre code m'était lourd à déchiffrer.

 

[Daniel38]

Bernard
clair que le code vous avez du batailler pour déchiffrer , désolé mais celui-ci fonctionne bien et rapide malgré tout, j'ai du mal avec les codes que propose Staple ...
J'utilise dès que je peux les méthodes worksheet, name mais les noms sont longs car ils sont générés automatiquement (beaucoup plus lisible ensuite dans la barre de formule à la place d'un $C$512, avec les noms on sait de quoi on parle.
Ensuite la puissance de nos machines nous permet d'éviter de trop programmer genre C++ ou autre codage illisible 1 ou 2 ans après.
Pour conclure, j'ai toujours programmé comme ça depuis 1986
Merci encore

 

[Staple1600]

Bonsoir

Daniel38
Donc je resterai avec mon interrogation
J'ai testé ta macro Formules et je n'ai pas trouvé dans quelles cellules étaient inscrites tes formules
(je parle de cette partie de ton code)

 '#### le problème est ici
            'formules pour l'extraction des noms d'entreprises PU Tot Mini, PU Tot Moyen, PU Tot Median et PU Tot Maxi
            ActiveCell.FormulaR1C1 = "=INDEX(!Plage_Ref_Nom_Entreprise,MATCH(R[-9]C,!Plage_Ref_Montant_HT))"
            ActiveCell.Offset(0, 1).FormulaR1C1 = "=INDEX(!Plage_Ref_Nom_Entreprise,MATCH(R[-9]C,!Plage_Ref_Montant_HT))"
            ActiveCell.Offset(0, 2).FormulaR1C1 = "=INDEX(!Plage_Ref_Nom_Entreprise,MATCH(R[-9]C,!Plage_Ref_Montant_HT))"
            ActiveCell.Offset(0, 3).FormulaR1C1 = "=INDEX(!Plage_Ref_Nom_Entreprise,MATCH(R[-9]C,!Plage_Ref_Montant_HT))"

PS: Comment peux-tu avoir du mal avec mon code puisque tu dis toi-même ne pas l'avoir testé...

Hello Staple
Pourquoi Dranreb ?
et je n'ai pas testé ton dernier code.

 

[Daniel38]

Hello Staple
Effectivement je suis cavalier mais uniquement avec les nanas (je plaisante)
Je pense que le problème soulevé était intéressant, la preuve tu as répondu et je t'en remercie encore, j'ai également fait un fichier sur lequel tu peux vérifier et adapter ton code, on reste en contact pour d'autres procédures ardues ...

Comme expliqué avant ton code est un peu difficile en lecture pour moi.
J'ai fait une réponse à Bernard ci-avant aussi

Voilà, (problème étant résolu pour moi)