Rechercher et compter des occurences de chaines de caractère

**breizho35** · 09/04/2018, 11h49

Bonjour,

Pourriez vous m'aider à faire une macro svp ? Je suis novice et après plusieurs tentatives je ne m'en sort pas.

Mon besoin est de trouver le nombre d’occurrence d'une chaine de caractère précise et en fonction de certain critère.
Je post un fichier exemple en pièce jointe.

J'ai 2 feuilles de calculs: feuil1 et feuil2

Pour chaque ligne de feuil1 (à partir de ligne 3), j'ai besoin de:

- sur feuil1, prendre la valeur de la colonne A (on va l'appeller IPA)
- sur feuil2, rechercher dans la collone C une variable nommé ZONE_XX
- pour chaque variable ZONE_XX trouvée dans feuil2 collone C, rechercher si la valeur IPA est présente dans la chaine de caractère du contenu de la collone E de feuil2(mais attention, cette cellule peut contenir plusieurs lignes avec des retours chariots).
- si IPA est trouvé, alors incrémenter de 1 la valeur de la cellule feuil1 collone B et continuer la recherche jusqu'à la dernière ligne de feuil2. si IPA est de nouveau trouvé, alors incrémenter de 1 la valeur de la cellule feuil1 collone B etc...
- si IPA n'est pas trouvé alors continuer la recherche jusqu'a la dernière ligne de feuil2.

- refaire la même procédure avec la ligne suivante de feuil1

Merci à vous,

Breizho

**patricktoulon** · 09/04/2018, 13h58

Bonjour
le sheets en piece jointe n'est absoluement pas representatif de ta question

faire une recherche sur la formule "=CHERCHE("toto",plage de recherche )"

**breizho35** · 09/04/2018, 14h07

Bonjour,
j'ai vérifié et je pense que ma pièce jointe est justement représentative de ma question, aurais je mal expliqué mon besoin ?
cdt
breizho

**Marc-L** · 09/04/2018, 14h38

Bonjour,

pourquoi un code ? Déjà via une formule de feuille de calculs …

Sinon pour un code écrire au moins un algorithme qui tienne la route.

___________________________________________________________________________________________________________

Je suis Paris, Egypte, Nigeria, New-York, Mogadicio, Barcelone, London, Manchester, Stockholm, Istanbul, Berlin, Nice, Bruxelles, Charlie, …

**breizho35** · 09/04/2018, 15h02

re,
pourquoi un code ?
>> Je vais devoir complexifier mes mes opérations par la suite, il va me falloir du vba.
>> Etant novice en vba, je cherche à faire qq chose de simple pour commencer, afin de bien comprendre et à l'enrichir progressivement avec des conditions...

**patricktoulon** · 09/04/2018, 15h02

oui autant pour moi

une esquisse de code
pas optimisé mais fonctionel

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub test()
    Set sh1 = Sheets("Feuil1")
    Set sh2 = Sheets("Feuil2")
    Set rang1 = sh1.Range("A3", sh1.Cells(Rows.Count, 1).End(xlUp))
    rang1.Resize(rang1.Rows.Count, 3).Offset(0, 1).ClearContents
    For Each cel1 In rang1.Cells
        For Each cell In sh2.Range("E1", sh2.Cells(Rows.Count, 5).End(xlUp)).Cells
            If cell.Text Like "*" & Trim(cel1.Text) & "*" And cell.Offset(0, -2).Text = "ZONE_XX" Then cel1.Offset(0, 1).Value = cel1.Offset(0, 1).Value + 1
            If cell.Text Like "*" & Trim(cel1.Text) & "*" And cell.Offset(0, -2).Text = "ZONE_BB" Then cel1.Offset(0, 2).Value = cel1.Offset(0, 2).Value + 1
            If cell.Text Like "*" & Trim(cel1.Text) & "*" And cell.Offset(0, -2).Text = "ZONE_CC" Then cel1.Offset(0, 3).Value = cel1.Offset(0, 3).Value + 1
        Next cell
    Next cel1
End Sub

**Marc-L** · 09/04/2018, 15h13

Commencer par une formule de calculs simplifie d'autant le code
car il suffirait de coder à peine cinq lignes sans utiliser de boucle …

**breizho35** · 09/04/2018, 17h44

re,
Merci Patrick, j'ai testé et cela répond à mon besoin. Pourriez vous svp commenter votre code ?

En effet, je suis novice et je suis en train d'adapter votre code à mon fichier final qui comprend plus de 4000 entrées dans feuil1, aussi plus d'entrée dans feuil2 et d'autres champs pour lesquels je vais adapter votre formule.

Et pouvez vous m'expliquer comment fonctionne ce bout de code (avec une notion d'Offset qui m'échappe):

For Each cell In sh2.Range("E1", sh2.Cells(Rows.Count, 5).End(xlUp)).Cells
If cell.Text Like "*" & Trim(cel1.Text) & "*" And cell.Offset(0, -2).Text = "ZONE_XX" Then cel1.Offset(0, 1).Value = cel1.Offset(0, 1).Value + 1
If cell.Text Like "*" & Trim(cel1.Text) & "*" And cell.Offset(0, -2).Text = "ZONE_BB" Then cel1.Offset(0, 2).Value = cel1.Offset(0, 2).Value + 1
If cell.Text Like "*" & Trim(cel1.Text) & "*" And cell.Offset(0, -2).Text = "ZONE_CC" Then cel1.Offset(0, 3).Value = cel1.Offset(0, 3).Value + 1
Next cell

Pouvons-nous optimiser certaine ligne pour gagner du temps ? (peut être en intégrant une notion de nombre de ligne max ?)

Merci

Breizho

**patricktoulon** · 09/04/2018, 18h18

re
for each cel1 in ......'la feuil1
for each cell in ......'la feuil2

If cell.Text Like "*" & Trim(cel1.Text) & "*" And cell.Offset(0, -2).Text = "ZONE_XX" Then cel1.Offset(0, 1).Value = cel1.Offset(0, 1).Value + 1

traduction
si la cellule boucle2 sur feuill2 contient quelque chose +la valeur de la cellule de la boucle 1 sans espace + autre chose et la cellule boucle 2 feuil2 decalé de -2 colonne ="zonne_XX" alors
la cellule de la boucle1 decalé de de +1 colonne (donc (B")= sa propre valeur +1

test cela

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 msgbox range("A1").offset(0,1).address

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

msgbox range("E5").offset(0,-2).address

Pouvons-nous optimiser certaine ligne pour gagner du temps ? (peut être en intégrant une notion de nombre de ligne max ?)

non car la on boucle uniquement sur le lignes utilisées avec sh2.Cells(Rows.Count, 5).End(xlUp)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

For Each cell In sh2.Range("E1", sh2.Cells(Rows.Count, 5).End(xlUp)).Cells

pour optimiser il faudrait passer par des formule ou un filtre avancé

**breizho35** · 10/04/2018, 12h15

Bonjour,

Merci Patrick pour tes explications, c'est parfait.

Sur un gros fichier, la macro est très très longue à s'exécuter (je compte en heure), une formule aurait peut être été bien mais c'est déjà très très bien, ça fonctionne et ç'est compréhensible.
Je vais mettre le sujet en résolu.

Merci beaucoup pour ton aide.

brezho

**Marc-L** · 10/04/2018, 13h25

Une formule est souvent bien plus instantanée par code car déjà sans boucle …

Maintenant le code de Patrick serait un peu plus rapide en désactivant l'affichage le temps de l'exécution …

**breizho35** · 10/04/2018, 15h24

Re,
en désactivant l'affichage le temps de l'exécution … >> Euh, on fait ça comment ? :-)

Merci

Breizho

**patricktoulon** · 10/04/2018, 15h30

déactiver le raffraichissement de l'ecran

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

application.screenupdating=false

a mettre tout au debut de la sub

**breizho35** · 10/04/2018, 15h54

Re,

merci Patrick !

Marc, je ne dis pas que je la solution formule n'est pas viable, je ne sais tout simplement pas l'écrire ou faire un algo à la hauteur pour une bonne compression de tous.

Le code de Patrick fonctionne, c'est donc efficace mais occasionne en effet une grosse lenteur pour balayer 17 champs de 4000 lignes dans la première feuil et des données de 500lignes sur la seconde feuil.

++

breizho

**Marc-L** · 10/04/2018, 16h14

Oui car une formule peut déjà s'appliquer globalement à une plage de cellules
au lieu d'une boucle cellule par cellule souvent bien plus longue …

C'est pour cela qu'en connaissant mieux Excel et en s'appuyant donc sur ses fonctionnalités internes
des usines à gaz en VBA algorithmique sont évitées et parfois le code est bien plus simple à maintenir.

Entre un débutant en VBA et un débutant en VBA connaissant bien Excel, il y a déjà un gouffre !

**breizho35** · 11/04/2018, 11h04

Merci à vous deux pour nos échanges, et merci particulièrement Patrick de m'avoir fournit le code.
Maintenant que j'ai un code qui fonctionne, je vais tenter de lui passer des formules pour gagner du temps (2h30 pour le traitement c'est tout de même long :-) )

