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 à tous,

J'ai trouvé cette fonction de Job75 que j'ai placé dans un module standard.

Function F(plage As Range, t$)
Dim Cel As Range, i As Integer
F = 0
For Each Cel In plage
For i = 1 To Len(Cel)
If Mid(Cel, i, [B][COLOR=red]7[/COLOR][/B]) = t Then F = F + 1
Next
Next
End Function

Placé manuellement dans une cellule de la feuille "Synthese", =F(C2:M12;"scanner"), cela fonctionne, résultat renvoyé = 15

Mais comment l'appeler dans la procédure "remplirsynthese2" pour remplacer l'instruction suivante :

 ShtR.Cells(DerLiR + 3, 2).FormulaR1C1 = "=COUNTA(R2C" & 3 & ":R" & DerLiR & "C" & 13 & ")"

Mais il faut aussi la modifier, puisque le 7 correspond à la longueur du texte recherché, alors que la chaine saisie dans la textbox est de longueur variable

Pouvez-vous m'aiguiller Cibleo

 

[cibleo]

Re : VBA : Compter 1 occurence dans une plage dynamique

Re bonsoir,

J'ai trouvé aussi cette autre fonction mais elle ne s'applique qu'à une cellule unique.

Comment l'adapter pour une plage de cellules ?

Public Function Compte(Car, Chaine) As Byte
'Compte le nombre d'occurences de "Car" dans la "Chaine"
Dim P As Byte
Do
P = InStr(P + 1, Chaine, Car, vbTextCompare)
If P > 0 Then Compte = Compte + 1
Loop Until P = 0
End Function

Traduite ci-dessous :

=compte("scanner";F6)

J'aimerais ceci :

=compte("scanner";C2:M12)

Cibleo

 

[soenda]

Re : VBA : Compter 1 occurence dans une plage dynamique

Bonjour le fil, cibleo

Une solution parmis d'autres

Public Function Compte(ch As String, rng As Range) As Integer
    Dim Cel As Range
    Dim V As Variant
    Dim S As String
 
    For Each Cel In rng
        S = S & Trim(Cel.Value) & " "
    Next
 
    ch = LCase(ch)
    For Each V In Split(Trim(S), " ")
        If LCase(V) = ch Then Compte = Compte + 1
    Next
 
End Function

A plus

 

[cibleo]

Re : VBA : Compter 1 occurence dans une plage dynamique

Bonjour à tous
Bonjour soenda,

Merci, on avance à grands pas.

J'ai placé ta fonction dans un module standard et remplacé ceci dans la procédure "remplirsynthese2" :

ShtR.Cells(DerLiR + 3, 2).FormulaR1C1 = "=COUNTA(R2C" & 3 & ":R" & DerLiR & "C" & 13 & ")"

Par cela :

ShtR.Cells(DerLiR + 3, 2).FormulaR1C1 = "=Compte(""[B][COLOR=red]scanner[/COLOR][/B]"" , R2C" & 3 & ":R" & DerLiR & "C" & 13 & ")"

Qui se traduit comme ceci en bout de colonne B feuille de calcul "Synthese".

=compte("[B][COLOR=red]scanner[/COLOR][/B]"; $C$2:$M$12)

Le résultat est ainsi obtenu.

Dans mon exemple, j'avais donc saisi le mot "scanner" dans la Textbox1 de mon formulaire pour la recherche.

Le premier argument de la fonction personnalisée est donc le mot saisi dans la TextBox1.

Comment modifier la syntaxe de la formule ci-dessus, dois-je indiquer la variable VSearch ou TextBox1.Value à la place de "scanner" ?

Là, je patauge complètement

Cibleo

 

[cibleo]

Re : VBA : Compter 1 occurence dans une plage dynamique

Re à tous,

Il me semble que j'ai trouvé :

ShtR.Cells(DerLiR + 4, 2).FormulaR1C1 = "=Compte[COLOR=red][COLOR=black]([/COLOR][B]""" & VSearch & """[/B][/COLOR] , R2C" & 3 & ":R" & DerLiR & "C" & 13 & ")"

J'aimerais maintenant créer une nouvelle fonction personnalisée sur le même modèle que soenda.

Feuille "Synthese", en bout de colonne C, s'affiche dynamiquement le nombre de cellules non vides pour le chauffeur "Pascal".

Voir l'instruction ci-dessous dans la procédure "remplirsynthese2"

ShtR.Cells(DerLiR + 3, 3).FormulaR1C1 = "=SUMPRODUCT((R2C" & 3 & ":R" & DerLiR & "C" & 13 & "<>"""")*(R2C" & 2 & ":R" & DerLiR & "C" & 2 & "=""PASCAL""))"

Qui se traduit comme ceci dans la feuille de calcul "Synthese".

=SOMMEPROD(($C$2:$M$19<>"")*($B$2:$B$19="PASCAL"))

Avec cette formule le résultat obtenu est 3 cellules non vides pour Pascal,

