Methode find: recherche valeurs dans un tableau

Bonjour à tous

I need some help

J'ai un tableau composé de 4 colonnes: nom, date d'embauche, formé depuis le, en formation le.

Un Userform est également présent, composé d'un commandbutton et d'une listbox.

Mon objectif: cliquer sur le bouton et avoir dans la listbox les noms et les dates de formation à venir.

Mon problème: mes précédents USF (conçus en partie grâce à votre aide) utilisaient la méthode find, mais recherchaient une valeur définie (saisie dans une textbox ou combobox) dans un tableau.

Cette fois ci, je ne sais pas quoi mettre dans le "What:=" de .Find. En effet, je ne cherche pas une valeur en particulier, seulement les dates (ou valeurs), dans une colonne (la D dans ledit tableau).

Voici ma problématique illustrée:

Dim Ws as Worksheet

Dim Plage As Range, Depart As String

Dim ligne As Integer, i As Integer

ListBox1.Clear

Set Ws = Sheets(1)

With Ws

Set Plage = Ws.Rows("5" & Rows.Count).Find(what:= ??????? , LookIn:=xlValues, lookat:=xlWhole)

If Not Plage Is Nothing Then

    Depart = Plage.Address
    i = 0
    Do
        If ????????? Then

            ligne = Plage.Row

            Me.ListBox1.AddItem ligne 

            Me.ListBox1.Column(1, i) = Cells(ligne, 1)
            Me.ListBox1.Column(2, i) = Cells(ligne, 4)             
            i = i + 1
        End If
        Set Plage = Ws.Rows("5" & Rows.Count).FindNext(Plage)

    Loop While Depart <> Plage.Address

End If
End With

De plus, je me suis aperçu d'une lacune supplémentaire:

j'utilise très souvent des tableaux dans mes feuilles de calculs.

Je pense savoir comment les désigner (les tableaux ou certaines de leurs colonnes) correctement dans la barre de formule, mais je ne sais pas le faire en vba...

Après de nombreuses recherches (infructueuses), je m'en remets donc à vos lumières....

Merci d'avance,

Bonjour,

Examen préalable de la question :

Tu peux ou non utiliser un tableau Excel sur ta feuille, je ne suis pas de ceux qui conseillent de les utiliser systématiquement, mais si tu choisis de le faire, autant en exploiter les avantages, sinon je n'en vois pas l'intérêt !

Vu côté VBA, le premier avantage est qu'un tel tableau est nommé automatiquement par Excel, nom qui figure dans le Gestionnaire de noms. Et ce nom est utilisable par VBA dans les mêmes conditions qu'un nom de plage, il couvre les données du tableau, en-tête exclue...

La ligne 1 du tableau est la première ligne de données, on dispose en permanence du nombre de lignes de données (.Rows.Count), on ajoute une ligne en la plaçant immédiatement sous la dernière ligne du tableau (elle y sera automatiquement intégrée). Et on opère uniquement en référence au tableau, sans avoir à faire référence à la feuille.

Il importe par contre que le tableau n'inclue aucune ligne vide et il convient d'y veiller !

Deuxième élément, tu appuies sur un bouton pour afficher la ListBox, OK ! Pour l'heure tu n'as qu'une liste à afficher, et même si tu choisis ultérieurement de pouvoir en afficher d'autres, sur d'autres critères, autant que le Userform s'ouvre avec un contenu ! Inutile donc de l'ouvrir à vide pour avoir à appuyer sur un nouveau bouton afin d'afficher une liste...

Je remplace donc (momentanément, car des évolutions pourront par la suite amener à y substituer un autre type de contrôle) le bouton par une étiquette (Label) pour indiquer simplement la nature de ce qu'on affiche.

Le bouton, on le modifie, car le premier bouton à prévoir avec un Userform, c'est de le fermer ! On va donc affecter ce bouton à la fermeture... Besoin d'aucune autre commande pour l'instant.

La ListBox va devoir afficher une liste en deux colonnes : Nom et date. On ouvre donc la fenêtre de propriété, et on passe la propriété ColumnCount à 2. Comme il n'est pas prévu d'autres types d'affichage, on définit cette propriété par défaut, et on évite ainsi d'avoir à encombrer la procédure Initialize d'élément qui n'ont rien à y faire...

Au passage on en profite pour renommer les contrôles, c'est toujours recommandé... (NB- Et pour ma part, quand je renomme, c'est pour mettre des noms plus courts que les noms par défaut ! Autant de moins à écrire lorsqu'on code...)

