Liste déroulante Userform en fonction d'un nombre de lignes variable

Bonjour à tous,

Désolé de vous embêter avec ce problème mais j'ai beau me relire impossible de trouver mon erreur

J'ai un tableau qui me sert dans un UserForm. Il me permet de récupérer des prénoms que je place dans des liste déroulantes, ces listes servent derrière à d'autre fonctions comme envoyer un mail au prénom sélectionné. Rien de bien compliqué et pourtant...

Je vous joins mon exemple pour que ce soit plus clair, en gros ma colonne 1 est la B, la seconde est la F, je viens donc chercher toutes les cases non vides dans ces deux colonnes pour les compiler dans deux listes déroulantes, ce qui me permet de pouvoir ajouter à souhait des prénoms supplémentaires en toute simplicité. Cela fonctionne parfaitement dans la colonne 1, j'ai ajouté deux lignes et elles s'intègrent bien, mais dans la colonne F impossible, j'ai une erreur d’exécution 9. Pourtant le code est un Copier/Coller avec changement de variables. Quelqu'un saurait-il m'aider à trouver le bug ?

Voici mon code:

Sheets("Employé(e)s").Activate
    nbrCommercial = Application.WorksheetFunction.CountA(Range("B3:B100"))
    nbrDeviseur = Application.WorksheetFunction.CountA(Range("G3:G100"))
    nbrCategorie = Application.WorksheetFunction.CountA(Range("K3:K100"))

    ReDim equipeCommerciale(nbrCommercial, 3)
    ReDim equipeDeviseur(nbrDeviseur, 3)
    ReDim categorieDevis(nbrCategorie, 3)

    'Compilation de l'équipe commerciale
    For i = 0 To nbrCommercial
        For J = 0 To 3
            equipeCommerciale(i, J) = Sheets("Employé(e)s").Cells(3 + i, 2 + J) '2 = Colonne prénom Commercial
        Next J
    Next i

    'Compilation de l'équipe technique
    For i = 0 To nbrDeviseur
        For J = 0 To 3
            equipeDeviseur(i, J) = Sheets("Employé(e)s").Cells(3 + i, 7 + J) '7 = Colonne prénom Deviseur
        Next J
    Next i

    'Compilation des catégories
    For J = 0 To nbrCategorie
        For i = 0 To 3
            categorieDevis(J, i) = Sheets("Employé(e)s").Cells(3 + J, 11 + i) '11 = Colonne lettre Catégorie
        Next i
    Next J

    'Création des listes déroulantes dans l'userform "Données_Initiales"
    For i = 0 To nbrCommercial
        If equipeCommerciale(i, 0) <> "" Then
            Commercial.AddItem equipeCommerciale(i, 0)
        End If
    Next i
    For i = 0 To nbrDeviseur
        If equipeDeviseur(i, 0) <> "" Then
            Deviseur.AddItem equipeDeviseur(i, 0)
        End If
    Next i
    For i = 0 To nbrCategorie
        If categorieDevis(i, 0) <> "" Then
            Catégorie.AddItem categorieDevis(i, 0)
        End If
    Next i

Hello,

ça me parait bien compliqué ton affaire ....

Sauf si j'ai loupé qqch dans ton code ... mais ton usine à gaz peut se réduire à ça :

    Dim lngLastEmployee&, i&
    With Worksheets("Employé(e)s")
        lngLastEmployee& = .Cells(Rows.Count, "C").End(xlUp).Row
        For i = 3 To lngLastEmployee&
            If .Cells(i, "B") <> Empty Then Commercial.AddItem .Cells(i, "B")
            If .Cells(i, "G") <> Empty Then Deviseur.AddItem .Cells(i, "G")
            If .Cells(i, "K") <> Empty Then Catégorie.AddItem .Cells(i, "K")
        Next i
    End With

Certes moins puriste et chic que passer par des tableaux mais tellement simple à s'approprier ...

A+

Salut Rag et merci pour ta réponse

Je débute, alors je suis passé par là ou je trouvais des solutions que je comprenais c'est difficile car pour chaque chose on peut passer par une tannée de formulations différentes.

Si je comprends ton code, tu comptes le nombre de cases dans la colonne C qui sont remplies en partant du bas et en remontant ce qui te donne le nombre de lignes puis tu viens chercher ce nombre de lignes en B/G/K c'est cela ? Mais cela implique t'il que les colonnes B/G/K aient le même nombre de lignes remplies ou si j'ai par exemple 4 lignes en B, 6 en G et 20 en K cela va remplir correctement mes listes déroulants ?

