Rechercher et compter des occurences de chaines de caractère

**patricktoulon** · 11/04/2018, 14h36

Envoyé par breizho35

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))

heu.... tu test avant de poser des question????

msgbox sh1.Range("A3", sh1.Cells(Rows.Count, 1).End(xlUp)).address

**RyuAutodidacte** · 11/04/2018, 14h40

Bonjour,
Oui c'est normal pour Rows.Count
C'est le End(xlUp) qui permet de remonter à la 1ère ligne non vide, si les données s'arrêtent à la ligne 4500 on aura 4500

**patricktoulon** · 11/04/2018, 15h35

en travaillant avec des varianles tableaux peut etre mais j'en doute
sous cette forme

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub test()
    Set sh1 = Sheets("Feuil1")
    Set sh2 = Sheets("Feuil2")
    rang1 = sh1.Range("A3:D" & sh1.Cells(Rows.Count, 1).End(xlUp).Row).Value
    rang2 = sh2.Range("A2:E" & sh2.Cells(Rows.Count, 1).End(xlUp).Row).Value
 
    For i = 1 To UBound(rang1)
        For a = 1 To UBound(rang2)
            If Trim(rang2(a, 5)) Like "*" & Trim(rang1(i, 1)) & "*" And rang2(a, 3) = "ZONE_XX" Then rang1(i, 2) = rang1(i, 2) + 1
            If Trim(rang2(a, 5)) Like "*" & Trim(rang1(i, 1)) & "*" And rang2(a, 3) = "ZONE_BB" Then rang1(i, 3) = rang1(i, 3) + 1
            If Trim(rang2(a, 5)) Like "*" & Trim(rang1(i, 1)) & "*" And rang2(a, 3) = "ZONE_CC" Then rang1(i, 4) = rang1(i, 4) + 1
        Next
 Next
    sh1.Cells(3, 1).Resize(UBound(rang1), 4) = rang1
End Sub

**RyuAutodidacte** · 12/04/2018, 09h10

Salut Patrick,

Ton code est presque parfait a un petit détail près, quand tu te retrouve à vérifier avec le Like ce type IPA :
192.168.228.3
192.168.228.30
192.168.228.31
Etc …
Le résultat risque d'être fossé pour certains

Ryu

**Marc-L** · 12/04/2018, 12h26

Ce n'est pas faux : un fossé peut se creuser avec des résultats faussés ! …

**patricktoulon** · 12/04/2018, 13h23

oui c'est vrai c'est une base ayant vu son exemplaire et plusieur possibilités il faut pousser plus loin le like
peut etre avec des caractere non numerique avec les etoiles "*[A-Z-a-z]"
etant donné que ces cellule multiligne sont en fait en wraptext c'est assez difficile de ne pas exclure toute les possibilité
sinon un regex categorique sur le debut et fin de chaine
possibilité il y a foison

**RyuAutodidacte** · 12/04/2018, 13h49

Bonjour,

Testons cela :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Sub test_Ryu()
Dim DLF1&, DLF2&, VA, VB, IPA
    DLF1 = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row:        DLF2 = Sheets(2).Cells(Rows.Count, 3).End(xlUp).Row
    Sheets(1).Range("B3:D" & DLF1).Clear
    VA = Sheets(1).Range("A1:D" & DLF1).Value:      VB = Sheets(2).Range("A1:E" & DLF2).Value
 
    For i = 3 To UBound(VA)
        For j = 2 To UBound(VB)
            IPA = Split(VB(j, 5), VA(i, 1))
            If UBound(IPA) > 0 Then
                If Not IsNumeric(Left(IPA(1), 1)) And VB(j, 3) = "ZONE_XX" Then VA(i, 2) = VA(i, 2) + 1
                If Not IsNumeric(Left(IPA(1), 1)) And VB(j, 3) = "ZONE_BB" Then VA(i, 3) = VA(i, 3) + 1
                If Not IsNumeric(Left(IPA(1), 1)) And VB(j, 3) = "ZONE_CC" Then VA(i, 4) = VA(i, 4) + 1
            End If
        Next
    Next
    Sheets(1).Cells(1).Resize(UBound(VA), 4) = VA
End Sub

**patricktoulon** · 12/04/2018, 13h57

non ryu les chaines recherchées peuvent etre au debut, toute seule, au milieu, a la fin
va checher toto dans :

dfedfezfeztotodfdf
dffg

ou

totodsgfsdgdfggfrg

ou

fsdfvdstoto

ou

toto

ou meme

