Trouver les cellules contenant un motRésolu

Question

Bonjour, 

Je me permets de poster ici car après avoir recherché une solution à mon problème, je ne l'ai toujours pas trouvée.

Dans mon classer, j'ai une feuille (feuille1) contenant une base de données.
Cette base de données référence des opérations effectuées par des tiers.
Un tiers est identifié par un numéro unique.
Cette base de données est alimentée chaque mois, et donc de nouveaux tiers peuvent être ajoutés.

Pour traiter cette base de données, j'ai créé une autre feuille (feuille2)
Dans feuille2, j'ai commencé par créé une colonne référençant chaque numéro unique à partir des données de feuille1 :

Sub Copie_Numeros()

'Sélection de la feuille1 & copie des valeurs dans la feuille Contrôles
ActiveWorkbook.Worksheets("feuille1").Select
ActiveSheet.Range("L2:L" & Range("A" & Rows.Count).End(xlUp).Row).Copy Worksheets("feuille2").Range("A2")

'Suppression des doublons
ActiveWorkbook.Worksheets("feuille2").Select
ActiveSheet.Range("A2").RemoveDuplicates Columns:=1, Header:=xlNo

(Au passage, à quoi correspond Columns:=1 ? Je l'ai laissé parce que ça marchait mais j'aimerai comprendre ce que j'écris ou recopie !)

Je voudrais regrouper certaines valeurs associées à chaque ligne de feuille1 pour un même numéro ; ou alors tirer une valeur parmi plusieurs, etc. (par exemple, regrouper tous les montants des transactions effectuées par un certain numéro, ou bien extraire la date de la plus vieille/récente transaction, etc.)

Mon idée était de commencer par chercher toutes les lignes associées à un certain numéro (ou bien les cellules, puisque après je vais devoir prendre la valeur d'une certaine cellule de cette ligne) pour les traiter ensuite.

Seulement je n'arrive pas à me servir de la fonction (si c'en est une) find. Et je ne sais pas si elle peut me retourner une plage (Range) de cellules qui seront discontinues.

Ou alors peut-être existe-t-il un outil plus simple ?

J'espère avoir été clair et je vous remercie de l'aide que vous m'apportez,
Cordialement.

ucfoutu · Answer

Bonjour, 
J'ai commencé par déplacer ta discussion, que tu avais ouverte dans la section VB.Net (!), vers la section adéquate (VBA Office (Excel, Word ...).
Prends-en s'il te plait bonne note et fais la prochaine fois très attention à ce choix. C'est important.

ucfoutu · Answer

Et toi, commence déjà par t'interroger sur le sens de ta ligne de code :
ActiveSheet.Range("A2").RemoveDuplicates Columns:=1, Header:=xlNo 
Ouvre donc ton aide vba sur le mot RemoveDuplicates et lis.
Cette méthode tend a supprimer les doublons éventuellement présents dans une plage. Comment peut-on penser à l'existence de doublons dans ... une seule cellule ?!

Pauyr · Answer

Bonjour,

Excuse moi de ne pas avoir posté là où il le fallait et merci d'avoir déplacé mon sujet à l'endroit approprié.

Un peu agressif le "et toi", non ? :)

Je n'ai pas de problème sur le RemoveDuplicates. Il le fait après avoir copié une  colonne de ma feuille1 (celle contenant tous les numéros uniques, mais qui sont en doublons puis cette base de donnée référence des transactions en fonction de leur date).

Edit : j'ai compris ta question. Je suis conscient que je ne sélectionne que la cellule A2 mais ça fait le boulout quand même. Peut-être avec le columns:=1 ?

ucfoutu · Answer

Le "et toi",; agressif ? Non ! Il correspond à mon "j'ai commencé par ..." et t'invite, toi également, à commencer par ...
Mais si cela te pose des problèmes de susceptibilité, je te laisse pile là.

Pauyr · Answer

Du tout. D'ou mon smiley !

J'ai édité mon message j'ai compris ce à quoi tu faisais référence.

ucfoutu · Answer

