Microsoft 365 Macro pour redimensionner une zone nommée

[chris6999]

Bonjour le forum

Je suis à la recherche d'un code me permettant de redimensionner une plage nommée en fonction de données variables.

Problème 1 : mon code ne fonctionne pas lorsque je ne suis pas sur la feuille où se trouve cette plage, ce qui me pose un pb dans mon projet.
Problème 2 : mon code "bricolé" ne bloque pas quand je suis sur la feuille en question mais la modification de la plage n'a rien avoir avec ce que ce que j'attends.

Je mets un fichier test pour que vous compreniez ma problématique.

J'espère qu'une bonne âme pourra m'aider car je cherche depuis des heures et je ne trouve pas de solution ...

Merci par avance pour votre aide

 

[vgendron]

bonjour

essaie ceci

Sub REDIMENSIONNER_PLAGE_NOMMEE()

Dim ligne As Variant

valeur_recherchée = "b"

Set ligne1 = Sheets("Historique des intégrations").Range(Cells(2, "B"), Cells(60000, "B")).Find(What:=valeur_recherchée, LookAt:=xlWhole)
If Not ligne1 Is Nothing Then ligne_début = ligne1.Row

Set ligne2 = Sheets("Historique des intégrations").Range(Cells(2, "B"), Cells(60000, "B")).Find(What:=valeur_recherchée, SearchDirection:=xlPrevious, SearchOrder:=xlByRows)
If Not ligne2 Is Nothing Then ligne_fin = ligne2.Row

'Historique_extractions
Dim maplage As String, derligne As Long
 
maplage = "$A$" & ligne_début & ":$A$" & ligne_fin

'Modification de la plage
With ActiveWorkbook.Names("Historique_extractions")
        '.Name = "Historique_extractions"
        .RefersTo = "='Historique des intégrations'!" & maplage
End With
 

End Sub

PS,on ne connait pas la finalité de cette plage nommée, mais je pense que tu aurais tout intérêt à utiliser les tableaux strucuturés

 

[chris6999]

Bonjour

Le but est d'alimenter une listbox.

Merci le code fonctionne enfin mais en revanche je ne peux pas le lancer depuis une autre feuille.
J'ai un debug car il n'arrive pas à définir la plage. Une idée pour corriger ce pb?

BMerci encore
Cordialement

 

[chris6999]

Bonjour

Le but est d'alimenter une listbox.

Merci le code fonctionne enfin mais en revanche je ne peux pas le lancer depuis une autre feuille.
J'ai un debug car il n'arrive pas à définir la plage. Une idée pour corriger ce pb?

BMerci encore
Cordialement

J'ai trouvé ! Cela donne


Sub REDIMENSIONNER_PLAGE_NOMMEE()

Dim ligne As Variant
Dim plage1 As Range

valeur_recherchée = "b"

Set plage = Sheets("Historique des intégrations").Range("B2:B60000")
Set ligne1 = plage.Find(What:=valeur_recherchée, LookAt:=xlWhole)
If Not ligne1 Is Nothing Then ligne_début = ligne1.Row


Set ligne2 = plage.Find(What:=valeur_recherchée, SearchDirection:=xlPrevious, SearchOrder:=xlByRows)
If Not ligne2 Is Nothing Then ligne_fin = ligne2.Row

'Historique_extractions
Dim maplage As String, derligne As Long

maplage = "$A$" & ligne_début & ":$A$" & ligne_fin


'Modification de la plage
With ActiveWorkbook.Names("Historique_extractions")
'.Name = "Historique_extractions"
.RefersTo = "='Historique des intégrations'!" & maplage
End With


End Sub

 

[vgendron]

Je pense que si tu postais ton vrai fichier, on pourrait surement t'orienter et t'aider a simplifier ton programme avec des méthodes plus propres et sures

 

[vgendron]

sinon, ta macro modifiée pour qu'elle puisse etre exécutée de n'importe quelle feuille

Sub REDIMENSIONNER_PLAGE_NOMMEE()

