Raisonnement filtrage multiple
Bonjour à tous,
J'ai pas mal fouillé sur differents forums, sans trouver ma réponse. Actuellement en stage dans le secteur financier je me remets a VBA depuis quelques jours. Je suis actuelement face à un soucis non pas de code, mais plus de logique/raisonnement.
Je vous explique la situation:
Je fais manuellement une extraction depuis BloomBerg vers un fichier Excel qui aura toujours le meme nom -> OK
Je fais un copier/coller via macro de cette feuille vers la feuille du classeur que j'utilise pour le reste de mon analyse -> OK
Je souhaite pouvoir supprimer les lignes qui ne repondent pas à plusieurs critères en meme temps par macro -> PAS OK
Analyser les données de façon automatique, produire un rapport et l'envoyer par email par macro -> OK
Mon extraction contient divers colonnes dont: la notation financière (AAA, AA+,...), le secteur d'activité, la localisation géographique, ...
J'ai mis en place un petit tableau où l'utilisateur (moi pour le moment) n'a qu'a cliquer sur des CheckBox.
Mon soucis est le suivant:
Je voudrais que si je selectionne à la fois les ratings AAA,BBB, ... et les secteurs X,Y ou Z et la geographie US, ME, EU... il me supprime les lignes qui ne vérifie pas les conditions.
Sauf que VBA a un raisonnement linéaire et qu'il considère une par une les conditions sans tenir compte des autres. Peut être que les CheckBox ne sont pas adaptées mais dans ce cas je ne vois pas quoi utiliser.
Pour Info voila comment je voyais mon code:
Dim ligne as Integer
Dim DernLigne as Integer
DernLigne = ...
If checkbox_AAA.value = false Then_
For ligne = 2 to DernLigne
If wk.sheet.cells(ligne,Rating)=1 Then_ 'Rating signifie la colonne des ratings au format chiffres, 1 correspond à AAA, 2 à AA+, etc)'
wk.sheet.cells(ligne,rating).row.delete
End if
Next ligne
End ifEt répéter ça pour chaque critère. Mais on comprend bien qu'une fois le critère "AAA" sera passé , il aura supprimé les lignes des autres ratings. Ce système fonctionne si on selectionne qu'une valeur par type de critère.
Bonne journée, a vos cerveaux
Bonjour,
Les Select Case permettent de vraiment imbriquer des conditions multiples surtout si chaque variable contient plusieurs cas et pour les If Then, il faut savoir manipuler les Or et And !
Voici un code avec des Select Case :
Sub Test()
Dim wk As Worksheet
Dim Rating As Integer
Dim Secteur As String
Dim Geographie As String
Dim ligne As Integer
Dim DernLigne As Integer
Set wk = ActiveSheet
DernLigne = wk.Cells(Rows.Count, 1).End(xlUp).Row
For ligne = DernLigne To 2 Step -1 '<--- pour la suppression de ligne, il faut commencer par la fin !
'adapter les numéros des colonnes
Rating = wk.Cells(ligne, 2).Value
Secteur = wk.Cells(ligne, 3).Value
Geographie = wk.Cells(ligne, 4).Value
Select Case Rating: Case 1, 3, 5 'si la valeur de la variable "Rating" correspond à une de ces trois valeurs, valide...
Select Case Secteur: Case "X", "Y", "Z" 'si le secteur correspond à de ces trois cas, valide...
Select Case Geographie: Case "US", "ME", "EU" 'et si Geographie correspond aussi à un de ces trois cas, valide..
'donc ici, suppression de la ligne puisque les trois cas sont validés !
wk.Cells(ligne, 1).EntireRow.Delete
End Select: End Select: End Select
Next ligne
End Subet un code avec If Then et des Or et And :
Sub Test()
Dim wk As Worksheet
Dim Rating As Integer
Dim Secteur As String
Dim Geographie As String
Dim ligne As Integer
Dim DernLigne As Integer
Set wk = ActiveSheet
DernLigne = wk.Cells(Rows.Count, 1).End(xlUp).Row
For ligne = DernLigne To 2 Step -1 '<--- pour la suppression de ligne, il faut commencer par la fin !
'adapter les numéros des colonnes
Rating = wk.Cells(ligne, 2).Value
Secteur = wk.Cells(ligne, 3).Value
Geographie = wk.Cells(ligne, 4).Value
If Rating = 1 Or Rating = 3 Or Rating = 5 And Secteur = "X" Or Secteur = "Y" Or Secteur = "Z" And Geographie = "US" Or Geographie = "ME" Or Geographie = "EU" Then
wk.Cells(ligne, 1).EntireRow.Delete
End If
Next ligne
End SubLes deux code donnant les mêmes résultats !
Hello Theze,
Tout d'abord merci ! je ne m'attendais pas à ce que l'on m'ecrive des lignes de code toute faites. Je ne connaissais pas l'existence des cases et ça me semble le plus lisible pour moi et léger pour le code (environ 90.000 lignes tout de même).
Petite question pour vraiment bien comprendre:
La macro va choisir la ligne, si les 3 "cases" sont validés, c'est génial ! Mais comment VBA comprends que je ne veux afficher que les ratings 1,3,5 aujourd'hui et 4,7,9 demain ? Idem pour les géo, secteurs
En tout cas merci tu viens en 30sec de résoudre 2h de reflexion sur papier pour moi ^^
Re,
Il y a plusieurs façons de faire, tu peux par exemple utiliser un tableau de 3 x 3 où tu mets dans la première ligne les Ratings, la seconde les secteurs et la troisième la géographie. Ici, c'est en "dur" dans le code mais tu pourrais utiliser des cellules dans une feuille par exemple :
Sub Test()
Dim wk As Worksheet
Dim TblCas()
Dim Rating As Integer
Dim Secteur As String
Dim Geographie As String
Dim ligne As Integer
Dim DernLigne As Integer
Set wk = ActiveSheet
'crée un tableau de 3 x 3...
ReDim TblCas(1 To 3, 1 To 3)
'où la 1 ère ligne contient les Ratings...
TblCas(1, 1) = 1
TblCas(1, 2) = 2
TblCas(1, 3) = 3
'la seconde les secteurs...
TblCas(2, 1) = "X"
TblCas(2, 2) = "Y"
TblCas(2, 3) = "Z"
'et la troisième la géographie
TblCas(3, 1) = "US"
TblCas(3, 2) = "ME"
TblCas(3, 3) = "EU"
DernLigne = wk.Cells(Rows.Count, 1).End(xlUp).Row
For ligne = DernLigne To 2 Step -1 '<--- pour la suppression de ligne, il faut commencer par la fin !
'adapter les numéros des colonnes
Rating = wk.Cells(ligne, 2).Value
Secteur = wk.Cells(ligne, 3).Value
Geographie = wk.Cells(ligne, 4).Value
Select Case Rating: Case TblCas(1, 1), TblCas(1, 2), TblCas(1, 3) 'si la valeur de la variable "Rating" correspond à une de ces trois valeurs, valide...
Select Case Secteur: Case TblCas(2, 1), TblCas(2, 2), TblCas(2, 3) 'si le secteur correspond à de ces trois cas, valide...
Select Case Geographie: Case TblCas(3, 1), TblCas(3, 2), TblCas(3, 3) 'et si Geographie correspond aussi à un de ces trois cas, valide..
'donc ici, suppression de la ligne puisque les trois cas sont validés !
wk.Cells(ligne, 1).EntireRow.Delete
End Select: End Select: End Select
Next ligne
End SubJe vois ce que tu fais et le comprends parfaitement. Cela fonctionne, mais je n'ai pas le choix. Sauf celui d'écrire dans le tableau ce que je recherche.
Je pense que je peux faire un tableau ou en face de chaque nom, j'écris 0 ou 1 en fonction de si je souhaite le prendre ou pas. Mais on va pas se mentir c'est assez moche et vu q'uà terme je ne serais pas le seul a utilise j'aimerai faire un truc de suite niquel.
Un truc du genre:
If checkbox_AAA=true then Range("cellule masquée a droite de AAA")=1
Else Range("cellule masquée a droite de AAA")=0
'Idem pour tous les ratings, géo, secteur'
'ton bout de code'
For ligne = DernLigne To 2 Step -1 '<--- pour la suppression de ligne, il faut commencer par la fin !
'adapter les numéros des colonnes
Rating = wk.Cells(ligne, 2).Value
Secteur = wk.Cells(ligne, 3).Value
Geographie = wk.Cells(ligne, 4).Value
Select Case Rating for j=1 to 3 step 1 _
If cells(ligne_petit_tableau,colonne)=1 Je pense que ça fonctionnera dans l'idée. Peut être vais-je avoir un soucis sur la fin pour selectionner les données.
Merci d'avoir pris le temps en tout cas
Re,
Bon j'ai regardé de plus près, et en fait ça ne fonctionne pas comme je le souhaite.
Première question: J'ai l'impression que les checkbox ne sont que des "outputs" du genre si cellule="X" alors coche la case. Je souhaite faire l'inverse et dire: Si coché alors prends le en compte dans ta selection. J'ai rename le bouton/check box par AAA dans propriété puis j'ai testé ce code maison à la fois en "dim checkbox" et "dim optionbutton". Dans les deux cas, il considère false que ça soit coché ou non.
Dim wk_selection As Worksheet
Dim AAA As OptionButton
Set wk_selection = Sheets("Selection")
If AAA = True Then wk_selection.Range("B2").Value = 1 _
Else: wk_selection.Range("B2").Value = 0Seconde question: j'ai dans un même classeur, une feuille avec mes cases à cocher et une autre avec mes données. Comment puis-je faire comprendre à VBA qu'il doit supprimer les données dans le classeur "données" sur la base des critères de la feuille "séléction" si les cellules "B" sont égales à 0.
En vous remerciant.
Re,
A priori, le choix peut être multiple et dans les trois types donc, je pense que pour les utilisateurs un UserForm serait plus confortable pour la sélection des valeurs à exclurent des les trois types. J'ai vite fais un fichier test afin de mieux imager ce que je veux dire. Il comporte trois ListBox à choix multiples avec des cases à cocher. Testes et reviens donner ton ressenti. Attention tout de même, pour pouvoir prendre en compte tous les choix possible, il faut faire une multitude de boucles et selon le nombre de lignes dans la feuille, ça risque de prendre un peu de temps et dans ce cas, il serait judicieux de passer par un tableau :
Thèze sur tous les fronts !
J'utilise jamais de userform (a tord surement) car je trouve ça peu lisible côté code, mais en effet c'est "joli" à l'utilisation.
Alors a priori ça correspond à 200% a ce que je veux faire à condition que je puisse récupérer la selection pour reprendre ton code en select case.
Ayant un truc plus urgent à finir ce soir, et ne pouvant prendre le fichier à la maison. Je regarde ça demain. Au besoin je relancerai le sujet et une fois finie, je mettrais [résolu].
Thanks my friend
Re,
A priori, le choix peut être multiple et dans les trois types donc, je pense que pour les utilisateurs un UserForm serait plus confortable pour la sélection des valeurs à exclurent des les trois types. J'ai vite fais un fichier test afin de mieux imager ce que je veux dire. Il comporte trois ListBox à choix multiples avec des cases à cocher. Testes et reviens donner ton ressenti. Attention tout de même, pour pouvoir prendre en compte tous les choix possible, il faut faire une multitude de boucles et selon le nombre de lignes dans la feuille, ça risque de prendre un peu de temps et dans ce cas, il serait judicieux de passer par un tableau :Test Rating.xlsm
J'ai quand meme pris le temps de jeter un petit coup d'oeil.
Tu sais me dire pourquoi pourquoi je n'arrive pas a avoir le userform sur mon fichier, bien que j'ai copié/coller ton code (avant de l'adapter evidement)
A la lecture rapide du code je pense que je peux travailler sans soucis sur deux feuilles differentes. Juste a preciser la worksheet sur mes objets.
Mais le point le plus crucial, on selectionne des données. Si elle répondent à ces critères alors on supprime. Mais moi il me faudrait l'inverse les conserver. Et on ne peut pas cliquer dans l'autre sens en disant je choisis ceux que je supprime.
Bonjour,
Tu peux aussi utiliser le filtre élaboré d'Excel dont voici un classeur exemple et le code :
Sub Filtrage()
Dim Plage As Range
Dim Cel As Range
Set Plage = DefPlage(ActiveSheet, 7, 1)
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
Plage.AdvancedFilter 2, Range("A2:C5"), Range("F2")
End Sub
Function DefPlage(Fe As Worksheet, Optional L As Long = 1, Optional C As Long = 1) As Range
On Error GoTo Fin
With Fe
Set DefPlage = .Range(.Cells(L, C), _
.Cells(.Cells.Find("*", .[A1], -4123, , _
1, 2).Row, .Cells.Find("*", .[A1], -4123, , _
2, 2).Column))
End With
Exit Function
Fin:
Set DefPlage = Nothing
End FunctionD'après l'aide Excel, mon besoin est un controle de formulaire, de type case à cocher.
Je suis donc tombé la dessus: https://forum.excel-pratique.com/viewtopic.php?t=5833&start=10&postdays=0&postorder=asc&highlight= Ou https://support.office.com/fr-fr/article/ins%C3%A9rer-une-zone-de-liste-%C3%A0-s%C3%A9lection-multiple-ee579158-e894-4c9b-bce8-6a579277b7ae Oui je cherche parfois aussi
Mais je galère à trouver comment adapter tout ses codes car je les maitrise mal, et que j'ai deux feuilles
Je me suis permis de te mettre un fichier type avec des vieilles données. Mais je pense vraiment que les cases à cocher c'est le must
___________________________________________ _________________________________________ ________________________________
EDIT: En gros l'idéal ça serait une cache à cocher devant chaque celulle de la feuille selection et utiliser la selection =False plutot que =True pour la suppression.
J'ai un peu de mal a appelé la checkbox dans mes macros malgré que dans propriétés je les ai renommé.
Il faudra donc un truc du genre:
Définir rating, géo et secteur en fonction de i=ligne comme tu l'avais proposé.
Pour chaque ligne de la feuille "donnée"
If checkboxAAA= False Then _
If sheets("donnée").cells(i,Numero de colonne rating).value= "AAA" ou "1" Then _
sheets("donnée").cells(i,1).entiererow.delete
If checkboxAA_plus=False Then ...En répetant ce mini bout de code pour l'ensemble des critères on va successivement supprimer ceux que l'on a pas selectionné non ?
@Thèze si tu passes par là, peux tu me confirmer que cette dernière solution est viable ? Et juste m'expliquer comment fonctionne les checkbox nommées voire faire juste un critère en code, je ferais les autres sur le même model. Mille merci
Problème concernant ce sujet résolu ! Par rapport au fichier joint plus haut, j'ai associé des "cases à cocher" a une cellule a droite. de chaque catégorie qui indique vrai ou faux. Vrai ou Faux étant repris dans le code au niveau de la cellule pour eviter de faire appel à la case à cocher. Pour info voila le code:
Sub Selecion_données()
Dim wk As Workbook
Dim wk_selection As Worksheet
Dim wk_donnée As Worksheet
Dim i As Integer
Dim ligne As Integer
Dim DernLigne As Integer
Dim Rating As String
Dim Secteur As String
Dim Geographie As String
Set wk = ThisWorkbook
wk.Activate
Set wk_selection = Sheets("Selection")
Set wk_donnée = Sheets("Données Brutes")
DernLigne = wk_donnée.Cells(Rows.Count, 1).End(xlUp).Row
'Filtrage par suppression des données non selectionnées, catégorie Rating'
For i = 2 To 23 Step 1
If wk_selection.Cells(i, 2).Value = False Then
For ligne = DernLigne To 2 Step -1
Rating = wk_donnée.Cells(ligne, 9).Value
If Rating = wk_selection.Cells(i, 1).Value Then wk_donnée.Cells(ligne, 1).EntireRow.Delete
Next ligne
End If
Next i
'Filtrage par suppression des données non selectionnées, catégorie Sector'
For i = 2 To 23 Step 1
If wk_selection.Cells(i, 4).Value = False Then
For ligne = DernLigne To 2 Step -1
Secteur = wk_donnée.Cells(ligne, 3).Value
If Rating = wk_selection.Cells(i, 3).Value Then wk_donnée.Cells(ligne, 12).entiereRow.Delete 'attention noté 12'
Next ligne
End If
Next i
'Filtrage par suppression des données non selectionnées, catégorie Geographic'
For i = 2 To 23 Step 1
If wk_selection.Cells(i, 6).Value = False Then
For ligne = DernLigne To 2 Step -1
Geographie = wk_donnée.Cells(ligne, 4).Value
If Rating = wk_selection.Cells(i, 5).Value Then wk_donnée.Cells(ligne, 12).entiereRow.Delete 'attention noté 12'
Next ligne
End If
Next i
End SubRe,
Tu n'est pas obligé d'utiliser des cases à cocher, tu peux utiliser une colonne supplémentaire par type et définir la fonte en "Wingdings 2", le P représente une coche ce qui est plus léger qu'une multitude de cases à cocher, il suffit alors d'utiliser les événements "SelectionChange()" et "BeforeDoubleClick()". Je te reposte ton fichier pour que tu puisses tester :
Hello, encore merci de ton aide,
Je sais pas si c'est quelquechose de plus evident pour mes collègues. J'ai fait tourner le code ce matin de la macro et ça a fonctionné parfaitement. Je réessaye ensuite et là:
Si je fais tourner en Pas à Pas, je n'ai pas de soucis sur la boucle IF, mais si je lance normalement j'ai une erreur d'execution de type '13' (incompadibilité de type) lorsque la condition
Rating = wk_donnée.Cells(ligne, 9).Valuedans le bloc
For i = 2 To 23 Step 1
If wk_selection.Cells(i, 2).Value = False Then
For ligne = DernLigne To 2 Step -1
Rating = wk_donnée.Cells(ligne, 9).Value
If Rating = wk_selection.Cells(i, 1).Value Then wk_donnée.Cells(ligne, 1).EntireRow.Delete
Next ligne
End If
Next iRéponse du site microsoft:
"Ce message apparaît pour 2 raisons :
1. Vous passez "effectivement" le mauvais type d'argument.
Dans ce cas, passer le type d'argument requis.
2. Vous tentez de modifier une propriété uniquement accessible en
lecture.
Dans ce cas, il n'y a aucun moyen de modifier une propriété en
lecture seule."
Alors là je sèche ..
EDIT: j'ai trouvé, putin c'est d'un con le système binaire x) j'avais une ligne sur la feuille donnée dont la rechercheV etait foireuse et donc j'avais #N/A...