XL 2016 NB.SI et Indirect Vba

[Fipat]

Bonjour,

Je recherche à extraire des noms dans une autre feuille, pour cela ça fonctionne et je veux compter le nombre de noms en même temps.
Mais je me suis embrouillé il semble dans la formule de ma macro.
Je joins un fichier exemple.
Merci pour votre aide.

 

[Staple1600]

Bonsoir

Pourquoi pas tout simplement un TCD?

 

[Fipat]

Bonsoir Staple1600,

Merci pour ta réponse
Je désire passer par le vba car en réalité j'ai un fichier avec plusieurs feuilles qui traitent des données.
Je fais plusieurs actions depuis une feuille que j'ai nommé tableau de bord et cela avec une trentaine de macro.
Elles affichent certains éléments et lance également des userform, bref tout un tas d'action.
Oui le TCD serait tellement plus simple, mais dans le cas de mon fichier j'ai besoin de la faire en vba.
Merci.

 

[Staple1600]

Re

Dans ce cas, pourquoi ne pas décrire précisément dans le 1er message ce que tu viens de décrire dans le 3ième?
Et de joindre un fichier exemple plus étoffé.
Pas vue d'userform dans ton fichier exemple...

 

[mapomme]

Bonsoir à tous,

Pourquoi chercher une formule alors que le dictionary peut tout faire ??
Essayez ceci:

Sub test()
Dim derlig&, i&, n&, DReport, clef

Set DReport = CreateObject("Scripting.Dictionary")
DReport.CompareMode =vbTextCompare
   With Sheets("Feuil1")
      If .FilterMode Then .ShowAllData
      derlig = .Cells(.Rows.Count, "d").End(xlUp).Row
      For i = 2 To derlig
         clef = .Cells(i, 4).Value
         If clef <> "" Then DReport(clef) = DReport(clef) + 1
      Next i
      n = DReport.Count
      Sheets("Feuil2").Range("a2:b" & .Rows.Count).Clear
      If n > 0 Then
         Sheets("Feuil2").Cells(2, 1).Resize(n, 1) = Application.Transpose(DReport.Keys)
         Sheets("Feuil2").Cells(2, 2).Resize(n, 1) = Application.Transpose(DReport.items)
      End If
   End With
End Sub

ou si une grande quantité de données :

Sub testBIS()
Dim derlig&, t, i&, n&, DReport, clef

Set DReport = CreateObject("Scripting.Dictionary")
DReport.CompareMode = vbTextCompare
   With Sheets("Feuil1")
      If .FilterMode Then .ShowAllData
      derlig = .Cells(.Rows.Count, "d").End(xlUp).Row + 1
      t = .Range("d1:d" & derlig)
      For i = 2 To UBound(t)
         clef = CStr(t(i, 1))
         If clef <> "" Then DReport(clef) = DReport(clef) + 1
      Next i
      n = DReport.Count
      Sheets("Feuil2").Range("a2:b" & .Rows.Count).Clear
      If n > 0 Then
         Sheets("Feuil2").Cells(2, 1).Resize(n, 1) = Application.Transpose(DReport.Keys)
         Sheets("Feuil2").Cells(2, 2).Resize(n, 1) = Application.Transpose(DReport.items)
      End If
   End With
End Sub

 

[Fipat]

Désolé Staple1600,

Mais il me semblais bien de faire simple et de cibler simplement ou ça coincé pour moi.
Je viens de trouver une parade.
Ma référence de recherche été erroné (mauvaise recherche).
J'ai donc rajouté une référence à la feuille qui reçois les données et cela fonctionne.
Je joins toujours le fichier qui désole encore une fois et simpliste mais cible bien la recherche.
Merci pour ces échanges. Bonne soirée

 

[Fipat]

Bonsoir mapomme,

J'ai testé ta solution et j'ai une erreur j'ai donc rajouté :

Dim TextCompare As String

Mais idem, donc j'ai fait par curiosité :

Sub test2()
Dim derlig&, i&, n&, DReport, clef
Dim TextCompare
Set DReport = CreateObject("Scripting.Dictionary")
DReport.CompareMode = TextCompare
   With Sheets("Feuil1")
      If .FilterMode Then .ShowAllData
      derlig = .Cells(.Rows.Count, "d").End(xlUp).Row
      For i = 2 To derlig
         clef = .Cells(i, 4).Value
         If clef <> "" Then DReport(clef) = DReport(clef) + 1
      Next i
      n = DReport.Count
      Sheets("Feuil2").Range("a2:b" & .Rows.Count).Clear
      If n > 0 Then
         Sheets("Feuil2").Cells(2, 1).Resize(n, 1) = Application.Transpose(DReport.Keys)
         Sheets("Feuil2").Cells(2, 2).Resize(n, 1) = Application.Transpose(DReport.items)
      End If
   End With
