Poblème avec EVALUATE
Bonjour à tous,
J'ai une formule qui fonctionne bien sur une feuille =INDEX(FILTRE(Tableau1;Tableau1[Validé]=FAUX);SEQUENCE(NB.SI.ENS(Tableau1[Validé];FAUX));{3.9.11.12.18.19.20.21.22})
elle retourne les colonnes [3.9.11.12.18.19.20.21.22] et les lignes qui correspondent au critère de recherche FAUX dans la colonne [Validé ], Jusque là tout va bien, par contre je n'arrive pas à récupérer cette formule sous VBA.
En fait je voudrais récupérer une plage et l'assigner, à la propriété .List d'une ListBox
ce que j'ai tenté, faut dire que Evaluate et moi on est pas très bon copain...
' // Test avec une plage
Dim rRange As Range
Set rRange = Evaluate("INDEX(FILTRE(Tableau1;Tableau1[Validé]=FAUX);SEQUENCE(NB.SI.ENS(Tableau1[Validé];FAUX));{3.9.11.12.18.19.20.21.22})")
ListBox1.List = rRange.value ' // Pas bon message d'erreur Object requisJ'ai testé avec un Tableau
' // Test avec un tableau
Dim Tab1 as Variant
Tab1 = Evaluate("INDEX(FILTRE(Tableau1;Tableau1[Validé]=FAUX);SEQUENCE(NB.SI.ENS(Tableau1[Validé];FAUX));{3.9.11.12.18.19.20.21.22})")
ListBox1.List = Tab1 ' //Pas bonSi quelqu'un à une idée .....
Bonsoir,
pouvez vous essayer avec cette version de formule :
=INDEX(FILTER(Tableau1,Tableau1[Validé]=FALSE),SEQUENCE(COUNTIFS(Tableau1[Validé],FALSE)),{3,9,11,12,18,19,20,21,22})
Sous VBA ne faut il pas que ce soit en anglais ? Avec EVALUATE certainement pas, je ne sais pas...
Voir créer une variable formule : Formule as string, puis :
Formule ="=INDEX(FILTER(Tableau1,Tableau1[Validé]=FALSE),SEQUENCE(COUNTIFS(Tableau1[Validé],FALSE)),{3,9,11,12,18,19,20,21,22})"
puis = EVALUATE(formule)
Ou tout simplement je crois qu'il faut rajouter le "=" à votre texte de formule... Non ?
@ bientôt
LouReeD
Salut,
Bon déjà je suis une truffe, de ne pas avoir vu le français dans la formule merci pour ça.
Si je mets des Debug.Print je me retrouve avec ça:
Function GetListeContrats()
Dim rRange
rRange = Evaluate("INDEX(FILTER(tTableau,tTableau[Validé]=FALSE),SEQUENCE(COUNTIFS(tTableau[Validé],FALSE)),{3,9,11,12,18,19,20,21,22})")
GetListeContrats = rRange
Debug.Print "TypeNname rRange : "; TypeName(rRange)
Debug.Print "Value rRange : "; rRange
End Function
En fait il ne retourne que la première cellule de la plage, ci-dessous le retour de la formule sur la feuille
ps: Le tableau ne porte pas le même nom c'est un fait, et cela à été modifié dans le programme
Bonjour,
essayez en déclarant rRange comme étant un tableau range Dim rRange() As Range
@ bientôt
LouReeD
Bonjour,
Une proposition à étudier.
Cdlt.
lien utile :
Nouvelle fonctionnalités VBA
Remplacer False par Faux et True par Vrai
Salut à tous
Déjà merci de vous pencher sur le sujet.
@Jean-Eric ta solution fonctionne nickel...
Mais car il y a un mais. Fonctionne sur 365, je sais j'ai 365, mais pas tout le monde, donc pour la portabilité c'est pas bon.
@LouReed J'ai déjà tenté plein de manipulations
- rRange en tableau "Erreur incompatibilité de type" évidemment Evaluate renvoie un string
- "Faux" en remplacement de "False" et "," en remplacement du "." : Erreur 2015
- "Faux" en remplacement de "False" et "." en remplacement de la "," : Erreur 2029
- Avec Fonction Matricielle rRange = Evaluate("{INDEX(FILTER.........,20,21,22})}") String et première cellule encore
Je vais quand même approfondir mes connaissances sur les nouvelles fonctions que je trouve très intéressantes.
Pour l'instant j'entends ma moto qui m'appelle, il fait beau donc je remet tout cela à plus tard...
Re,
C'est une question qu'il fallait se poser dès l'utilisation des fonctions Filtre et Sequence ?
Cdlt.
@LouReeD,
On apprivoise 365 ?
Tu dois revoir toutes tes applications avec les nouvelles fonctionnalités de 365.
@Jean-Eric tu as entièrement raison, J'avoue avoir péché par trop de recherches sur Evaluate et de m'y être perdu.
Donc pour en revenir aux fondamentaux, je recherche une fonction avec Evaluate sans passer par un AddItem ça je sais faire. qui renvoie un tableau de plusieurs colonnes et de une ou plusieurs lignes qui auraient une de leur colonne à False, Et ce sans passer par un calcul intermédiaire comme ce qui à été fais dans l'exemple de Jean-Eric (Qui fonctionne très bien mais qui n'est pas adapter à tous les Excel).
Si quelqu'un veux tenter de trouver, Je vais de mon coté approfondir le sujet...
Jean-Eric : et bien non je n'apprivoise pas 365... Je fais avec ou plutôt sans 2019 !
Sans parler de nouvelle versions, ne serait ce par mon évolution de connaissance dans VBA, en effet, je devrais reprendre mes applications !
Mais bon, j'ai déjà du mal à finir celles en cours...
@ bientôt
LouReeD
Bonsoir,
Jean-Paul ci dessous un code qui utilise Find afin de collecter des lignes d'une plage de données, cette dernière étant récupérée dans un tableau sous VBA afin d'augmenter la vitesse d'exécution :
Sub LRD()
Dim TabRésultat(), Cpt
TabDonnées = Range("A1:K10")
ColExtract = Array(1, 5, 11, 8) ' ici un tableau pour les colonnes à extraire avec possibilité de modifier l'ordre des colonnes
For i = 2 To UBound(TabDonnées)
' la condition recherchée
If TabDonnées(i, 5) = "d_500000" Then
ReDim Preserve TabRésultat(Cpt)
TabRésultat(Cpt) = WorksheetFunction.Index(TabDonnées, i, ColExtract) ' 0= ligne entière ou alors un Array avec l'ordre des colonnes
Cpt = Cpt + 1
End If
Next i
On Error Resume Next
Range("A18").Resize(UBound(TabRésultat, 1) + 1, UBound(ColExtract) + 1).Value = Application.Transpose(Application.Transpose(TabRésultat))
If Err > 0 Then Range("A18") = TabRésultat
On Error GoTo 0
End SubTabDonnées récupère votre plage de données.
ColExtract est (si vous voulez) un tableau d'extraction des valeurs des colonnes voulues et dans l'ordre voulu.
la boucle For To Next permet de scanner le tableau VBA.
Le test If ici permet de faire un test de valeur sur une colonne choisie, mais il est bien évident que ce test peut être "multiplié" avec des And et autre colonne.
TabRésultat est le tableau dans lequel on va stocker les données des colonnes ColExtract correspondant au "match" des conditions.
Pour remplir ce tableau on utilise Find() sur le tableau de données VBA en ligne i de la boucle For To Next et en troisième paramètre soit 0 pour récupérer tel quelle la ligne entière du tableau de données VBA, soit avec le paramètre ColExtract afin de récupérer une partie des données et dans l'ordre choisi.
Une fois le scan effectué, on se trouve avec soit un tableau avec plusieurs données et c'est la ligne avec une double transpose qui fonctionne, soit il n'y a qu'une ligne en mémoire et du coup la recherche d'erreur fait qu'on affiche le tableau d'une ligne simplement.
Le fichier représentatif du code :
Je rassure tout le monde, ce n'est pas de moi, c'est de la part du MFerrand ! Ci dessous une copie de son message à l'intention de Curulis :
Salut Curulis,
En effet, cela a de multiples applications, et je n'en ai pas encore fait complètement le tour... :wink:
Je n'ai rien inventé, j'avais trouvé une allusion en circulant, il y a pas mal de temps, trouvé l'idée intéressante, fait quelques essais, infructueux... et je n'avais pas poursuivi. Jusqu'à il y a environ un an où un demandeur s'essayait à opérer ainsi sans y parvenir à partir d'un tuto qu'il avait trouvé...
Le tuto dont je n'ai pas gardé les références étant relativement plus complet, j'ai pu trouver les points où je faisais fausse route...
La méthode décrite consistait en :
- prélèvement en tableau de la plage source (affectation d'une plage à une variable Variant qui produit un tableau VBA à 2 dimensions,
- parcours de ce tableau pour repérer les lignes répondant aux conditions cherchées, et prélèvement de ces lignes entières en utilisant INDEX, pour les affecter à un tableau unidimensionnel incrémenté au fur et à mesure,
- affectation du tableau à une plage par double transposition.
Adaptation si tu prèlèves directement à partir de la plage source sans passer par un tableau, pas besoin d'utiliser INDEX, le prélèvement fonctionne de la même façon que l'affectation d'une plage à une variable Variant.
Mais dans le cas général de parcours d'une plage, passer par l'intermédiaire d'un tableau présente des avantages car plus rapide, et la fonction INDEX offre le cas échéant des possibilités supplémentaires.
Supposons que tu affectes une plage dont tu dois extraire des lignes sous condition à une variable aa. Tu parcours le tableau produit.
For i = 1 To UBound(aa) 'ou à partir de 2 si tu as une en-tête
If aa(i, x) = Condition Then 'x=colonne testée pour la condition
Redim Preserve Tablo(n) 'Tablo=tableau dynamique de résultats à 1 dimension, n variable d'incrémentation
Tablo(n) = WorksheetFunction.Index(aa, i, 0) 'Index renvoie la matrice ligne entière en mettant l'index colonne à 0
n = n + 1
End If
Next i
Possibilités supplémentaires offertes par Index : supposons que tu doives prélever sur la ligne seulement les colonnes (au hasard !) : 1, 3, 9, 5 dans cet ordre :
Tablo(n) = WorksheetFunction.Index(aa, i, Array(1, 3, 9, 5))
va te renvoyer exactement la ligne que tu veux obtenir comme résultat.
Bonne journée à toi.
Une fois le tableau copié sur une feuille, il est facile de le mettre en list pour votre USF, voir il n'est peut-être pas utile de passer par une feuille...
@ bientôt
LouReeD
Bonsoir…
Un exemple avec une de mes études pour alimenter un contrôle Listbox d’un formulaire avec certaines colonnes d’un Tableau filtré (office 2016).
Salut a tous,
LouReed, Ordonc merci pour votre implication, Pour l'heure mon cerveau fume et j'ai besoin d'un petit break, donc il fait beau dans ma région je vais donc m'aérer les neurones et je reviens vers vous dés ma balade terminée.
Je ne laisse pas tombé pour autant l'utilisation de Evaluate dans diverses formules.
Et bien le bonjour en cette belle journée !
@ bientôt
LouReeD
Salut à tous,
Bon voilà suis de retour, LouReeD, Ordonc, j'ai testé vos codes cela fonctionne très bien mais ce n'est pas ce que je recherche. En effet nous passons à chaque fois par des lignes sur une plage, ce que j'aimerais c'est de passer directement avec la Propriété List de la ListBox.
J'ai du louper quelque chose.
Bonsoir,
c'est ce que j'ai dis dans mon message : Une fois le tableau copié sur une feuille, il est facile de le mettre en liste pour votre USF, voir il n'est peut-être pas utile de passer par une feuille...
Oui on peut très bien remplir une ListeBox avec un tableau en "mémoire" VBA.
Reste à voir comment !
@ bientôt
LouReeD
Bonjour,
ci joint un fichier test avec une seule condition pour l'extraction mais le fait d'en rajouter n'est pas compliqué.
Une fois le tableau d'extraction créé, il est intégré à la ListeBox et tout ceci en VBA sans accès feuille :
@ bientôt
LouReeD
Salut à tous,
@LouReeD, C'est un bon début je dois creuser, deux options que je dois rectifier si une seule donnée est trouvée alors il y a un bug sur l'affichage dans la listbox. voir avec par exemple If TabDonnées(i, 1) = "d_10" Then ... en second lieu si pas de données là ça plante tout simplement.
Je vais peaufiner et mettre cela dans ma boite à outils, merci bien
Bonsoir,
le premier code fourni "à l'origine" gérait le problème de la seule donnée avec un on error resume next, après pour un tableau vide il faut tester si le tableau est vide ou pas avant l'exécution du code.
@ bientôt
LouReeD
Bonsoir,
Voici le code modifié et customisé !
Sub LRD()
Dim TabDonnées, TabRésultat(), Cpt, TR As Range, ColExtract, I, Ligne
TabDonnées = Range("A1:K10")
' si ligne = -1 alors c'est ColExtract qui est pris en compte, sinon c'est ligne
' si ligne = 0 alors extraction de toutes les colonnes sans mélange
Ligne = -1
ColExtract = Array(1, 5, 11, 8)
' Scan pour récupérer les données en fonction d'un test (ou plus) sur une ou plusieurs colonnes
For I = 2 To UBound(TabDonnées)
' la condition recherchée
If TabDonnées(I, 1) = "d_10" Then
' on agrandi le tableau résultat
ReDim Preserve TabRésultat(Cpt)
' on y met les résultats trouvés
TabRésultat(Cpt) = WorksheetFunction.Index(TabDonnées, I, IIf(Ligne = -1, ColExtract, Ligne))
Cpt = Cpt + 1
End If
Next I
' remplissage de la listbox
With ListBox1
' on efface la liste
.Clear
' on crée le nombre de colonne du tableau d'extraction
If Ligne = 0 Then ' si l'extraction cible la ligne entière
.ColumnCount = UBound(TabDonnées, 2)
Else ' sinon on prend le nombre de colonne d'extraction
.ColumnCount = UBound(ColExtract) + 1
End If
' gestion des erreurs
On Error Resume Next
' si plus d'un résultat
If UBound(TabRésultat) > 0 Then
.List = Application.Transpose(Application.Transpose(TabRésultat))
' si un sel résultat
ElseIf UBound(TabRésultat) = 0 Then
' on crée une ligne identique à celle existante
ReDim Preserve TabRésultat(Cpt)
TabRésultat(Cpt) = TabRésultat(Cpt - 1)
' on colle les deux lignes
.List = Application.Transpose(Application.Transpose(TabRésultat))
' on sélectionne la dernière ligne (qui est en double donc)
.ListIndex = .ListCount - 1
' on la supprime
.RemoveItem (.ListIndex)
' on déselectionne la restante
.ListIndex = -1
End If
' si le compte de données de TabRésultat provoque une erreur alors c'est qu'il n'y a pas de résultat
If Err > 0 Then MsgBox ("Pas de résultat")
On Error GoTo 0
End With
End SubLa variable "Ligne" fait son apparition, le nombre de colonne de la ListBox est fonction de cette variable, de la taille du tableau source et/ou de ColExtract.
Une gestion d'erreur afin de détecter un résultat "nul" et mise en place de la gestion du résultat unique : solution choisie (la seule que j'ai trouvée) est de créer une deuxième ligne identique, de remplir la listbox avec ces deux lignes de données identiques et ensuite supprimer la deuxième.
Le fichier :
@ bientôt
LouReeD
Bonjour,
La proposition de LouReeD revistée avec les données sous forme de tableau (structuré).
Pour le fun !
Bon weekend.
Cdlt.
Sub LRD_2()
Dim lo As ListObject
Const X As String = "d_500000" '"d_"
Dim colExtract As Variant
Dim tblData As Variant, arrList() As Variant
Dim rw As Long, lCounter As Long
Set lo = Range("t_données").ListObject
If lo.InsertRowRange Is Nothing Then
If WorksheetFunction.CountIf(lo.ListColumns(5).DataBodyRange, X) > 0 Then
tblData = lo.DataBodyRange
colExtract = Array(1, 5, 11, 8)
For rw = LBound(tblData) To UBound(tblData)
If tblData(rw, 5) = X Then
ReDim Preserve arrList(lCounter)
arrList(lCounter) = WorksheetFunction.Index(tblData, rw, colExtract)
lCounter = lCounter + 1
End If
Next rw
With ListBox1
'.Clear
.ColumnCount = UBound(colExtract) + 1
Debug.Print UBound(colExtract)
If lCounter > 0 Then .List = Application.Transpose(Application.Transpose(arrList))
End With
Else
MsgBox "pas de résultat !...", 64, "information"
End If
End If
End Sub