Bonne journée.

breizho

**patricktoulon** · 11/04/2018, 11h30

bonjour
2h30 !!! non la il y a un soucis ailleurs
j'ai une autre idée qui me trotte je vais la mettre au point

**breizho35** · 11/04/2018, 12h04

ok merci, toute idée me sera utile.

Aussi, je suis en train de corriger des erreurs qui se comprennent tout à fait compte tenu du code.

La chaine testée ci dessous est une adresse IP donc sous la forme XXX.XXX.XXX.XXX les X étants des chiffres de 0 à 255.
Le problème est que le IF match du 192.168.230.13 alors que la valeur testées était 192.168.230.130
If cell.Text Like "*" & Trim(cel1.Text) & "*" And cell.Offset(0, -1).Text = "TRUC TRUC" Then cel1.Offset(0, 1).Value = cel1.Offset(0, 1).Value + 1

Compte tenu du fait que je peux avoir tout autre caractère à la suite mais pas un chiffre. Il me faut modifier le test:
If cell.Text Like "*" & Trim(cel1.Text) & NOT [0 à 9] & "*" And cell.Offset(0, -1).Text = "TRCU TRUC" Then cel1.Offset(0, 1).Value = cel1.Offset(0, 1).Value + 1

Quelle serait la syntaxe du NOT [0 à 9] ?

Merci

breizho

**patricktoulon** · 11/04/2018, 12h26

If cell.Text Like "*" & Trim(cel1.Text) & NOT [0 à 9] & "*" And cell.Offset(0, -1).Text = "TRCU TRUC" Then cel1.Offset(0, 1).Value = cel1.Offset(0, 1).Value + 1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

If cell.Text Like "*" & Trim(cel1.Text) & "*"  and not cell.Text Like "[0- 9] " And cell.Offset(0, -1).Text = "TRCU TRUC" Then cel1.Offset(0, 1).Value = cel1.Offset(0, 1).Value + 1

**breizho35** · 11/04/2018, 14h33

re,

juste une idée, pour gagner un peu de traitement, ne pourrions nous pas définir un nombre de ligne limité (par exemple 4000), plutôt que je prendre la maximum (1048576 lignes) ?
Ci dessous, la variable "Rows.Count" prend la valeur 1048576
Set rang1 = sh1.Range("A3", sh1.Cells(Rows.Count, 1).End(xlUp))

Rechercher et compter des occurences de chaines de caractère [XL-2013]

Macros et VBA Excel

Discussions similaires

Partager

Partager