trouver les villes absentes et afficher en msgbox

[gosselien]

Bonjour à tous,

peu habitué aux fonctions et array, je demande un peu d'aide avec un fichier annexé:

A gauche la liste des TOUTES les communes d'une certaine province; à droite la liste des communes ayant participé à une course à pied.

(Il y a normalent à gauche 281 communes et 7 provinces je n'ai mis que 2 provinces ici).

Je dois trouver quelle commune dans la liste de droite ne fait pas partie de la liste de gauche MAIS j'aurais voulu que toutes ces villes/provinces de gauche soient dans le code VBA et pas dans un fichier XL mais je ne sais pas comment faire. (Array avec les communes et provinces associées ?)

Je cherche donc une FONCTION où j'entrerais , la zone sélectionnée (en gras et italique dans mon exemple)avec la souris, la prince supposée (H-E ou BRW) et le résultat dans un msgbox
ex: =trouveville($A$2:$A$36,"H-E")
réponse:
msgbox(CHATELET, FARCIENNES) ce sont les 2 communes absentes de la liste de droite

Si je sélectionne la même liste A2:A36 mais que je mets "BXL" à la place de "H-E" , je dois avoir un message d'erreur bien sûr ...
msgbox("Les communes sélectionnées ne sont pas dans cette province !")

Une commune ne peut se trouver que dans une seule province

J'espère avoir été clair
Merci

 

[Robert]

Bonsoir Gosselien, bonsoir le forum,

En pièce jointe ton fichier modifié. Attention si tu dois recopier le code VBA dans ton fichier d'origine, il se trouve dans les trois composants suivants :

• Feuil1(Feuil1)

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'au changement de celllue active dans l'onglet
Set DF = Range("XFD1") 'définit la variablr DF (déclarée publique dans le module [Module1]
If Target.Address <> "$E$5" Then Exit Sub 'si la cellule sélectionnée n'est pas E5, sort de la procédure
If DF.Value = "oui" Then Exit Sub 'si la valeur de DF est "oui"'sort de la procédure
Module1.Macro1 'lance la procédure [Macro1] du module [Module1]
End Sub

Private Sub Worksheet_Change(ByVal Target As Range) 'au changement dans l'onglet
If Target.Address <> "$E$5" Then Exit Sub 'si le changement a lieu ailleurs que dans E5, sort de la procédure
Module1.Macro2 'lance la procédure [Macro2] du module [Module1]
End Sub

• ThisWorkbook

Private Sub Workbook_Open() 'à l'ouverture du classeur
Module1.Macro1 'lance la procédure [Macro1] du module [Module1]
End Sub

• Module1

Public DF As Range 'déclare la variable DF (Déjà Fait)
Public O As Worksheet 'déclare la variable O (Onglet)
Public TTV As Variant 'déclare la variable TTV (Tableau de Toutes les Villes)

Sub Macro1()
Dim I As Integer 'déclare la variable I (Incrément)
Dim D As Object 'déclare la variable D (Dinctionnaire)
Dim L As String 'déclare la variable L (Liste)
Dim TMP As Variant 'déclare la variable TMP (TeMPoraire)

Set O = Worksheets("Feuil1") 'définit l'onglet O
Set DF = O.Range("XFD1") 'définit la variale DF
TTV = O.Range("A1").CurrentRegion 'définit le tableau de toutes les villes TTV
Set D = CreateObject("Scripting.Dictionary") 'définit le dictionnaire D
For I = 2 To UBound(TTV, 1) 'boucle sur toutes les lignes I de TTV (en partant de la seconde)
  D(TTV(I, 2)) = "" 'alimente le dictionnaire D avec les données en colonne 2 de TTV
Next I 'prochaine ligne de la boucle
TMP = D.keys 'récupère dans le tableau temporaire TMP la liste des éléments du dictionnaire D sans doublon
L = "Toutes," & Join(TMP, ",") 'définit la liste L
With Range("E5").Validation 'prend en compte la validation de données de la cellule E5
  .Delete 'efface un éventuelle ancienne validation de données
  .Add xlValidateList, Formula1:=L 'définit la liste L comme liste de validation de donnée
End With 'fin de la prise en compte de la validation de données de la cellule E5
DF.Value = "oui" 'définit la valeur de DF
End Sub

Sub Macro2()
Dim DL As Integer 'déclare la variable DL (Dernière Ligne)
Dim TVP As Variant 'déclare la variable TVP (Tableau des Villes ayant Participé)
Dim D As Object 'déclare la variable D (Dictionnaire)
Dim I As Integer 'déclare la variable I (Incrément)
Dim TEST As Boolean 'déclare la variable TEST
Dim J As Integer 'déclare la variable J (incrément)
Dim MSG As String 'déclare la variable MSG (MeSsaGe)

Set O = Worksheets("Feuil1") 'définit l'onglet O
TTV = O.Range("A1").CurrentRegion 'définit le tableau de toutes les villes TTV
DL = Range("J" & Application.Rows.Count).End(xlUp).Row 'définit la dernière ligne éditée DL de la colonne J
TVP = O.Range("J1:J" & DL) 'définit le tableau des villes ayant participé TVP
Set D = CreateObject("Scripting.Dictionary") 'définit le dictionnaire D
Select Case Range("E5").Value 'agit en fonction de la valeur de la cellule E5
    Case "Tous" 'cas "Tous"
        For I = 2 To UBound(TTV, 1) 'boucle 1 : sur toutes les lignes I de TTV (en partant de la seconde)
            TEST = False 'initialise la variable TEST
            For J = 2 To UBound(TVP, 1) 'boucle 2 sur toutes les lignes J de TVP (en partant de la seconde)
                If TTV(I, 1) = TVP(J, 1) Then 'Consition si...
                    TEST = True 'redéfinit la variable TEST
                    Exit For 'redéfinit la variable TEST
                End If 'redéfinit la variable TEST
            Next J 'redéfinit la variable TEST
            'si TEST est [Faux], définit le message MSG (ajoute la ville TTV(I,I) au message)
            If TEST = False Then MSG = IIf(MSG = "", TTV(I, 1) & vbCr, MSG & TTV(I, 1) & vbCr)
        Next I 'prochaine ligne de la boucle 1
    Case Else 'les autres cas
        For I = 2 To UBound(TTV, 1) 'boucle 1 : sur toutes les lignes I de TTV (en partant de la seconde)
            'si TV(I,2 ) est égale à la valeur de E5, alimente le dictionnaire D avec les données en colonne 1 de TTV
            If TTV(I, 2) = Range("E5").Value Then D(TTV(I, 1)) = ""
        Next I 'prochaine liigne de la boucle
        TMP = D.keys 'récupère dans le tableau temporaire TMP la liste des éléments du dictionnaire D sans doublon
        For I = 0 To UBound(TMP) 'boucle sur tous les élément du tableau TMP
            TEST = False 'initialise la variable TEST
            For J = 2 To UBound(TVP, 1) 'boucle 2 sur toutes les lignes J de TVP (en partant de la seconde)
                If TMP(I) = TVP(J, 1) Then 'condition : si TMP(I) est égale à TVP(J,1)
                    TEST = True 'redéfinit la variable TEST
                    Exit For 'redéfinit la variable TEST
                End If 'redéfinit la variable TEST
            Next J 'redéfinit la variable TEST
            'si TEST est [Faux], définit le message MSG (ajoute la ville TMP(I) au message)
            If TEST = False Then MSG = IIf(MSG = "", TMP(I) & vbCr, MSG & TMP(I) & vbCr)
        Next I 'prochaine ligne de la boucle 1
End Select 'fin de l'action en fonction de ...
MsgBox MSG 'affiche le message MSG
End Sub

Le Code va créer une liste de validation de données automatique en E5, contenant la liste des différentes provinces.
Sélectionne une province dans E5 et une MsgBox affichera la liste des villes de la province sélectionnée n'ayant pas participé à la courses à pieds. Tu peux sélectionner Toutes (= toutes les provinces) mais tu ne pourras pas sélectionner plusieurs provinces...
Attention le tableau en colonnes A et B ne doit pas contenir de cellules vide au milieu !...

Le fichier :

 

[gosselien]

Bonjour,

Très bonne idée ce code et très bien commenté en + !!!

Je vais regarder ça de près et je te dirai si ça s'applique à mon fichier constitué de TCD et de la liste des villes/provinces
Je tiens au jus.

Merci

P.

 

[Paf]

Bonjour gosselien, Robert,

un essai avec:
- liste des villes incluse dans le code ( à compléter)
- validation de données pour le choix de la province ( à compléter)
- création d'un dictionnaire globale à l'activation de la feuille Feuil1 (peut-être à déplacer dans Private Sub Workbook_Open() )
-une macro évènementielle déclenchée à la sélection de province


A+

 

[gosselien]

Bonjour Rober et Paf,

Paf, penses tu que l'on puisses mettre 281 communes dans l'array TabVille ? et autant de data dans Tabcode mais sans les encoder manuellement comme ça semble être le cas dans ton code ?

P.

ps: j'essayé qq chose de mon côté, qui se rapproche de ce que je veux sauf que j'aurais voulu mettre toutes les communes et provinces dans le code avec une array comme tu a fais mais en cas de modification, c'est plus délicat si l'utilisatrice n'utilise pas le vba; et d'autre par, comme c'est une fonction (à défaut de mieux pour l'instant) le msgbox apparait à chaque recalcul. Une autre solution sans recalcul serait envisageable...
Ma version ci annexée, mais j'espère aussi m'inspirer de l'un de vos codes (qui serviront, à n'en pas douter)

 

