nommer une plage de cellule à l'aide d'une macro

[Jacques]

Bonjour ou plutôt bonsoir le forum,

Je vais essayer d'être précis dans ma demande, je dispose d'un tableau avec un nombre de colonne aléatoire, chaque colonne ne comportant pas le même nombre de ligne (nombre pouvant évoluer)

Je souhaite définir la plage de cellule non vide dans chaque colonne et lui attribuer le nom qui se trouve au dessus (dans l'exemple joint "joueurX")
Ceci afin de pouvoir appeler ces plages de celulles par leur nom pour définir plusieurs liste.
J'ai créé une macro avec mes connaissances certes limitées où je défini les adresses de début et de fin de liste mais je n'arrive pas à les utiliser pour définir ma plage.

J'espère être assez compréhensible.
Merci aux personne pouvant m'aider

Jacques

 

[Robert]

Bonsoir Jacques, bonsoir le forum,

Cette macro devrait te convenir :

Public Sub noms()
Dim fin(2) As Byte 'déclare la variable fin (tableau de trois valeurs : 0, 1 et 2)
'boucle sur les colonnes 5 à 7
For x = 5 To 7
'définit la variable fin (fin(0), fin(1) puis fin(2))
'représenant la dernière ligne éditée de chaque colonne
fin(x - 5) = Cells(65536, x).End(xlUp).Row
'ajoute un nom (relatif à la ligne 5) à la plage "6:dernière ligne" de chaque colonne
ActiveWorkbook.Names.Add Name:=Cells(5, x).Value, RefersTo:=Range(Cells(6, x), Cells(fin(x - 5), x))
Next x
End Sub

À plus,

Robert

 

[Robert]

Re Jacques, bonsoir le forum,

Désolé mais je me suis rendu compte que l'untilisation de la variable fin sous forme de tableau n'avait aucun intérêt. Voici la correction :

Public Sub noms()
Dim fin As Byte 'déclare la variable fin
'boucle sur les colonne 5 à 7
For x = 5 To 7
'définit la variable fin
'représenant la dernière ligne éditée de chaque colonne
fin = Cells(65536, x).End(xlUp).Row
'ajoute un nom (relatif à la ligne 5) à la plage "6:dernière ligne" de chaque colonne
ActiveWorkbook.Names.Add Name:=Cells(5, x).Value, RefersTo:=Range(Cells(6, x), Cells(fin, x))
Next x
End Sub

À plus,

Robert

 

[Lord Nelson]

Salut Jacques,

Encore une question intéressante de ta part, merci pour ce petit exercice.

Sub Nom_des_Joueurs()
Dim Col As Integer
Dim DerLigne As Long
Dim Temp As String
Col = 5
Do
If Cells(5, Col) = "" Then Exit Do
DerLigne = Cells(65536, Col).End(xlUp).Row
Temp = "=" & ActiveSheet.Name & "!" & Cells(6, Col).Address & ":" & Cells(DerLigne, Col).Address
ActiveWorkbook.Names.Add Name:=Cells(5, Col).Text, RefersTo:=Temp
Col = Col + 1
Loop
End Sub

Ca devrait le faire.

A+
Horatio

 

[Lord Nelson]

Désolé Robert, je ne voulais pas te marcher sur les pieds, mais tu as posté avant que j'aie fini ma petite cuisine...
A+
Horatio

 

[Robert]

Re Jacques, le forum, bonsoir Horatio,

M'en fous, j'ai des pompes à bout renforcé... (loule).
N'empêche que ta petite cuisine a l'avantage de fonctionner sur toutes les colonnes contenant un nom en ligne 5, alors que la mienne va s'arrêter lamentablement à la colonne 7... Bien joué !

À plus,

Robert

 

[@+Thierry]

Bonsoir Robert, Amiral, Jacques, le Forum

Voici encore une autre approche, tendant aussi à nommer toutes les plages de cellules par colonnes de ce qui se trouve dans la ligne 5 de la Feuille 1 (Feuil1)

NB pas de test, cette macro nommera tout ce qui ce trouve en dessous de la cellule remplie en ligne 5, et par conséquent les noms non valides ne passeront pas, sinon pour le reste ça semble rouler...

Option Explicit

Sub NamingPlage()
Dim HeadColumns As Range, Cell As Range
Dim ColLetter As String
Dim PlageName As String
Dim CellEnd As String
Dim TestInfo As String

  With Feuil1
    Set HeadColumns = .Range(.Range("A5").Address(0, 0), .Range("IV5").End(xlToLeft).Address(0, 0))
  End With

   For Each Cell In HeadColumns
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If Cell <> "" Then
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PlageName = Cell
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ColLetter = Left(Cell.Address(False, False), (Cell.Column < 27) + 2)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CellEnd = Feuil1.Range(ColLetter & "65536").End(xlUp).Address

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'ICI c'est just des tests que je laisse....
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;TestInfo = TestInfo & "Colonne " & ColLetter & vbTab
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;TestInfo = TestInfo & Cell.Offset(1, 0).Address & ":" & CellEnd & vbTab
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;TestInfo = TestInfo & PlageName & vbCrLf


&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;With ThisWorkbook
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.Names.Add Name:=CStr(PlageName), RefersTo:="=Feuil1!" & Cell.Offset(1, 0).Address & ":" & CellEnd
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End With
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If
&nbsp;&nbsp;&nbsp;Next

'Retour du Test
MsgBox "Voici les infos du test" & vbCrLf & TestInfo
End Sub

PS on notera "l'amusant" moyen de retourner la lettre d'une colonne !! (lol)

Bonne nuit
@+Thierry

 

[Jacques]

Merci à tous pour vos idées, je vais essayer ça et je vous redis si ça marche.

A+

Jacques

 

[Lord Nelson]

Bonjour à tous !

Ca c'est superbe :

ColLetter = Left(Cell.Address(False, False), (Cell.Column < 27) + 2)

Un grand bravo à Thierry !

A+
Horatio

 

[Jacques]

Salut à tous,

C'est formidable, ça marche à la perfection, vous êtes vraiment tous excellent.
Au nom de tous les utilisateurs du forum un grand bravo pour l'aide que vous nous apportez.

Jacques

 

[CHti160]

Salut "Jacques" Robert , l'Amiral et @+Thierry
hier Hervé m'a aussi concocté une procédure contenant la ligne ci dessous
colonne = Left$(c.Address(0, 0), (c.Column < 27) + 2)
équivalente je pense à celle de maitre @+Thierry Lol
ColLetter = Left(Cell.Address(False, False), (Cell.Column < 27) + 2)][/b
peut on m'en expliquer la signification pourquoi (c.column<27+2)
sûrement évident mais ?????
Merci d'avance
A+++
Jean Marie

 

[Hervé]

Salut Chti160

Left(Cell.Address(False, False), (Cell.Column < 27) + 2)

(Cell.Address(False, False), =récupére l'adresse sous la forme A1 , BB26, etc...

cell.column<27+2 = si le numéro de la cellule est supérieur à 26, extrait les deux caractères à gauche de l'adresse, sinon extrait seulement le premier.

comme cell.column<27 renvoi vrai ou faux lorsque tu ajoutes 2 tu le transforme en nombre ce qui te permet de l'utilisé avec left.

C'est comme lorsque on utilise *1 dans les sommeprod on transforme un vrai ou faux en numérique.

PS : j'espère avoir été clair je suis pas très pédago.

Salut
Hervé

 

[CHti160]

Dabord Salut "Hervé"
encore Merci por hier
et on ne sait jamais qui est là(Forum) donc je te cite et tu arrives c'est pas beau ça!!
de plus il est déconseillé d'appeler directement les participants.
C'est super j'ai compri tu est donc bon pédagogue (rassuré)lol
A+++
Jean Marie

 

[@+Thierry]

Bonjour Jean Marie, Hervé, Horatio, Jacques, Robert, le Forum

Tout d'abord content que nos réponses puissent satisfaire la demande initiale de Jacques, et merci pour ses compliments et remerciements.

Pour complément d'info à la question de "Chti-Jean Marie", en fait il faut connaitre la valeur numérique d'une Bollean... En effet, lorsque des valeurs de type Boolean sont converties en d'autres types de données, False devient 0 et True devient -1
(D'ailleur on peux aussi paramétrer les valeur de tel ou tel paramètre de la même manière, exemple "UserForm.Show 0", est exactement la même chose que "UserForm.Show False")

Le reste est un simple calcul si le numéro de colonne est plus petit que 27 on va retourner -1, sinon on va retourner 0, soit avec un ajout de + 2 ce sera donc une valeur numérique : soit 1, soit 2

Mis en pratique celà donne :

ColLetter = Soit 1 ou 2 caractère(s) à gauche de la Valeur Relative de l'adresse de la Cellule Scannée.

Etant donné que ColLetter est préalablement déclarée de type String le Left$ n'est pas nécessaire, Left suffit.

Pour rendre à César ce qui est à César, je ne sais plus d'où vient cette astuce, mais je l'ai vu passer il y a pas mal d'années avant qu'XLD existe, sur le MPFE, toute la difficulté résidant dans le fait de savoir s'en rappeler lorque l'on en a besoin !!!

Bon Dimanche
@+Thierry

 

[Lord Nelson]

Salut tout le monde,

Ce post est décidemment fort intéressant, merci à tous.

Nous te suivons bien dans ta logique booléenne, Thierry, en VB ou VBA
Faux = 0
Vrai = -1

Oui, mais alors pourquoi la logique Excel est-elle différente dans les formules ? avec
Faux = 0
Vrai = 1

(Souvenons-nous à cet égard des superbes SOMMEPROD de Monique qui exploite à fond ce Vrai = 1).

Quelqu'un connaît-il la raison logique de cette inversion de signe ?

A+
Horatio