Bien reçu ton MP (n'en parlons donc plus).
 Une question, à ce stade : cette application est-elle destinée à être distribuée ? Car si oui : attention : la méthode RemoveDuplicate n'est présente qu'à partir de la version 2007.
________________________
Réponse exacte ? => "REPONSE ACCEPTEE" facilitera les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement répéter son contenu. Je n'interviend

Pauyr · Answer

* Oui elle est destinée à être distribuée (à des filiales, en interne) donc je pense que c'est bon pour la version 2007 ou supérieur ; j'utilise la version 2010 là.

La méthode RemoveDuplicate est bien utile néanmoins ; j'en avais codé une version à la main et c'était plutôt long à l'exécution (bien qu'il soit probable que je n'ai pas utilisé la méthode la plus rapide).

* Pour étoffer un peu sur ce que je voudrais réussir à faire, j'aimerai donc stocker les numéros de cellule (ou simplement le numéro de la ligne) où se trouvent donc les numéros dans la première feuille pour ensuite pouvoir comparer (facilement) les différentes valeurs associées à chacun de ces numéros.

Je me répète peut-être mais j'essaie juste d'être clair ; je ne pense pas pouvoir fournir une copie du fichier pour des raisons de confidentialité. De plus j'aimerais comprendre la méthode et ne pas simplement recopier un code.

* Pourquoi ne pas utiliser un TCD ? J'ai essayé d'en coder un, mais de chez moi et avec une version différente ce qui fait que j'ai eu quelques problèmes. 

Aussi, je n'arrive pas à faire en sorte que les étiquettes de colonnes soient "somme de valeurs" ; ce que j'arrive à faire en faisant mon TCD à la main.

De plus, il faudrait que je filtre certaines des valeurs associés à un numéro. Par exemple, ressortir la date mini de la transaction, sauf si cette transaction est X. Dans ce cas-là, il me faudrait la première date mini pour une transaction y. Ce genre de choses ; je ne suis pas sûr de pouvoir le faire avec un TCD.


* Bonne journée,
Cordialement.

ucfoutu · Answer

Si tu as l'intention d'effectuer plusieurs requêtes différentes sur tes données, il vaudra sans doute mieux que tu travailles avec une véritable base de données
La méthode Find te permet de "relever" plusieurs rangs correspondant à un dans une colonne (ton aide VBA est assortie d'un exemple à ce sujet).
Il est toujpurs possible de constituer une plage discontinue (avec la méthode Union). La plage ainsi créée peut être utilisée ...
Fais cette expérience : des données dans A1 à C1, A2 à C2 et A3 à C3
et lance ce code :
Set plage = Application.Union(Range("A1:C1"), Range("A3:C3"))
plage.Copy Destination:=Range("F3")
Pour ce qui est de tes autres questions (transaction la plus récente et transaction la plus ancienne) : Tout dépend de comment tu as enregistré les données.
Si dans l'ordre chronologique (donc à chaque fois à la ligne suivante) : la transaction la plus ancienne est la première et la plus récente, la dernière. 
Mais si pas dans ordre chronologique, tu as le choix entre :
---trier l'ensemble sur la colonne des dates puis relever la 1ère ou la dernière
--- ne pas trier et utiliser WorkSheetFunction.mini ou WorksheetFunction.maxi (selon le cas), et chercher ensuite la valeur obtenue à l'aide de Find
 
________________________
Réponse exacte ? => "REPONSE ACCEPTEE" facilitera les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement répéter son contenu. Je n'interviend

jordane45 · Answer

Bonjour,
chercher toutes les lignes associées à un certain numéro

Il est possible, via la méthode FIND de rechercher TOUTES les occurrences d'un mot (ou d'un numéro..) dans un plage

Voir le second exemple : FindALL
http://www.jr.loucan.fr/e107_plugins/forum/forum_viewtopic.php?11

Pauyr · Answer

Si tu as l'intention d'effectuer plusieurs requêtes différentes sur tes données, il vaudra sans doute mieux que tu travailles avec une véritable base de données 

Donc, ne pas passer par un TCD ? Ce matin j'ai enregistré une macro en créant un TCD pour voir le code que cela me donnait et je  pense avoir un peu compris.

Est-il possible de faire une opération préalable à ce qu'affichent les colonnes d'un TCD ? Si non, j'imagine que je peux créer une TCD pour avoir les valeurs que je veux, puis y juxtaposer une colonne perso (où, là, je ferai l'opération nécessaire pour avoir la donnée que je veux) ?