[Paf]

re,

Paf, penses tu que l'on puisses mettre 281 communes dans l'array TabVille ? et autant de data dans Tabcode

j'ai fait des essai avec quelque 600 noms de ville. pas de soucis.

mais sans les encoder manuellement comme ça semble être le cas dans ton code ?

Peut-être ai-je mal compris, mais comment concilier ça et

MAIS j'aurais voulu que toutes ces villes/provinces de gauche soient dans le code VBA et pas dans un fichier XL

il y a forcément une part manuelle (minime).
Dans le la feuille 2 du classeur post #4 il y a une macro qui met en forme la liste des villes et la liste des provinces, il n'ya plus qu'à copier le résultat dans le code(initialisation des arrays).

La liste des villes et codes provinces doit être en feuille 1 colonnes A et B ( comme elles sont dans le classeur post #1)


Par ailleurs, dans le classeur post #4, j'ai laissé des essais de fonctions personnalisées; mais le soucis de la fonction c'est effectivement qu'elle est recalculée lors de modifications. S'il ne faut pas qu'elle soit recalculée, le mieux, heu..., c'est de ne pas utiliser de fonction personnalisée mais une macro ( comme celles proposées éventuellement)

A+

 

[gosselien]

re,


j'ai fait des essai avec quelque 600 noms de ville. pas de soucis.
OK, alors ça pourrait fonctionner


il y a forcément une part manuelle (minime).
Dans le la feuille 2 du classeur post #4 il y a une macro qui met en forme la liste des villes et la liste des provinces, il n'ya plus qu'à copier le résultat dans le code(initialisation des arrays).
Je vais vois si avec ça je peux faire comme désiré

La liste des villes et codes provinces doit être en feuille 1 colonnes A et B ( comme elles sont dans le classeur post #1)

Par ailleurs, dans le classeur post #4, j'ai laissé des essais de fonctions personnalisées; mais le soucis de la fonction c'est effectivement qu'elle est recalculée lors de modifications. S'il ne faut pas qu'elle soit recalculée, le mieux, heu..., c'est de ne pas utiliser de fonction personnalisée mais une macro ( comme celles proposées éventuellement)
Je pense aussi en effet que je passerai par une macro avec au moins un inputbox demander quelle zone il faut tester
A+

Je vais essayer le ménager la chèvre et le choux

Merci !

 

[Si...]

salut

Je dois trouver quelle commune dans la liste de droite ne fait pas partie de la liste de gauche MAIS j'aurais voulu que toutes ces villes/provinces de gauche soient dans le code VBA …
Je vais essayer le ménager la chèvre et le choux

Tu as oublié le loup : en sachant comment tu initialises la liste des villes présentes on peut cibler plus vite celles qui manquent.

En attendant, une liste déroulante peut faire l’affaire. Dans le fichier joint tu as 2 cas pour voir...

 

[gosselien]

Hello,

merci si... je regarde à ton code aussi
Bonne semaine

P.