Hello,

Je détermine la dernière ligne de ton tableau. Ici j'ai vu que ta colonne C était toujours alimentée donc j'ai pris celle la.

Maintenant si ta colonne C est alimentée jusqu'en ligne 10 et la colonne G 25, le programme boucle jusque la ligne 10.

Il te faut une colonne toujours remplie, normalement dans une structure de base de données tu as une colonne ID toujours alimentée dès que tu ajoute de la donnée dans une ligne.

Maintenant, si vraiment ça t'embete au plus haut point d'ajouter une colonne comme ceci, tu peux insérer ton tableau dans un range via currentregion et compter le nb de ligne du range.

Ensuite je boucle sur chaque ligne directement dans la feuille, et si c'est pas vide, alors j'ajoute l'item à la combo.

Merci pour ces précisions, cela ne m’embête pas du tout d'ajouter cette colonne, ça parait logique avec cette formule d'avoir une colonne ID. Puis j'avoue que je ne connais pas encore le currentregion on va y aller en douceur

Par contre va falloir que je trouve pourquoi ton code ne fonctionne pas quand je l'intègre maintenant car j'ai un Code 9.

Trouvé, le soucis c'est que je fais appel aux variables de mes tableaux un peu partout après (nbrCommercial, equipeCommerciale...) du coup plus rien ne marche, j'avoue que réintégrer ta façon de faire dans mon code ça va être une belle galère comparé au soucis de départ.

J'ai essayé mais j'avoue que j'ai du mal a m'en sortir. la j'ai intégré ton code en mettant une colonne A et an cherchant mes anciennes variables:

 Dim lngLastEmployee&, i&

    With Worksheets("Employé(e)s")
        lngLastEmployee& = .Cells(Rows.Count, "A").End(xlUp).Row
        For i = 3 To lngLastEmployee&
            If .Cells(i, "B") <> Empty Then Commercial.AddItem .Cells(i, "B")
                                            mailCommercial = Cells(i, "D")
                                            initialesCommercial = Cells(i, "E")
                                            ChargéAffaire = Cells(i, "B") + Cells(i, "C")
            If .Cells(i, "F") <> Empty Then Deviseur.AddItem .Cells(i, "F")
                                            mailDeviseur = Cells(i, "H")
                                            initialesCommercial = Cells(i, "I")
                                            ChargéDevis = Cells(i, "F") + Cells(i, "G")
            If .Cells(i, "J") <> Empty Then Catégorie.AddItem .Cells(i, "J")
        Next i
    End With

Mais je bloque sur mes anciennes recherche de tableau du genre:

'Création du calendrier et du tableau de charge
        For i = 0 To nbLigneCalendrier 'Recherche de la date de début du devis
            If calendrier(i) = devisRestant(nombreDevisRestant, 4) Then
                'Recherche du deviseur
                For J = 0 To nbrDeviseur
                    If equipeDeviseur(J, 3) = devisRestant(nombreDevisRestant, 10) Then '10 = Colonne "Chargé de devis" -1
                        numDeviseur = J
                    End If
                Next J
                'Imputation de la charge sur la journée
                For m = 0 To duréeJour - 1
                    charge(i + m, numDeviseur) = charge(i + m, numDeviseur) + ETP
                Next m
            End If
        Next i
        nombreDevisRestant = nombreDevisRestant + 1
    Next t

Je ne comprends pas comment remplacer mes anciennes variables, car tu me donnes une façon de faire une liste déroulante OK, mais je dois au final garder mes anciens Redim quand même pour tout le reste de mon code.

Hello,

Tu en fais quoi de tes tableaux ?

Le prénom me sert à la liste déroulante. Le nom me sert avec le prénom en récapitulatif des personne chargé d'affaire et de devis, les initiales me servent la ou je copie les information (Onglet 2), et le mail me sert à envoyer le mail récap.

J'ai trouvé mon erreur il y a 10m en étape par étape, c'était plus loin quand je viens fait mon graphique j'avais limité le nombre de ligne, en la passant en illimité c'est nickel, en tout cas merci pour ta combine elle est parfait pour un menu déroulant, je l'ai appliqué sur un autre VBA que je bosse et c'est tellement rapide

Hello,

C'est surtout très simple à comprendre

Tant mieux si tu as réussi

a+