Pauyr · Answer

Mince, j'ai envoyé mon message avant de dire tout ce que je voulais. La suite : 

Il est toujpurs possible de constituer une plage discontinue (avec la méthode Union). La plage ainsi créée peut être utilisée ... 
Fais cette expérience : des données dans A1 à C1, A2 à C2 et A3 à C3 
et lance ce code : 
Set plage = Application.Union(Range("A1:C1"), Range("A3:C3"))
plage.Copy Destination:=Range("F3")

Okay je vois comment ça marche. Merci pour le union !

Pour ce qui est de tes autres questions (transaction la plus récente et transaction la plus ancienne) : Tout dépend de comment tu as enregistré les données. 
Si dans l'ordre chronologique (donc à chaque fois à la ligne suivante) : la transaction la plus ancienne est la première et la plus récente, la dernière. 
Mais si pas dans ordre chronologique, tu as le choix entre : 
---trier l'ensemble sur la colonne des dates puis relever la 1ère ou la dernière 
--- ne pas trier et utiliser WorkSheetFunction.mini ou WorksheetFunction.maxi (selon le cas), et chercher ensuite la valeur obtenue à l'aide de Find 

Le problème de trier sur la colonnes des dates est que je veux une date par numéro. J'imagine que c'est le même problème pour le WorksheetFunction.mini/maxi. Je vais réfléchir à comment je peux procéder et je vais regarder le findall préconisé par Jordane.

Merci à vous

Pauyr · Answer

Bonjour,

Merci à vous deux, grâce à votre aide j'ai grandement pu avancer.
Je me retrouve néanmoins devant un nouveau problème.
J'ai réussi à extraire la plage contenant la date minimale de transaction pour un numéro donné.

Néanmoins, parfois, cette plage n'est pas unique.
J'ai alors voulu introduire un Countif sur cette plage pour déterminer si elle contenait une ou plusieurs cellules.
Ça marchait bien hier, mais depuis ce matin, je me tape une erreur d'exécution 1004 "impossible de lire la propriété Countif de la classe worksheetfunction".

Et là, je suis perdu. Voici le code : 

Option Explicit
Global numeroGM, mindate, maxdate, minkm, maxkm, minheure, maxheure As Variant
Global c, cel, cell, cell2, cell3, cell4, cell5 As Range

Sub aTest()
Dim x As Variant
Application.ScreenUpdating = False

'Prend la valeur d'un numéro G dans la feuille Contrôles
 numero = "XX"

'Trouve la range où apparaît le numéro GM dans la BDD filtrée
    Worksheets("bddfiltrée").Select
    Set cell = FindAll(Range("L2:L" & Range("L" & Rows.Count).End(xlUp).Row), numeroGM)
    MsgBox cell.Address
   'MsgBox x
      
        ' Décale à la colonne Date de transaction
        Set cell2 = cell.Offset(0, 3)
        
         'Transforme les valeurs en date
            'For Each c In cell2
            'c.Value = CDate(c)
            'Next c
                                  
                ' Trouve la date minimale
                mindate = Application.WorksheetFunction.Min(cell2)
                MsgBox (mindate & "      " & cell2.Address)
                
                        'Teste si la plage cell contient une ou plusieurs valeurs
                        
                        x = Application.WorksheetFunction.CountIf(cell2, mindate)
                        MsgBox x
                                
                                'Si la plage n'est pas unique, on fait une recherche sur les heures
                                If x <> 1 Then
                                Set cell5 = FindAll(cell2, CDate(mindate)).Offset(0, 1)
                                   
                                        'Changer le format des cellules en heures
                                        For Each c In cell5
                                        c.NumberFormat = "[$-F400]h:mm:ss AM/PM"
                                        c.Value = Hour(c)
                                        Next c
                  
                                            minheure = Application.WorksheetFunction.Min(cell5)
                                            MsgBox (minheure & "    " & cell5.Address)
                                            'mindate = FindAll(cell5, Hour(minheure)).Offset(0, -1)
                                            'MsgBox mindate
  End If
  