dfdnftotodfndf
dscdsctotodvjdsvf
fjsdhfjtoto

c'est ca l'execercice
sans parler des espaces ou des vrai suats de ligne eventuels

**RyuAutodidacte** · 12/04/2018, 14h02

C'est pas faux

bon ben c'est régler là non ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Sub test_Ryu()
Dim DLF1&, DLF2&, VA, VB, IPA
    DLF1 = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row:        DLF2 = Sheets(2).Cells(Rows.Count, 3).End(xlUp).Row
    Sheets(1).Range("B3:D" & DLF1).Clear
    VA = Sheets(1).Range("A1:D" & DLF1).Value:      VB = Sheets(2).Range("A1:E" & DLF2).Value
 
    For i = 3 To UBound(VA)
        For j = 2 To UBound(VB)
            IPA = Split("X" & VB(j, 5) & "X", VA(i, 1))
            If UBound(IPA) > 0 Then
                If Not IsNumeric(Left(IPA(1), 1)) And VB(j, 3) = "ZONE_XX" Then VA(i, 2) = VA(i, 2) + 1
                If Not IsNumeric(Left(IPA(1), 1)) And VB(j, 3) = "ZONE_BB" Then VA(i, 3) = VA(i, 3) + 1
                If Not IsNumeric(Left(IPA(1), 1)) And VB(j, 3) = "ZONE_CC" Then VA(i, 4) = VA(i, 4) + 1
            End If
        Next
    Next
    Sheets(1).Cells(1).Resize(UBound(VA), 4) = VA
End Sub

**patricktoulon** · 12/04/2018, 14h09

re

Code :

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

IPA = Split("X" & VB(j, 5) & "X", VA(i, 1))

oui je sais pas pas testé mais c'est cohérent la derniere fois que j'ai utiliser cette methode(ajout de caractere distinct) un certain Mr m'a affublé d'adjectifs calificatif heu... bon

ca reste lourd vu la longueur de tableau qui est prevue

**patricktoulon** · 12/04/2018, 14h13

perso j'ai adopté une autre solution bien plus radicale et sans sous boucle avec un regex
un simple regex.replace sur le patern "(\D)" par " "si la valeur numerique si trouve toujours et que la chaine le represntant est de meme longeur alors good !!!

edit:
le regex m'a donné une idée tient
meme sans lui mais en suivant le meme raisonement des matchs

Code :

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

1
2
3
4
5
6
7
8
Sub test()
    Dim chaine1 As String, chaine2 As String
    chaine1 = 123456
          x = Split(" " & "aaaaaaa123456ghfg446", chaine1)
     If UBound(x) > 0 Then
    If Not IsNumeric(Right(x(0), 1)) And Not IsNumeric(Left(x(1), 1)) Then MsgBox "oui" Else MsgBox "non"
    End If
  End Sub

essaie
"123456ghfg446"
ou
"5123456ghfg446"
ou
"aaaaaaa1234569ghfg446"
ou
"aaaaaaa1234567"
ou
"aaaaaaa123456"
comme ca c'est bon pour mac aussi

pour tre plus precis le pattern du regex est "(\d)?" & chaine1 & "(\d)?" on cherche la chaine précédé d'un chiffre ou rien et suivi d'un chiffre ou rien
si le .excecute trouve des matchs et que l'un des matchs a le meme len c'est good

edit ou
et meme en prenant le chose autrement avec le regex et matchs on cherche [un caractere non numerique] et [ la chaine numerique] et [ un caractere non numerique]
et on ajoute a la chaine de test un espace devant et derriere pour les occurence devut et fin dans chaine2
on fait sauter ainsi la boucle sur les matchs

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Function oui_non(chaine1 As String, chaine2 As String)
    oui_non = "non"
    With CreateObject("VBScript.RegExp"): .Global = True: .Pattern = "(\D)" & chaine1 & "(\D)"
        Set Matchs = .Execute(" " & chaine2 & " ")
        If Matchs.Count > 0 Then oui_non = "oui"
    End With
End Function
Sub test2()
    Dim chaine1 As String, chaine2 As String
    chaine1 = "123456"
    chaine2 = "aaa5123456ggggg123456ooojhg"
    MsgBox oui_non(chaine1, chaine2)
End Sub

je sais pas comment tu traduis ca en vba Mac avec le regex

**RyuAutodidacte** · 12/04/2018, 22h02

Bonsoir breizho35,
Re Patrick,

(Trop taf = - de vba )

