Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

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
 
Trier par date Trier par votes

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
 
Vote positif 0 Vote négatif

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
 
Vote positif 0 Vote négatif

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 ?
 
Vote positif 0 Vote négatif

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
 
Vote positif 0 Vote négatif

Staple1600

XLDnaute Barbatruc
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
 
Vote positif 0 Vote négatif

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
 
Vote positif 0 Vote négatif

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)
 
Vote positif 0 Vote négatif

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
 
Vote positif 0 Vote négatif
Identifiez-vous ou inscrivez-vous pour participer.

Discussions similaires

N
  • Résolu(e)
XL 2021 comment faire recherche dans une colonne et ligne
Réponses
5
Affichages
376
job75
B
  • Question
XL 2013 Comment trier les nom des feuilles et fusionner leur tableaux dans une une autre feuille ?
Réponses
10
Affichages
537
Compte Supprimé 979
C
  • Résolu(e)
Microsoft 365 VBA Insertion du numéro de semaine de manière dynamique
Réponses
11
Affichages
220
Etoto
G
  • Question
Microsoft 365 Export données
Réponses
4
Affichages
450
GClaire
G
D
  • Question
XL 2021 Filtre Multi Critères Listview VBA
2
Réponses
21
Affichages
1 K
Dranreb
S
  • Question
XL 2019 Problème dans un code vba
Réponses
2
Affichages
537
snoopy07
S
P
  • Question
Microsoft 365 Problème de destination à l'exécution d'une macro
Réponses
2
Affichages
423
Pimpon974
P
D
  • Question
XL 2016 VBA format date
2
Réponses
17
Affichages
1 K
vgendron
  • Résolu(e)
XL 2016 cellules A, b, C et D sont automatiquement vides
Réponses
3
Affichages
559
job75
I
  • Résolu(e)
XL 2019 Supprimer une ligne d'une base de données une fois qu'elle est transférée sur une autre feuille
Réponses
8
Affichages
583
Isabellisa
I
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…