VBA : Compter 1 occurence dans une plage dynamique

[cibleo]

Bonsoir le forum,

Pour bien comprendre, j'illustre avec le fichier joint (JP14)

Feuille "Synthese", clic sur Bouton 4, j'ai fait une recherche avec le mot "scanner" sur les feuilles de Janvier.

S'affichent alors toutes les lignes contenant ce mot.

En bout de colonne B s'affiche dynamiquement la mention "TOTAL", puis le nombre de fois où apparait le mot.

Dans la procédure ci-dessous, j'ai donc placé une instruction (en rouge) qui est l'équivalent de =NBVAL($C$2:$M$12)

Mais le résultat renvoyé est égal à 13 soit le nbre de cellules non vides.

Or le mot "scanner" apparait ici 15 fois sur un total de 13 cellules. (puisqu'un même mot peut figurer plusieurs fois dans une cellule).

Private Sub remplirsynthese2(£nomfeuille As Variant, VSearch As String)
Dim plage As Range, Cel As Range, Adrdeb As String
Dim lig As Long
 
With Sheets(£nomfeuille)
    'Ici Plage de recherche par mots (TextBox1)
    Set plage = .Range("B3:L" & .Range("a65536").End(xlUp).Row)
    With plage
.../...
[COLOR=darkred]ShtR.Cells(DerLiR + 2, 2).Value = "TOTAL"[/COLOR]
.../...
[COLOR=darkred]ShtR.Cells(DerLiR + 3, 2).FormulaR1C1 = "=COUNTA(R2C" & 3 & ":R" & DerLiR & "C" & 13 & ")"[/COLOR]
.../...

Comment puis-je parvenir à compter l'occurence saisie initialement dans la Textbox de mon formulaire figurant dans la plage dynamique de la feuille "Synthese" ?

Dois-je créer une fonction personnalisée ou m'inspirer de la solution de Boisgontier dans le fil ci-dessous ?

https://www.excel-downloads.com/threads/recherche-doccurence-sans-reference.95348/

Pouvez-vous m'aider à résoudre ce problème.

Merci Cibleo

 

[cibleo]

Re : VBA : Compter 1 occurence dans une plage dynamique

Re pierrejean,

Plus haut, j'ai remis le fichier avec ta modification + la nouvelle version de la fonction de soenda, tu peux tester.

J'ai encore une autre variable à déclarer dans une de tes fonctions.

Manque la variable m dans la Function cpte_mots_pour

Sinon cela devrait fonctionner.

Cibleo

Roger peux-tu joindre le fichier pour pouvoir tester demain, j'ai du mal à te suivre.

 

[ROGER2327]

Re : VBA : Compter 1 occurence dans une plage dynamique

Bonsoir à tous

Je viens comme un cheveu sur la soupe avec ce petit truc :

[COLOR="DarkSlateGray"][B]Function compteMot(txt As String, mot As String) As Integer
Application.Volatile
Dim i As Long, j As Long, prfx, sufx
   prfx = Array(" ", ",", ":", "-", vbLf, vbCr)       [COLOR="SeaGreen"]'Adapter aux besoins[/COLOR]
   sufx = Array(" ", ",", ":", "-", ".", vbLf, vbCr)  [COLOR="SeaGreen"]'Adapter aux besoins[/COLOR]
   txt = " " & txt & " "
   For i = 0 To UBound(prfx)
      For j = 0 To UBound(sufx)
         compteMot = compteMot + UBound(Split(txt, _ 
            delimiter:=prfx(i) & mot & sufx(j), compare:=vbTextCompare))
      Next j
   Next i
End Function[/B][/COLOR]

Cette fonction renvoie le nombre d'occurrences du mot "mot" préfixé par l"une des valeurs de la liste prfx et suffixé par l'une des valeurs de la liste sufx dans le texte "txt".
Exemples dans le classeur joint.​

ROGER2327
#2434

 

[soenda]

Re : VBA : Compter 1 occurence dans une plage dynamique

Re,

Soenda, je viens de tester ta nouvelle version de Function Compte, cela à l'air de fonctionner , reste à l'adopter pour la Function test.

Evidemment ...

A plus

Edition : bonsoir Roger2327

 

[ROGER2327]

Re : VBA : Compter 1 occurence dans une plage dynamique

Re...

(...) Roger peux-tu joindre le fichier pour pouvoir tester demain, j'ai du mal à te suivre. (...)

J'ai oublié la pièce jointe : oubli réparé, voir #17.
​

ROGER2327
#2436

 

[pierrejean]

Re : VBA : Compter 1 occurence dans une plage dynamique

Re

Pour illustrer la difference de comportement entre les fonctions de ROGER (que le salue) et la mienne
Voir lignes en jaune
ps: j'ai mis egalement la fonction de Soenda (que je salue egalement)

 

[ROGER2327]

Re : VBA : Compter 1 occurence dans une plage dynamique

Bonjour à tous, bonjour pierrejean

C'est l'intérêt du paramétrage. Par exemple :

[COLOR="DarkSlateGray"][B]Function compteMot4(txt As String, mot As String) As Integer
Application.Volatile
Dim i As Long, j As Long, prfx, sufx
   prfx = Array(" ", ",", ":", "-",[COLOR="DarkRed"] ".", "e", "r",[/COLOR] vbLf, vbCr) [COLOR="SeaGreen"]'Adapter aux besoins[/COLOR]
   sufx = Array(" ", ",", ":", "-", ".", [COLOR="DarkRed"]"a", "s",[/COLOR] vbLf, vbCr) [COLOR="SeaGreen"]'Adapter aux besoins[/COLOR]
   txt = " " & txt & " "
   For i = 0 To UBound(prfx)
      For j = 0 To UBound(sufx)
         compteMot4 = compteMot4 + UBound(Split(txt, _
            delimiter:=prfx(i) & mot & sufx(j), compare:=[COLOR="DarkRed"]vbBinaryCompare[/COLOR]))
      Next j
   Next i
End Function[/B][/COLOR]

donne, dans le classeur joint, les mêmes résultats que votre fonction nb_dans.​

ROGER2327
#2441

 

[pierrejean]

Re : VBA : Compter 1 occurence dans une plage dynamique

Re

L'autre interet de votre fonction est de donner un excellent exemple de fonction recursive
Quant au parametrage il est vrai qu'il permet de pallier l'absence d'énoncé precis du problème (c'est ce qui caracterise la majorité des questions posées sur le forum)

 

