Intégrer le calcul du nombre de ligne en VBA

Bonjour,

J'aurai besoin d'un petit coup de main pour intégrer le calcul du nombre de ligne d'une colonne pour l'intégrer dans une formule. Ce n'est pas très clair comme ça en le disant, mais voici le code :

J'aimerai intégrer ma variable Nblg2 au calcul de la formule au lieu de $I$2:$I$5.

J'ai essayé en mettant Range("I2:I" & Nblg2)... mais ça ne fonctionne pas.

Private Sub Worksheet_activate()

Dim Nblg2 As Long

  Nblg2 = Sheets("Tâches à faire").Range("I" & Rows.Count).End(xlUp).Row

    With Range("B7").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="='Tâches à faire'!$I$2:$I$5"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

End Sub

Merci à ceux qui m'aideront

Bonjour mp911

mp911 a écrit :

Nblg2 = Sheets("Tâches à faire").Range("I" & Rows.Count).End(xlUp).Row

andrea73 a écrit :

par ce que si j'ai bien compris ta zone de cellule commence en ligne 2 !?

Oui, elle commence bien en ligne 2.

Mais ce que je veux faire, c'est dans le calcul de la fonction "Formula1" dans le code au lieu d'avoir

Formula1:="='Tâches à faire'!$I$2:$I$5"

J'aimerai un truc qui ressemble à ça, mais qui fonctionne

Formula1:="='Tâches à faire'!Range("I2:I" & Nblg2)"

(re)

Ok

Alors comme ça (au feeling sans essayer) peut-être que

andrea73 a écrit :

Formula1:="='Tâches à faire'!Range("I2:I" & Mid(Str(Nblg2),2)"

Explications :

1. Str( fait la conversion en texte DONC (par exemple nblg2 = 9 Devient " 9" par ce qu'il garde une place pour le signe négatif)
2. Mid( enlève cette espace en ne prenant le Str() qu'à partir du 2ème caractère

Non, ça ne fonctionne pas.

En fait, je ne comprends pas pourquoi

Range("I2:I" & Nblg2)

ne fonctionne pas dans mon cas. Pour toutes mes autres commandes ça fonctionne... il doit manquer un caractère ou un truc qui cloche avec l'exécution que je fais.

(re)

Je ne sais pas si tu as essayé ma dernière formule ? (j'ai l'impression que nos post ce sont croisés !)

Et si tu envoyais un PJ pour que je teste de visu !

andrea73 a écrit :

(re)

Je ne sais pas si tu as essayé ma dernière formule ? (j'ai l'impression que nos post ce sont croisés !)

Et si tu envoyais un PJ pour que je teste de visu !

Oui, j'ai essayé ta formule, mais ça ne fonctionne pas.

Merci quand même pour ton aide

Bonsoir

Bonsoir andrea73

Si j'ai compris c'est tout simple

Private Sub Worksheet_activate()

Dim Nblg2 As Long

  Nblg2 = Sheets("Tâches à faire").Range("I" & Rows.Count).End(xlUp).Row

  With Range("B7").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
         xlBetween, Formula1:="='Tâches à faire'!$I$2:$I$"& Nblg2
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
  End With

End Sub

Bonsoir Banzai64,

Merci pour ton aide, mais ça ne fonctionne pas parfaitement.

En pièce jointe, mon fichier pour mieux comprendre.

Je voudrais que sur la feuille Travail, en B7, ma liste déroulante s'adapte au nombre de valeur que j'ai en colonne de la feuille "Tâche à faire"

En mettant juste le Nblg2 à la fin, ça me met plein de blancs dans la listbox

Bonsoir

Cela fonctionnera quand tu enlèvera le 5

Formula1:="='Tâches à faire'!$I$2:$I$5"& Nblg2

Banzai64 a écrit :

Bonsoir

Cela fonctionnera quand tu enlèvera le 5

Formula1:="='Tâches à faire'!$I$2:$I$5"& Nblg2

Ah mais quel boulet !!!!!!!!!!!!!!!!!!!!!

Merci beaucoup Banzai64, c'était facile en fait.