End Sub



De plus, j'ai du mal à comprendre l'histoire de format des cellules. Dans ma BDD filtrée, les heures sont aux format heures mais le worksheetfunction.min ne marche que si je passe par un Cdate ?

Du coup, quand j'essayais hier de comparer les heures, je m'emmêlais également les pinceaux. Elles sont afficheés en format "hh:mm" (16:14 par ex), j'ai voulu alors changer le format (j'ai trouvé le c.NumberFormat = "[$-F400]h:mm:ss AM/PM" grâce à l'enregistreur de macro pour pouvoir les traiter mais pareillement le min me donne des 0.

Cordialement.

ucfoutu · Answer

déjà : si cell is nothing (pas de correspondance trouvée), cell12 est également nothing.

Pauyr · Answer

Je suis pas sûr de bien comprendre, mais tel que je l'ai écrit cell est toujours quelque chose (la colonne cible contient forcément le numéro auquel je fais référence). Donc à fortiori cell2 l'est aussi.

ucfoutu · Answer

J'ai des difficultés à suivre ton code (je n'écris personnellement jamais de manière si "embrouillée")
Je remarque par ailleurs tes déclarations de variables, sous la ligne Option Explicit.
Elles ne sont pas faites correctement.
Sous VBA, le type de chaque variable DOIT être défini (Dim toto as string, titi as string, par exemple et non Dim toto, titi as string)

PS : et ce fait peut être très gênant, car, dans cette déclaration :
Global c, cel, cell, cell2, cell3, cell4, cell5 As Range
seule la plage cell5 est assurément typée en Range, les autres variables restant des Variant.
Il se trouve que VBA va "traduire" alors au mieux ce type, en fonction de ton code.
Or : il risque alors d'y voir tantôt une plage, tantôt son contenu ! 
Et (comme dit plus haut) j'ai des difficultés à voir avec la plus grande certitude ce que tu cherches à faire ...


 
________________________
Réponse exacte ? => "REPONSE ACCEPTEE" facilitera les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement répéter son contenu. Je n'interviend

Pauyr · Answer

* Merci pour l'info ! Je pensais (me semble l'avoir lu qqpart ..) qu'on pouvait faire une déclaration horizontale d'un coup comme je l'ai fait ! Je vais rectifier ceci. Je dois également mettre le type (dim, public, etc?) après le global ? (Je dirais que non car je crois que global est un type, mais je préfère redemander !)

* Quant à ce que je veux faire (je pensais que mon code était assez clair, commenté et indenté !).

Je choisis un numéro dans une liste contenu sur une feuille à part.
Je vais dans la feuille contenant la BDD de toutes les transactions.
Je cherche la plage où se numéro est trouvé dans la colonne répertoriant tous les numéros.
Je décale cette plage de manière à avoir toutes les dates de transactions effectuées par ce numéro.
J'effectue l'opération Cdate sur ces cellules.
J'effectue worksheetfunction.min pour trouver la plus petite date de transaction effectué par ce numéro.

Une fois que j'ai cette date, je recherche sa plage.
Une fois que j'ai cette plage, je la décale horizontalement de manière à pouvoir prendre les valeurs qui y sont associées et qui m'intéressent.

Le problème que je rencontre est quand la plage  de la date mini contient plusieurs cellules (dans le cas où la personne a effectuée deux opérations le 1er janvier par exemple).

Pour contourner cela, je me suis dit que j'allais compter (avec Countif) combien de cellule contenant cette plage et que dans le cas où c'était différent de 1, j'allais refaire des opérations de manière à cette fois-ci, comparer l'heure.

J'espère être clair, n'hésite pas à me redemander si quelque chose est flou.

