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

Daniel38

XLDnaute Occasionnel
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

XLDnaute Barbatruc
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)
VB:
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

XLDnaute Occasionnel
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

XLDnaute Barbatruc
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

XLDnaute Occasionnel
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

XLDnaute Barbatruc
Re

Un exemple de petit test (vite fait)
VB:
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

XLDnaute Occasionnel
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

XLDnaute Barbatruc
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)
VB:
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
 

Discussions similaires

Réponses
17
Affichages
1 K

Statistiques des forums

Discussions
313 258
Messages
2 096 614
Membres
106 684
dernier inscrit
ekouepatrick