[ROGER2327]

Re : VBA : Compter 1 occurence dans une plage dynamique

Re...

Je déplore comme vous que les problèmes soient fréquemment posés de façon imprécise. Quant à la fonction que je propose, je ne crois pas qu'elle soit récursive. Il me semble que, par exemple, compteMot4 est exactement interchangeable avec celle-ci :

[COLOR="DarkSlateGray"][B]Function compteMot4a(txt As String, mot As String) As Integer
Application.Volatile
Dim i As Long, j As Long, prfx, sufx[COLOR="Sienna"], s As Integer[/COLOR]
   prfx = Array(" ", ",", ":", "-", ".", "e", "r", vbLf, vbCr)
   sufx = Array(" ", ",", ":", "-", ".", "a", "s", vbLf, vbCr)
   txt = " " & txt & " "
   For i = 0 To UBound(prfx)
      For j = 0 To UBound(sufx)
         [COLOR="Sienna"]s[/COLOR] = [COLOR="Sienna"]s[/COLOR] + UBound(Split(txt, _
            delimiter:=prfx(i) & mot & sufx(j), compare:=vbBinaryCompare))
      Next j
   Next i
   [COLOR="Sienna"]compteMot4a = s[/COLOR]
End Function[/B][/COLOR]

Sous cette forme, on voit que compteMot4a ne s'appelle pas elle-même.
Est-ce la ligne