PS : J'avouerai en toute honnêteté que je ne comprends pas pourquoi je passe par Cdate. Toujours est-il que si je ne le fais pas, ça me renvoie des 0.


Edit : là, j'ai pour l'instant le problème sur le Countif qui me met l'erreur d'exécution 1004 "impossible de lire la propriété Countif de la classe worksheetfunction". 

Et, le fait que je comprends pas trop les format dates etc mais je vais regarder ça de plus près après manger :)

ucfoutu · Answer

La date et l'heure de la transaction sont à mettre dans une colonne, si tu veux faciliter les choses.
Est-ce le cas ?
Si non : fais-le
Si oui, cette colonne doit être formatée en  dd/mm/yyyy hh:mm;@
Excel mémorise alors les dates en numérique. Les comparaisons n'ont alors pas besoin de la fonction CDate
 
________________________
Réponse exacte ? => "REPONSE ACCEPTEE" facilitera les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement répéter son contenu. Je n'interviend

Pauyr · Answer

J'ai en effet une colonne date, en format date (quand je clique sur la cellule c'est ce que m'indique Excel).

J'ai en effet pu me passer de ce bloc :  'Transforme les valeurs en date
            For Each c In cell2
           'c.Value = CDate(c)
           'Next c
Par contre j'ai du utiliser le Cdate dans mon find all pour qu'il convertisse les dates stockées en numérique en date 'calendrier' pour qu'il puisse recherche parmi les dates de la colonne date. Jusque-là je comprends et c'est logique.

J'ai également une colonne heure, en format standard.
D'où le c.NumberFormat = "[$-F400]h:mm:ss AM/PM" que j'appliquais à ma sélection de cellule de ma colonne heure. J'ai essayé de modifier la partie droite du égal pour comprendre les paramètres mais ensuite Excel m'affichait "personnalisée" au lieu de "heure".

J'essaye vraiment de comprendre ce que j'écris et pourquoi je l'écris, j'espère ne pas apparaître comme quelqu'un qui veut une solution clé en main ; mais je suis relativement novice au langage VBA en plus du fait que je passe beaucoup de temps dessus donc j'imagine que je sature à un moment.

Merci en tout cas pour le temps que tu m'accordes.

ucfoutu · Answer

Formate donc (je te l'ai déjà dit) tas dates de transactions en Date-heure et tout sera simple 

Je ne veux pas transformer cette discussion en apprentissage (ce n'est pas le but d'un forum de développement).
Je vais me contenter de tenter de te mettre sur mla voie, par un exemple très simple :
 
Un bouton de commande Commnadbutton1 avec ce code, que je ne mets-là que pour "construire" une base de travail :

Private Sub CommandButton1_Click()
  Range("A1:A10").NumberFormat = "dd/mm/yyyy hh:mm:ss;@ "
  For i = 1 To 10
    deb = Timer
    Range("A" & i).Value = Now
    Do While Timer < deb + 1
      DoEvents
    Loop
  Next
End Sub

Un bouton de commande Commandbutton2, qui, lui est l'exploitation des données : 
Private Sub CommandButton2_Click()
  MsgBox CDate(WorksheetFunction.Min(Range("A1:A10")))
End Sub

Amuse-toi à déplacer vers où tu veux la première ligne ===>> ce sera toujours ellle qui sera, bien évidemment, retournée.

Je crois, suis persuadé, de ce que tu arriverais à un tel résultat tout seul si tu voulais bien cesser de tenter de brûler les étapes de ton apprentissage (jamais bon ...). Un apprentissage est toujours lent et commence toujours par des exercices très simples.

Pauyr · Answer

... Petite update.

Je ne peux pas en fait me passer du bloc susmentionné (j'ai cru que je pouvais car en fait je l'avais déjà exécuté avant, du coup quand je le désactivais ça changeait rien).

Quand je recommence à zéro, il me met une erreur. Pourtant le format est bien date dans la BDD. Au pire c'est pas très grave, ça marche quand je le laisse activé, j'ai d'autres chats à fouetter.

Trouver les cellules contenant un mot

29 réponses

Votre réponse

Discussions similaires