XL 2013 Comment exprimer en chiffre la valeur numérique recueillie par une variable ?

[Sebast]

Bonjour à toutes et à tous,

je suis à la recherche d'un moyen pour exprimer en chiffre les bornes d'une plage.

Actuellement, afin de définir une liste de validation "sélective" qui va piocher ses infos dans une feuille plutôt qu'une autre, je passe par .Add xlValidateList, Formula1:=f
avec f étant la fonction définie préalablement.

Telle que la liste de validation est contruite, je dois écrire par exemple f = "=Agricole!$A$2:$A$11", c’est-à-dire je dois de visu constater la borne basse de la plage (ici A11)

Comme mes listes sont vivantes et appelées sans cesse à changer, j'aimerais pouvoir automatiser le repérage de l'extrémité des plages
(sans compter que j'en ai beaucoup, le fichier joint étant ultra-simplifié)
Je sais que je pourrai insérer des lignes vides, à renseigner au fur et à mesure et ainsi définir au préalable une borne lointaine suffisante mais cela perturberait la lecture …

Avec quelque chose comme
lastline = Sheets("Agricole").Range("A" & Rows.Count).End(xlUp).Row
je sais le faire MAIS ma la liste de validation n'accepte pas de variable
donc je dois convertir la variable lastline en chiffre (concrètement faire en sorte que lastline soit remplacé par le chiffre 11)…

Quelqu'un sait-il comment transformer la variable lastline en chiffre ?

D'avance, merci pour vos lumières

Private Sub Worksheet_Change(ByVal Target As Range)
Dim f As String
Dim SupportDansFeuilleSaisie As Range
Dim dernlign As Long

dernlign = Sheets("Saisie").Range("B" & Rows.Count).End(xlUp).Row
Set SupportDansFeuilleSaisie = Sheets("Saisie").Range("B2:B" & dernlign)


If Not Intersect(Target, SupportDansFeuilleSaisie) Is Nothing Then


For Each Target In SupportDansFeuilleSaisie
    Select Case Target
         Case "Divers"
                f = "=Frais_généraux!$A$2:$A$4"
         Case "Autre"
                f = "=Frais_généraux!$A$2:$A$4"
         Case "Produit"
                f = "=Atelier!$A$2:$A$5"
         Case Else
                f = "=Agricole!$A$2:$A$14"
    End Select



With Target(1, 2).Validation
     .Delete
     .Add xlValidateList, Formula1:=f
End With

Next Target

End If

End Sub

 

[David]

Re : Comment exprimer en chiffre la valeur numérique recueillie par une variable ?

Salut,

Si je comprend bien, tes listes peuvent s'allonger, mais ça marche parfaitement en mettant ta solution :
ex juste pour agricole.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim f As String
Dim lastline As Long
Dim SupportDansFeuilleSaisie As Range
Dim dernlign As Long

dernlign = Sheets("Saisie").Range("B" & Rows.Count).End(xlUp).Row
lastline = Sheets("Agricole").Range("A" & Rows.Count).End(xlUp).Row
Set SupportDansFeuilleSaisie = Sheets("Saisie").Range("B2:B" & dernlign)


If Not Intersect(Target, SupportDansFeuilleSaisie) Is Nothing Then


For Each Target In SupportDansFeuilleSaisie
    Select Case Target
         Case "Divers"
                f = "=Frais_généraux!$A$2:$A$4"
         Case "Autre"
                f = "=Frais_généraux!$A$2:$A$4"
         Case "Produit"
                f = "=Atelier!$A$2:$A$5"
         Case Else
                f = "=Agricole!$A$2:$A" & lastline
    End Select



With Target(1, 2).Validation
     .Delete
     .Add xlValidateList, Formula1:=f
End With

Next Target

End If

End Sub

@+

 

[gosselien]

Re : Comment exprimer en chiffre la valeur numérique recueillie par une variable ?

Bonjour,


ceci semble fonctionner:

dim Last as Long
---ton code
Case "Divers"
'f = "=Frais_généraux!$A$2:$A$4"
Last = Sheets("frais_généraux").[A65000].End(xlUp).Row
f = "=Frais_généraux!$A$2:$A$" & Last

--- ton code

P.

 

[Sebast]

Re : Comment exprimer en chiffre la valeur numérique recueillie par une variable ?

Bonjour David,
Salut Gosselien,

David : tu as raison, mon problème n’était que de syntaxe (je n’avais pas placé les guillemets où il fallait) …

Gosselien : idem
Je n’étais pas si loin, manifestement c’était chez moi un souci de syntaxe.


De plus, comme j’ai Excel 2013 (donc au-delà de 65 000 lignes) je pense que du coup on peut même écrire (même si c’est hautement improbable …) :

Last=sheets(« Frais_généraux »).range(« A » & rows.count).end(xlup).row
f = "=Frais_généraux!$A$2:$A$" & Last


Encore merci pour vos réponses rapides et efficaces. Sans votre aide, je serais resté sur mon constat d’échec !

 

[chris]

Re : Comment exprimer en chiffre la valeur numérique recueillie par une variable ?

Bonjour

Tes listes sont des tableaux : il suffit de nommer la colonne et cela suivra automatiquement
Exemple le nom Rubrique associé à =Tableau2[Rubrique]
fera que tes listes déroulantes utilisant le nom Rubrique seront toujours à jour si tu ajoutes ou enlèves des éléments.

Si ta source était en aussi en tableau, les listes de validation se propageraient aussi sur les nouvelles lignes.
Il suffit donc de les définir sur la 1ère ligne avant la saisie ou sur les lignes déjà remplies : après cela marche tout seul...

Edit : coup de fil pendant ma réponse, du coup j'arrive après Gosselin et David que je salue, mais je ne comprends pas pourquoi faire du VBA là où on peut automatiser sans....

 

[Sebast]

Re : Comment exprimer en chiffre la valeur numérique recueillie par une variable ?

Bonjour Chris,

merci pour ta précision.
Tu as raison, les tableaux présentent cet avantage ...
Cet exemple servira peut-être aux personnes ayant des versions sans liste/tableau.

Encore merci