Mais j'aimerais obtenir 5 puisque le mot "scanner" apparait 5 fois dans les 3 cellules concernées par Pascal.

Pouvez-vous m'aider à créer cette nouvelle fonction qui doit faire référence à la colonne B où se trouve le nom des chauffeurs.

Cordialement Cibleo

 

[soenda]

Re : VBA : Compter 1 occurence dans une plage dynamique

Re

Une solution avec une cellule liée (...)

Le TextBox1 est dessiner sur la cellule [J1],
j'ai donc donnée à la propriété LinkedCell : J1
[A1:A5] est la plage dans laquelle on recherche le contenu de TextBox1

Maintenant, les instructions suivantes fonctionnent :

    Feuil1.[E1].Formula = "=Compte(J1,A1:A5)"
    ' ou
    Feuil1.[E1].FormulaR1C1 = "=Compte(RC[5],RC[-4]:R[4]C[-4])"

A adapter ...

A plus

Edition : Oups ! Pas vu ton dernier message . . . (Avec le fichier ça va mieux)

 

[soenda]

Re : VBA : Compter 1 occurence dans une plage dynamique

Bonsoir le fil, Cibleo

Une solution parmis d'autres

Function test(ByVal Nom$, ByVal ch$, ByVal R As Range) As Integer
    Dim L%, C As Integer
    Dim A, V As Variant
    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
 
            For C = 1 To R.Columns.Count
                A = A & Trim(p.Offset(L, C).Value) & " "
            Next
 
        End If
    Next
 
    For Each V In Split(Trim(A), " ")
        If V = ch Then test = test + 1
    Next
 
End Function

ex. d'utilisation : =test("PASCAL","scanner",B2:M19)

Attention, je n'ai pas géré la casse. A adapter, donc.

A plus

 

[cibleo]

Re : VBA : Compter 1 occurence dans une plage dynamique

Bonjour à tous,
Bonjour soenda

Je vais approfondir les tests aujourd'hui.

Aves tes fonctions, je me suis aperçu que dans certains cas de figure le résultat était faussé. (même bug pour les 2 fonctions)

En résumé, il y a des retours à la ligne dans les cellules de la feuille "Synthese".

Exemple : dans une cellule j'ai ceci : RDV scanner CHU Nantes sur une ligne.

Le mot scanner est bien compté.

La même phrase sur 2 lignes avec retour à la ligne après "scanner" comme ceci :

RDV scanner
CHU Nantes

Le mot "scanner" n'est pas compté puisque la fonction doit considérer qu'il y a concaténation "scannerCHU"

Avec la fonction de Job 75, je ne rencontre pas de problème, le résultat est obtenu.

Sauf que si je prends le mot "Dupont" (6 caractères), elle ne fonctionne plus : résultat = rien.

Function F(plage As Range, t$)
Dim Cel As Range, i As Integer
F = 0
For Each Cel In plage
For i = 1 To Len(Cel)
[B]If Mid(Cel, i,[/B] [B][COLOR=red]7[/COLOR][/B]) = t Then F = F + 1
Next
Next
End Function

On est pas très loin du résultat escompté

Bonne journée à tous

Cibleo

 

[soenda]

Re : VBA : Compter 1 occurence dans une plage dynamique

Bonjour le fil, cibleo

La fonction Mid est très pratique, mais elle pose le problème des mots entiers, ex:
Si je veux compter le nombre de mot "pas" dans ce groupe (pas, pastèque, tapas)
- Résultat avec Mid => 3
- avec Concaténation => 1

Si le seul problème posée par la solution concaténation est le retour chariot,
alors je préconise une solution "rustine" du type :
- Si RetourChariot alors A <= A & " x "

A plus et merci pour ce retour inattendu mais très utile.

 

[cibleo]

Re : VBA : Compter 1 occurence dans une plage dynamique

Re à tous, soenda

Tu dis :

La fonction Mid est très pratique, mais elle pose le problème des mots entiers, ex:
Si je veux compter le nombre de mot "pas" dans ce groupe (pas, pastèque, tapas)
- Résultat avec Mid => 3

Oui, c'est exactement le résultat recherché, je me suis mal exprimé au départ

En B16 (fonction ---> compte) , j'aimerais obtenir 16 et non 14.

Ce que me renvoie bien B17 (fonction de job75 adapté pour une chaine de 7 caractères) mais non exploitable pour différentes longueurs de mots.

En C16 (fonction ---> test), j'aimerais obtenir 7 et non 5.
En effet, F6 présente 3 fois le mot "scanner", la fonction ne le compte qu'1 fois.

Pour bien visualiser le décompte des mots "scanner", je les ai surlignés dans la feuille "Synthese".

A nouveau le fichier.

Ps : dans la feuille "Synthese" ne pas tenir compte de la ligne 13, y a un bug, je n'ai pas encore regardé.

Bon dimanche Cibleo

 

[pierrejean]

Re : VBA : Compter 1 occurence dans une plage dynamique

bonjour cibleo
Salut a tous

Vois ligne 19 si mes comptes sont corrects (fonction personnalisées en module3)

 