[COLOR="DarkSlateGray"][B]         compteMot4 = compteMot4 + UBound(Split(txt, _
            delimiter:=prfx(i) & mot & sufx(j), compare:=vbBinaryCompare))[/B][/COLOR]

qui vous fait penser que la fonction est récursive ? Dans ce cas, une exécution pas à pas montre qu'elle ne provoque pas un nouvel appel de compteMot4. Avec cette syntaxe, compteMot4 désigne une variable de type Integer.
En fait pour que la fonction s'appelle elle-même, il faudrait que l'on y trouvât une ligne de la forme :

[COLOR="DarkSlateGray"][B]         x = compteMot4[COLOR="Sienna"](paramètre1, paramètre2)[/COLOR][/B][/COLOR]

.
C'est du moins ce que je conçois, mais dans ce domaine, je me méfie toujours un peu, et je revendique le droit à l'erreur...​

ROGER2327
#2445

 

[pierrejean]

Re : VBA : Compter 1 occurence dans une plage dynamique

Re

Bon sang mais c'est bien sur !
Il me faudra apprendre a retourner 7 fois mon clavier sur lui même avant d'ecrire une anerie !!
Je revendique moi aussi haut et fort le droit a l'erreur

 

[ROGER2327]

Re : VBA : Compter 1 occurence dans une plage dynamique

Re...

Re

Bon sang mais c'est bien sur !
Il me faudra apprendre a retourner 7 fois mon clavier sur lui même avant d'ecrire une anerie !!
Je revendique moi aussi haut et fort le droit a l'erreur

Ne vous affligez pas ! Ou bien je prends une part de votre affliction, car, avant de vous répondre, j'ai eu un doute sérieux. Et s'il ne s'écrivait que des âneries jamais plus graves que celle-la, tout serait pour le mieux dans le meilleur des mondes !
​

Amicalement,
ROGER2327
#2446

 

[cibleo]

Re : VBA : Compter 1 occurence dans une plage dynamique

Bonsoir à tous

Un petit tour sur le fil, pour vous dire que je compare les 3 solutions avec le fichier de Roger.

Les différents cas de figure y sont présentés, c'est ce qu'on appelle une bonne base de travail.
On reconnait bien les vertus pédagoqiques de Roger2327

Pas mal le coup du suffixe et préfixe, j'ai eu du mal à comprendre au départ, j'ai cru qu'elle donnait le même résultat que soenda.

Petit bémol, j'aimerais que la fonction de Roger et Pierrejean s'applique à une plage et non pas à 1 seule cellule.
Dans ce sens, la solution de soenda correspond mieux à ma demande.

Finalement le résultat attendu pourrait correspondre à la colonne D (fonction nb_dans)

Dans l'absolu, je pourrais dire que la solution de pierrejean serait l'idéal puisqu'elle pare à toutes les erreurs de frappe ou concanétation.

A un degré moindre celle de Roger aussi. (je vais analyser en profondeur le paramétrage)

Celle de soenda me va comme un gant, si je veille aux fautes de frappes ou erreur de concanétation lors de ma saisie.

Pas simple de vous départager, avec vos solutions je suis tombé dans l'expectative

Ps : je n'ai pas revu le travail précédemment réalisé par pierrejean, je crois qu'il tenait compte d'une plage entière, je revoie cela.

Il faut que je revienne aussi sur la 2ème fonction réalisée par soenda.(Function test)
Voir le résultat souhaité plus haut.

A+ Cibleo

 

[ROGER2327]

Re : VBA : Compter 1 occurence dans une plage dynamique

Re...