Dans le même principe que mon split, en plus propre, utilisation Len, Instr, Mid avec vérification du caractère de droite si il est numérique
dont je conseille cette version à @breizho35, car surement plus abordable et plus simple à comprendre (cf. Faq pour => Len, Instr, Mid et variable tableau)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Sub test3_Ryu()
Dim DLF1&, DLF2&, VA, VB, IPA As Integer, LIP As Byte
    DLF1 = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row:        DLF2 = Sheets(2).Cells(Rows.Count, 3).End(xlUp).Row
    Sheets(1).Range("B3:D" & DLF1).Clear
    VA = Sheets(1).Range("A1:D" & DLF1).Value:      VB = Sheets(2).Range("A1:E" & DLF2).Value
 
    For i = 3 To UBound(VA)
        For j = 2 To UBound(VB)
            IPA = InStr(VB(j, 5), VA(i, 1)):    LIP = Len(VA(i, 1))
            If Len(VB(j, 5)) >= LIP And IPA > 0 Then
                If Not IsNumeric(Mid(VB(j, 5), IPA + LIP, 1)) And VB(j, 3) = "ZONE_XX" Then VA(i, 2) = VA(i, 2) + 1
                If Not IsNumeric(Mid(VB(j, 5), IPA + LIP, 1)) And VB(j, 3) = "ZONE_BB" Then VA(i, 3) = VA(i, 3) + 1
                If Not IsNumeric(Mid(VB(j, 5), IPA + LIP, 1)) And VB(j, 3) = "ZONE_CC" Then VA(i, 4) = VA(i, 4) + 1
            End If
        Next
    Next
    Sheets(1).Cells(1).Resize(UBound(VA), 4) = VA
End Sub