Troisième élément, il faut penser à la liste à prélever, chercher les dates à venir de formation (supérieures à la date du jour donc), pour les placer dans la ListBox. La meilleure méthode sera donc de constituer un tableau à affecter directement à la ListBox (propriété List). La méthode Find pour rechercher les éléments à prélever n'est pas forcément la mieux adaptée dans ce cas (d'ailleurs, je crois que Find n'est la mieux adaptée que lorsque les autres sont inutilisables ou nettement plus lentes... et dans le cas de recherche de dates on s'évite souvent des difficultés en procédant autrement). C'est a-priori le parcours de la colonne dates de formation pour y détecter les dates à retenir qui paraît le plus logique : chaque fois qu'on en trouve une, on ajoutera une ligne à notre tableau (à affecter à la ListBox), en l'incrémentant...

On peut donc maintenant passer au code : une procédure pour fermer le Userform, une procédure pour remplir la ListBox à l'ouverture (Initialize donc).

Private Sub cbFerme_Click()
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    Dim TbF(), f%, i%, d
    d = Date
    With [Tableau1]
        For i = 1 To .Rows.Count
            If .Cells(i, 4).Value2 > d Then
                ReDim Preserve TbF(1, f)
                TbF(0, f) = .Cells(i, 1): TbF(1, f) = .Cells(i, 4).Text
                f = f + 1
            End If
        Next i
    End With
    If f > 1 Then
        lbxF.Column = TbF
    ElseIf f = 1 Then
        lbxF.AddItem TbF(0, 0)
        lbxF.List(0, 1) = TbF(1, 0)
    End If
End Sub

Le code fait apparaître quelques petits éléments non abordés dans les préalables... C'est que en l'écrivant il faut tenir compte de la façon dont la situation va se présenter et répondre à tous les cas dans le détail...

Pas de problème pour le parcours du tableau Excel et l'incrémentation du tableau à affecter, c'est conforme aux généralités envisagées au départ, à un détail près : le tableau constitué est à 2 colonnes et lignes variables (indices de base 0), on établit donc un tableau (colonnes, lignes) car on ne peut faire varier que la dernière dimension [le tableau sera donc à transposer lors de l'affectation].

On a mis la date du jour dans une variable (d) utilisée pour la comparaison ligne par ligne : Date est une fonction, l'accès à une variable sera plus rapide que le renvoi d'un fonction à chaque fois...

La comparaison s'effectue en utilisant la propriété Value2 de l'objet Range (c'était en principe inutile ici, les risques de conversion sauvage de dates par VBA ne devant pas se poser dans ce cas, mais cela permet d'indiquer que cette propriété renvoie la valeur sous laquelle la date est stockée dans Excel, soit numérique, qui ne risque plus alors d'être convertie à tort.

Et pour compléter les possibilités offertes par les propriétés de l'objet Range, on la prélève en utilisant la propriété Text, qui la renvoie telle qu'elle est affichée dans la cellule (propriété de nature à éviter des conversions pour affichage en prenant en compte directement une valeur formatée).

Une fois le prélèvement fait, il faut affecter le résultat ! Il faut tenir compte des éventualités : pas de résultat (il ne faudra pas affecter), un seul résultat (la propriété List de la ListBox ne peut être utilisée pour l'affectation), si plusieurs résultats on peut faire comme prévu.

f est la variable utilisée pour l'incrémentation, on l'a utilisée à partir de 0, en l'incrémentant après chaque ajout de ligne, pour la ligne suivante. Donc en fin de parcours, si f=0 c'est qu'il n'y a pas de tableau ! si f=1, c'est qu'on a 1 élément au tableau, d'indice 0, au-delà f indique le nombre d'élément...

Si on est dans le cas standard prévu (f>1) on affecte le tableau. On a dit qu'il faudrait le transposer... ce que l'on va faire sans le faire explicitement ! La ListBox dispose d'une propriété Column en quelque sorte symétrique de List, qui représente les mêmes données mais transposées ! Notre tableau transposé, on l'affecte au moyen de Column, et il apparaîtra redressé à l'affichage...

Si un seul élément on utilise alors la méthode AddItem, qui affecte la donnée de la 1re colonne et pour la colonne suivante on ajoute la donnée avec List(0, 1) [la numérotation des lignes et colonnes part de 0 dans une ListBox...]

Voilà pas à pas une méthode pour aborder la question puis construire le code correspondant... J'espère qu'elle te sera utile !

Cordialement.