La question est de savoir ce que vous voulez compter. Par exemple, si vous voulez trouver les occurrences du mot "scanner", direz-vous qu'il faut compter "scannera" ?
Personnellement, je répondrais non à cette question.
Doit-on compter "Scanner" ? Oui pour moi.
Si vous voulez compter les occurrences du groupe de lettres "s, c, a, n, n, e, r" prises dans cet ordre, direz-vous qu'il faut compter "scannera" ?
Cette fois, je répondrais oui.
Doit-on compter "Scanner" ? Non.
Comme toujours, la réponse est dans la question.​

ROGER2327
#2450

 

[cibleo]

Re : VBA : Compter 1 occurence dans une plage dynamique

Re à tous,

Les différents cas de figure présentés dans ton fichier, m'avaient mis dans l'expectative

Je vais donc opter pour ceci.

La question est de savoir ce que vous voulez compter. Par exemple, si vous voulez trouver les occurrences du mot "scanner", direz-vous qu'il faut compter "scannera" ?
Personnellement, je répondrais non à cette question.
Doit-on compter "Scanner" ? Oui pour moi.

Bonne soirée Cibleo

 

[ROGER2327]

Re : VBA : Compter 1 occurence dans une plage dynamique

Re...

Sur la base que vous retenez (reconnaître des mots, et non des suites de lettres), un paramétrage plus souple que ceux que j'ai proposés précédemment s'impose. Par exemple :

[B][COLOR="DarkSlateGray"]   prfx = Array(" ", Chr(160), "-", "(", ")", "[", "]", "{", "}", ",", ";", ".", """", Chr(147), Chr(171), Chr(133), ":", "/", "\", "!", "+", "_", "*", vbLf, vbCr)
   sufx = Array(" ", Chr(160), "-", "(", ")", "[", "]", "{", "}", ",", ";", ".", """", Chr(148), Chr(187), Chr(133), ":", "/", "\", "!", "+", "_", "*", vbLf, vbCr, "s ", "s" & Chr(160))[/COLOR][/B]

Au cas (fort probable) où je n'aurais pas pensé à tout, il très facile d'ajouter un item à ces listes.
Vous remarquerez que ce paramétrage permet de compter "scanners" (pluriel), et d'ignorer "scannera".
Voilà le code, insensible à la casse, grâce au paramètre compare:=vbTextCompare :

[COLOR="DarkSlateGray"][B]Function compteMot6(txt As String, mot As String) As Integer
Application.Volatile
Dim i As Long, j As Long, s As Integer, prfx, sufx
   prfx = Array(" ", Chr(160), "-", "(", ")", "[", "]", "{", "}", ",", ";", ".", """", Chr(147), Chr(171), Chr(133), ":", "/", "\", "!", "+", "_", "*", vbLf, vbCr)
   sufx = Array(" ", Chr(160), "-", "(", ")", "[", "]", "{", "}", ",", ";", ".", """", Chr(148), Chr(187), Chr(133), ":", "/", "\", "!", "+", "_", "*", vbLf, vbCr, "s ", "s" & Chr(160))
   txt = " " & txt & " "
   For i = 0 To UBound(prfx)
      For j = 0 To UBound(sufx)
         s = s + UBound(Split(txt, delimiter:=prfx(i) & mot & sufx(j), compare:=vbTextCompare))
      Next j
   Next i
   compteMot6 = s
End Function[/B][/COLOR]

ROGER2327
#2452

 

[soenda]

Re : VBA : Compter 1 occurence dans une plage dynamique

Bonsoir le fil

Je répare mon omission d'hier

Function test(ByVal Nom$, ByVal ch$, ByVal R As Range) As Integer
Dim L As Integer
Dim p As Range
 
Set p = Range(Left(R.Address, InStr(R.Address, ":") - 1))
 
For L = 0 To R.Rows.Count - 1
    If p.Offset(L).Value = Nom Then test = test + Compte(ch, p.Offset(L, 1).Resize(, R.Columns.Count - 1))
Next
 
End Function

@cibleo : je te laisse le soin d'ajouter la fonction "UCase" (ou LCase) aux endroits appropriés.

A plus