Patrick Je préfère cette version plutôt que le Split - pour le Grep sur Mac je n'ai pas encore regarder, mais c'est qq ch de similaire (à part le fait de passer par le Terminal via l'Applescript)

Edit : voilà un exemple que j'avais fait sur Mac (Excel 2011) : https://www.developpez.net/forums/d1...e/#post9496005

Edit 2 :
• Patrick

pour le post #28

• A celui qui à mis des

gratuitement sans explications/en justifier la raison est pour moi pas constructif (sérieux si on a plus le droit de s'exprimer …), j'ai donc mis

• @breizho35 les codes proposés par patrick (mis à part celui avec le Like ayant un petit défaut) sont OK, le plus abordable étant celui avec le Split (à adapter);
concernant le regex mieux vaut être habitué (peut être très pratique dans certaine situation)

**RyuAutodidacte** · 13/04/2018, 10h52

Salut Patrick,

j'ai testé dans un 1er temps les résultats dans le Terminal de Mac OS X :

Code :

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

echo "RSO_TRUC_BIDULE_192.168.228.31/22" | Grep -Eo '(\D)192.168.228.31(\D)'

résutlat : _192.168.228.31/

Code :

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

echo "192.168.228.31/22RSO_TRUC_BIDULE_" | Grep -Eo '(\D)192.168.228.31(\D)'

résutlat : aucun

Code :

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

echo "RSO_TRUC_BIDULE_/22RS192.168.228.31" | Grep -Eo '(\D)192.168.228.31(\D)'

résutlat : aucun

J'ai fais ça vite fait je regarderai dès que j'ai un moment …

**Docmarti** · 13/04/2018, 12h23

Envoyé par breizho35

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

[!0-9]

**patricktoulon** · 13/04/2018, 12h38

salut RYU

echo " RSO_TRUC_BIDULE_192.168.228.31/22 " | Grep -Eo '(\D)192.168.228.31(\D)'

echo " 192.168.228.31/22 " | Grep -Eo '(\D)192.168.228.31(\D)'

comme je l'ai dit dans ma derniere version ajouter un espace devant derriere tout simplement pour les debut et fin de chaine qui commenceraient ou termineraient par la chaine elle meme

tu l'a pas vu dans la demo ?

**RyuAutodidacte** · 13/04/2018, 18h03

re,

tu l'a pas vu dans la demo ?

j'ai un peu zappé car pressé par le temps - cela donne pour moi :

Code :

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

echo " 192.168.228.31/22RSO_TRUC_BIDULE_ " | Grep -Eo '(\D)192.168.228.31(\D)'

" 192.168.228.31/"

Code :

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

echo " RSO_TRUC_BIDULE_/22RS192.168.228.31 " | Grep -Eo '(\D)192.168.228.31(\D)'

"S192.168.228.31 "

Mais ce serai mieux avec un Lookahead negative et un Lookahead positive : le pattern brute :
(?!\D)192.168.228.31(?=\D)
Si tu as cela dans tons terminal, c'est mieux !
(https://regexr.com - jette y un oeil c'est sympa qd on veut trouver la bonne définition)

**patricktoulon** · 13/04/2018, 20h37

re j'ai des doutes avec cette syntaxe avec un regex en VB
JE vais regarder
je confirme j'ai vrai tout le temps

**Promethee25** · 14/04/2018, 00h32

Bonjour,

Il faudrait plutôt un negative lookbehind en préfixe et un negative lookahead en suffixe:

Code :

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

(?<!\d)92.168.2.3(?!\d)

Mais le souci est que VBA ne supporte pas ce genre de pattern donc il faut se rabattre sur quelque chose de "moins évolué":

Code :

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

(^|\D)92.168.2.3(\D|$)

Le fait que les parenthèses capturent des choses qui ne nous intéressent pas n'a pas d'importance car on cherche juste à savoir si le pattern a une correspondance et non à récupérer tel ou tel groupe (sinon on utiliserait "(?:^|\D)92.168.2.3(?:\D|$)").

Je propose une solution hybride Excel / VBA à base d'expressions régulières. Son avantage est que la partie VBA est une fonction matricielle à utiliser directement dans Excel et qui se contente de tester si oui ou non le pattern a une correspondance dans chacune des cases de la plage testée en entrée.
=> pas de maintenance de plage de cellules dans du code VBA; c'est Excel qui se charge de cette tâche puisqu'il est bien plus adapté à ça.
De plus cette fonction est générique et donc réutilisable à l'envi.

Code VBA:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Function CheckRegExp(testedRg As Range, pattern As String)
    Dim reg
    Dim rg As Range
    Dim tabMatch()
    Dim i As Long, j As Long
 
    i = testedRg.Row - 1
    j = testedRg.Column - 1
 
    Set reg = CreateObject("VBScript.RegExp")
    reg.pattern = pattern
 
    ReDim tabMatch(1 To testedRg.Rows.Count, 1 To testedRg.Columns.Count)
 
    For Each rg In testedRg
        tabMatch(rg.Row - i, rg.Column - j) = reg.test(rg.Value)
    Next rg
 
    CheckRegExp = tabMatch
 
    Set reg = Nothing
    Erase tabMatch
End Function

Formule Excel matricielle en Feuil1!B3 (à valider par Ctrl+Maj+Entrée):

Code :

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

=SOMME(1 * CheckRegExp(Tableau1[Texte];"(^|\D)" & Feuil1!$A3 & "(\D|$)") * (Tableau1[Zone] = Feuil1!B$1))

(Il est à noter que j'ai créé un tableau structuré pour délimiter la plage d'analyse)

Voir fichier joint.

**patricktoulon** · 14/04/2018, 02h12

re
bonsoir promethee

Le fait que les parenthèses capturent des choses qui ne nous intéressent pas n'a pas d'importance car on cherche juste à savoir si le pattern a une correspondance et non à récupérer tel ou tel groupe (sinon on utiliserait "(?:^|\D)92.168.2.3(?:\D|$)").

ma proposition etait dans ce sens pattern="(\D)" & chaine1 & "(\D)" le tiens me dedoine des espaces ajoutés a la chaine testée

**Docmarti** · 17/04/2018, 04h13

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Sub RechercheAvecLike()
 
Set f1 = Sheets("Feuil1")
Set f2 = Sheets("Feuil2")
debutF1 = 3
colonnesF1 = 3
dern1 = f1.Cells(Rows.Count, 1).End(xlUp).Row
dern2 = f2.Cells(Rows.Count, 1).End(xlUp).Row
 
f1.Cells(debutF1, 2).Resize(dern1 - (debutF1 - 1), colonnesF1).ClearContents
 
For j = 2 To 4 'Colonnes B a D
  entete = f1.Cells(1, j)
 
For k = 2 To dern2
 ac = f2.Cells(k, "c")
 ae = "x" & f2.Cells(k, "e") & "x"
 
If ac = entete Then
 
For m = 3 To dern1
 
 ValeurColonneA = f1.Cells(m, 1)
 
 testLike = ae Like "*" & "[!0-9]" & ValeurColonneA & "[!0-9]" & "*"
 
 If testLike Then
  f1.Cells(m, j).Value = f1.Cells(m, j).Value + 1
 End If
 
Next
 
End If
 
Next
Next
 
End Sub

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

Macros et VBA Excel

Discussions similaires

Partager

Partager