'déclarations
Dim ligne As Range
Dim maplage As String, derligne As Long

    valeur_recherchée = "b"
    
    With Sheets("Historique des intégrations") 'dans la feuille (pas besoin de l'activer)
        Set ligne1 = .Range(.Range("B2"), .Range("B6000")).Find(What:=valeur_recherchée, LookAt:=xlWhole)
        If Not ligne1 Is Nothing Then ligne_début = ligne1.Row
        
        Set ligne2 = .Range(.Range("B2"), .Range("B6000")).Find(What:=valeur_recherchée, SearchDirection:=xlPrevious, SearchOrder:=xlByRows)
        If Not ligne2 Is Nothing Then ligne_fin = ligne2.Row
    
        maplage = "$A$" & ligne_début & ":$A$" & ligne_fin
    End With
    
    With ActiveWorkbook.Names
        .Add Name:="Historique_extractions", RefersTo:="='Historique des intégrations'!" & maplage
    End With
End Sub

 

[patricktoulon]

bonsoir messieurs(juste en passant)
je cite

Le but est d'alimenter une listbox.

ok
et imaginons que ligne2=ligne1(c'est à dire qu'il y a qu'un seule "B")
cela veut dire que vous allez devoir avoir deux modes de remplissage par le .list
car justement le value d'une plage nommée d'une seule cellule renverra la valeur et non un tableau
voir même(question ) la listbox doit elle être remplie si il n'y a pas de ventilation?
@vgendron
maplage = "$A$" & ligne_début & ":$A$" & ligne_fin
ok et justement!! si ligne_fin est nothing on fait quoi ?
on clique ok sur le message d'erreur et on prie Ste madeleine ?

Voyez plus loin dans la conception de vos macros

 

[vgendron]

la définition de ligne1 et ligne2, je n'y ai pas touché... ca vient du demandeur..
j'ai juste corrigé la définition de maplage pour qu'elle aille au bon endroit et qu'elle ne dépende pas de la cellule active au moment de lancer la macro.

voir plus loin, je sais faire.. ne soyez pas aussi condescendant

 

[vgendron]

@chris6999
un peu de lecture ici sur les listbox et leurs méthodes de remplissage possibles

Utiliser les contrôles dans un UserForm, en VBA Excel

Utiliser les contrôles dans un UserForm, en VBA Excel

silkyroad.developpez.com

 

[TooFatBoy]

Bonjour,

on ne connait pas la finalité de cette plage nommée, mais je pense que tu aurais tout intérêt à utiliser les tableaux strucuturés

Effectivement, ça devrait sûrement éviter tout ce micmac pour calculer l'adresse de cette plage.

 

[chris6999]

Bonjour,


Effectivement, ça devrait sûrement éviter tout ce micmac pour calculer l'adresse de cette plage.

Je pense que si tu postais ton vrai fichier, on pourrait surement t'orienter et t'aider a simplifier ton programme avec des méthodes plus propres et sures

Bonjour et merci pour ton aide mais grâce à ta première proposition j'ai réussi à faire ce que je voulais (voir mon message de 18h53).
Je ne mets jamais mes vrais fichiers qui sont un peu lourd et qui contiennent des données sensibles.
Bon week end

 

[TooFatBoy]

On a bien vu ton "message de 18h53", mais ce qu'on voulait te dire c'est qu'avec des TS tu n'aurais pas besoin de calculer les adresses. Mais bon, si tu ne veux pas en entendre parler, c'est ton droit bien évidemment.


@+

 

[Staple1600]

Bonjour le fil

[Juste en passant]

Je ne mets jamais mes vrais fichiers qui sont un peu lourd et qui contiennent des données sensibles.

L'usage c'est de ne jamais joindre de fichiers originaux.
Mais de prendre le temps, de créer un fichier exemple qui illustre simplement le problème rencontré
(en général deux, trois feuilles suffisent, sur chaque feuille une vingtaine de lignes)
Et comme tu utilises Excel 365, tu as tout ce qu'il faut pour créer rapidement des données "bidons"
Exemple

Sub Creer_Exemple()
Dim r As Range
[A1] = "ENTETE_1"
[A1].AutoFill Destination:=Range("A1:J1"), Type:=xlFillDefault
Range("A2").Formula2R1C1 = "=RANDARRAY(25,10,100,1000,TRUE)"
Set r = [A1].CurrentRegion: r = r.Value
r.Borders.Value = 1
End Sub

[/Juste en passant]