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

 

[Staple1600]

Bonsoir le fil

Une façon plus courte de nommer une plage de cellules
(à tester d'abord sur un classeur vierge pour vérifier les adresses des plages nommées dans le gestionnaire de noms)

Sub test1()
On Error Resume Next
Names.Item("Plage_Ref_Montant_HT").Delete
LgRef = 1
NBEntreprise = 3 ' pour test
Range(Cells(LgRef, 10), Cells(LgRef, 10 + (NBEntreprise * 5) - 5)).Name = "Plage_Ref_Montant_HT"
End Sub
Sub test2()
On Error Resume Next
Names.Item("Plage_Ref_Montant_HT").Delete
LgRef = 2
NBEntreprise = 5 ' pour test
Range(Cells(LgRef, 10), Cells(LgRef, 10 + (NBEntreprise * 5) - 5)).Name = "Plage_Ref_Montant_HT"
End Sub

 

[Daniel38]

Merci pour cette proposition mais en fait le nom est lié nom du lot, j'avais oublié de le préciser

Plage_Ref_Montant_HT = "Ref_Plage_PT_HT_" & ActiveCell.Offset(0, 4) (en 0,4 de la feuille menu, c'est le nom du lot sous format 01_Maçonnerie_BA par exemple boucle faite sur toute les feuilles ...)

Plage_Ref_Nom_Entreprise = "Plage_Ref_Nom_Entreprise_" & ActiveCell.Offset(0, 4) pareil en 0,4 c'est le nom du lot

Il peut y avoir 30 lots (1 lot = 1 feuille) et plusieurs entreprises par lot (boucle pour changer de feuille et donc de nom faite avant) les noms fonctionnent bien.

Donc la syntaxe, comme je l'indique fonctionne bien jusque la, je nomme qu'à la fin des conditions selon le NBENTREPRISE et non à chaque fois...

Mon soucis c'est d'inclure les plages dans les formules, sur le site de Tosoft, il parle du ! point d'exclamation à mettre devant la "variable plage" mais je ne trouve pas la bonne syntaxe qui pourrait une fois la formule copiée sur la feuille, le nom réel est intégré à la formule ...
Pas facile ...

Merci encore pour la réponse

 

[Staple1600]

Re

Ma précédente proposition n'est pas une solution mais une information.
(Sur une autre syntaxe pour nommer une plage)
Si tu avais testé, tu aurais vu que ma syntaxe permet d'éviter les If NBEntreprise = n Then Range...

Peut-être qu'un petit fichier exemple simplifié simplifierait la vie de ceux qui seront tentés de te répondre, non ?

 

[Daniel38]

Compliqué d'envoyer un fichier car les tableaux sont énormes !!!

En fait voilà dans le module Vba, on écrit :
ActiveCell.FormulaR1C1 = "=INDEX(!Plage_Ref_Nom_Entreprise,MATCH(R[-9]C,!Plage_Ref_Montant_HT))"

et sur la feuille la formule apparait avec les noms correspondants à la feuille en cours de la boucle :
=INDEX(Plage_Ref_Nom_Entreprise_420_REVETEMENTS_SOLS_COLLES;EQUIV(DH60;Ref_Plage_PT_HT_420_REVETEMENTS_SOLS_COLLES))

Chaque feuille lot à un nom différent, un nombre de lignes et colonnes différent

j'ai bien noté ta syntaxe intérressante le fait d'éviter le SI pourrait faire gagner du temps

 

[Staple1600]

Re

Comme précédemment précisé...

Peut-être qu'un petit fichier exemple simplifié

NB: On ne joint jamais le fichier original, mais un fichier créé pour l'occasion, dont la vocation est d'illustrer la problématique rencontrée.

 

[Daniel38]

Je comprends bien mais comment adapter un fichier sur un problème sur une syntaxe comme expliquée
je vais voir ce que je peux faire ...

 

[Staple1600]

Re

C'est ta question..

A toi de voir si prendre le temps de créer un fichier avec quelques lignes, quelques plages nommées, et une portion adaptée en conséquence de ton VBA, favorisera sa résolution

 

[Staple1600]

Re

Un exemple de petit test (vite fait)

Sub PetitTest()
For i = 1 To 5
Cells(1, i).Resize(5).Name = "Zone_" & i
Next
' faire CTRL+F3 pour aller voir dans le gestionnaire de noms
MsgBox [Zone_2].Address
[A1:E5] = "=ROW()*COLUMN()"
'syntaxe avec formule
[G1].FormulaR1C1 = "=MATCH(15,Zone_5,0)"
'syntaxe VBA
[G3] = Application.Match(25, [Zone_5], 0)
End Sub

NB: A tester sur une feuille vierge.
(Cette macro n'a qu'un caractère illustratif, ce n'est pas une solution à la question)

EDITION: Bonsoir eriiiic

 

[Daniel38]

Wow OK ça à l'air top
je finalise le fichier la

 

[eriiic]

Bonjour à tous,

pourquoi un ! devant tes noms ?
On ne le met que si il y a un nom de feuille devant.
eric

 

[Daniel38]

Voila le fichier simplifié
je regarde ton module
merci

 

[Daniel38]

Eric
je cherche la syntaxe pour pouvoir coller la plage définie avec VBA dans la formule la feuille
oui le ! est aussi fait pour la feuille mais voila, on cherche

 

[Daniel38]

Staple
Merci pour l'exemple mais
[G1].FormulaR1C1 = "=MATCH(15,Zone_5,0)"
Zone_5 est le nom connu dans la formule, le problème c'est que je ne connais pas le nom, il est défini en fonction du nom du lot qui est variable ainsi que des plages Plage_Ref_Nom_Entreprise et Plage_Ref_Montant_HT ...

je pense que le fichier va pouvoir mieux expliquer le problème ...

Peut-être faire une formule spécifique en fonction de la colonne où se situera la formule mais bon pour 30 lots ça va faire 120 lignes au lieu de 30 x le nombre d'entreprises soit oops ! je ne suis pas couché ...

attention tableaux variables selon le nb d'entreprise et de lignes par lot, l'usine à gaz est avant (boucle, lastcell, like*, message d'attente, pas joint)

 

[Staple1600]

Re

J'ai donc ouvert le fichier
Tu peux me dire, si en procédant ainsi le nommage est identique au niveau des adresses des cellules
(j'ai changé les noms juste pour simplifier mon test)

Sub Test_Suite()
'test nommage simplifié
Dim i&, ws As Worksheet
i = 1
For Each ws In Worksheets
If ws.Name <> "Menu" Then
col = ws.Cells(3, Columns.Count).End(xlToLeft).Column
lig = ws.Cells(Rows.Count, 1).End(3).Row
ws.Cells(1, 1).Resize(lig, col).Name = "Plg_Ref_Ent" & "_" & i
ws.Cells(lig - 4, "J").Resize(, 6).Name = "Ref_Plg_HT_" & i
i = i + 1
End If
Next
End Sub