End Sub

Et là, aucun souci, cela marche sans problème.
Comment une variable déclarer ainsi peut donner un résultat.
Désolé si ma question semble idiote, mais je suis un jeune fouineur
En tout merci pour ces échanges.

 

[Staple1600]

Bonsoir mapomme

=>Fipat
Ne sois pas désolé.
Je disais simplement qu'en posant la question détaillée dès le départ, c'est plus simple pour les répondeurs

 

[Fipat]

J'ai bien compris Staple1600 et je te remercie d'avoir participer à ma demande
Par contre selon la réponse de mapomme, je ne comprends pas le fait de déclarer une variable sans "attribu" si on le dit ainsi.
Pour moi une variable doit toujours avoir un as et quelque chose.
Bon en tout cas c'est bien sympa, car j'apprends avec vous
Merci.

 

[mapomme]

omment une variable déclarer ainsi peut donner un résultat.

C'est une coquille dans mon code. J'ai oublié le vb devant le nom.

Il faut virer la ligne que tu as ajoutée et remplacer l'instruction :
DReport.CompareMode = TextCompare
par :
DReport.CompareMode = vbTextCompare

vbTextCompare est une constante (et pas une variable) qui est déclarée au sein de la bibliothèque Microsoft Scripting Runtime qui contient l'objet dictionary. D'ailleurs vbTextCompare est égale à 1.

 

[Fipat]

Effectivement cela marche sans souci
Désolé de faire mon chiant, une constante marquée par vb ?
je ne comprends pas.

 

[mapomme]

Re @Fipat, @Staple1600,

Effectivement cela marche sans souci
Désolé de faire mon chiant, une constante marquée par vb ?
je ne comprends pas.

Je n'y peux rien. C'est le nom de la constante. Microsoft nomme ses constantes comme il veux.

En fait c'est plus "sioux" que ça.

Dans la bibliothèque "Scripting Runtime", la classe de l'objet dictionary comporte une propriété nommée "CompareMode" qui permet de préciser comment faire les comparaison de texte. Cette propriété peut prendre 3 valeurs : BinaryCompare (zéro), DatabaseCompare (deux), TextCompare (un).
BinaryCompare distingue les majuscules des minuscules, TextCompare ignore la casse et DatabaseCompare est généralement réservée à Access.

Ces constantes sont définies dans la classe dictionary de Scripting Runtime et sont à priori inconnues de VBA sauf si on indique à VBA où chercher.

Si on fait du "late binding" en utilisant CreateObject(...), les constantes sont inconnues de VBA.

Si on veut que VBA connaissent le dictionary et propose les propriétés et leur valeur au moment d'écrire le code, il faut le faire par un "early binding". Pour cela, dans l'environnement VBE, on sélectionne le menu Outils puis Référence... et on coche la ligne "Microsoft Scripting Runtime".

Quand on revient au code et qu'on tape" DReport.comparemode=", VBA propose la liste des trois valeurs.

J'ai utilisé ici une astuce sournoise. La constante TextCompare n'existe pas dans VBA mais il existe une constante VBA qui s'appelle vbTextCompare et qui a la même valeur (1).
Donc en faisant du "late binding", l'instruction DReport.CompareMode = TextCompare ne passe pas.
Mais si à la place, on indique la constante VBA vbTextCompare et qui possède la même valeur, on gruge VBA.

Le mieux est encore d'écrire : DReport.CompareMode = 1 .

 

[Fipat]

Oups je viens de comprendre, d'en mon engouement d'aller plus vite que la musique, je me suis perdu.
Ou trouver ces bibliothèques ?

 

[mapomme]

Ou trouver ces bibliothèques ?

Cette bibliothèque s'appelle "Microsoft Scripting Runtime".
La méthode pour indiquer à VBA de référencer cette blibliothèque est décrite dans mon dernier message:

• aller dans l'environnement VBA
• choisir le menu Outils/ Références...
• on coche la ligne "Microsoft Scripting Runtime"
• puis OK

 

[Fipat]

J'ai appris les classes Windows et les UDF.
Es-ce la même logique ?
Je vais regarder de plus prêt tes recommandations.
Merci à vous