[soenda]

Re : VBA : Compter 1 occurence dans une plage dynamique

Re,

La fonction "Compte" avec sa rustine qui tient compte des retourchariot.
(elle n'extrait que les mots complets)

Public Function Compte(ch As String, rng As Range) As Integer
    Dim V, A As Variant
    Dim S As String
    Dim C As Range
 
    For Each C In rng
        S = Replace(C.Value, Chr(10), " ")   [B][COLOR=seagreen]' la rustine, voir #10[/COLOR][/B] 
        A = A & Trim(S) & " "
    Next
 
    ch = LCase(ch)
    For Each V In Split(Trim(A), " ")
        If LCase(V) = ch Then Compte = Compte + 1
    Next
 
End Function

A plus

Edition : bonjour pierrejean

 

[cibleo]

Re : VBA : Compter 1 occurence dans une plage dynamique

Bonsoir pierrejean

J'ai placé tes 3 fonctions en bout de module1.

J'ai une erreur de déclariations de variable dans la fonction ci-dessous quand je teste avec mon fichier.

Un truc basique, mais là je commence à saturer

Peux-tu corriger le tir, puis tester mon fichier en faisant une recherche avec le mot "scanner" sur le mois de janvier, on devrait retrouver le résultat énoncé plus haut.

Function nb_dans(mot As String, dans As String)
nb_dans = 0
X = InStr(dans, mot)
While X <> 0
  dans = Left(dans, X - 1) & Right(dans, Len(dans) - X - Len(mot) + 1)
  nb_dans = nb_dans + 1
  X = InStr(dans, mot)
Wend
End Function

Le résultat doit s'afficher dynamiquement grâce aux instructions ci-dessous dans la procédure "remplirsynthese2".

Si je ne me suis pas planté dans la retranscription en VBA de tes fonctions.

'Ci-dessous j'empoie les fonctions personnalisées de pierrejean
 ShtR.Cells(DerLiR + 6, 2).FormulaR1C1 = "=compte_mots(""" & VSearch & """ , R2C" & 3 & ":R" & DerLiR & "C" & 13 & ")"
 ShtR.Cells(DerLiR + 6, 3).FormulaR1C1 = "=cpte_mots_pour(""" & VSearch & """ , R2C" & 2 & ":R" & DerLiR & "C" & 13 & ", ""PASCAL"")"
 ShtR.Cells(DerLiR + 6, 4).FormulaR1C1 = "=cpte_mots_pour(""" & VSearch & """ , R2C" & 2 & ":R" & DerLiR & "C" & 13 & ", ""SYLVIE"")"
 ShtR.Cells(DerLiR + 6, 5).FormulaR1C1 = "=cpte_mots_pour(""" & VSearch & """ , R2C" & 2 & ":R" & DerLiR & "C" & 13 & ", ""JOËLLE"")"
 ShtR.Cells(DerLiR + 6, 6).FormulaR1C1 = "=cpte_mots_pour(""" & VSearch & """ , R2C" & 2 & ":R" & DerLiR & "C" & 13 & ", ""JEAN-LUC"")"
 ShtR.Cells(DerLiR + 6, 7).FormulaR1C1 = "=cpte_mots_pour(""" & VSearch & """ , R2C" & 2 & ":R" & DerLiR & "C" & 13 & ", ""FRÉDÉRIQUE"")"
 ShtR.Cells(DerLiR + 6, 8).FormulaR1C1 = "=cpte_mots_pour(""" & VSearch & """ , R2C" & 2 & ":R" & DerLiR & "C" & 13 & ", ""FRÉDÉRIQUE"")"
 ShtR.Cells(DerLiR + 6, 9).FormulaR1C1 = "=cpte_mots_pour(""" & VSearch & """ , R2C" & 2 & ":R" & DerLiR & "C" & 13 & ", ""J-FRANÇOIS"")"
 ShtR.Cells(DerLiR + 6, 10).FormulaR1C1 = "=cpte_mots_pour(""" & VSearch & """ , R2C" & 2 & ":R" & DerLiR & "C" & 13 & ", ""A PRÉVOIR"")"

A+ Cibleo

Ps : pas vu soenda, vous allez trop vite en cette fin de week-end

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

 

[pierrejean]

Re : VBA : Compter 1 occurence dans une plage dynamique

Re

Il suffit d'ajouter Dim x as integer

Function nb_dans(mot As String, dans As String)
[COLOR=blue]Dim X as integer[/COLOR]
nb_dans = 0
X = InStr(dans, mot)
While X <> 0
  dans = Left(dans, X - 1) & Right(dans, Len(dans) - X - Len(mot) + 1)
  nb_dans = nb_dans + 1
  X = InStr(dans, mot)
Wend
End Function

Quant a ta transcription il faudrait avoir la macro complete pour tester

edit: Au temps pour moi : elle existe bel et bien mais comme je ne sais pas trop quand elle est utilisée !!
Si tu peux faire l'essai et revenir eventuellement si souci