XL 2016 Macro pour remplacer RechercheV sur beaucoup de criteres de recherche

[CedGP]

Bonjour A toutes et a tous

je suis un peu en panne niveau VBA sur lequel je n'ai pas bossé depuis une dizaine d'année et je cherche a faire une action qui pourtant me parait simple mais je bloque
j'ai recherché sur d'ancienne macro pour essayer de me renouveler mais je n'y arrive pas
Je m'explique


J'ai un fichier que j'importe mensuellement en le collant dans l'onglet "Depenses"
Les libellés produits (colonne P) sont particulièrement diffus pour une meme categorie de produit

Je cherche a ce que la macro "remplace" une formule Si qui a ses limites en terme de conditions

Concretement

Si dans l'onglet "depenses" colonne P il y a une mention, je souhaiterai que la donnée soit recherchée dans l'onglet "Categories" Colonne A et me reporte l'info de la colonne B de cet onglet dans l'onglet "depenses" Colonne Z

A Noter ce fichier est synthetique et comprends enormément de lignes

Si quelqu'un a quelques minutes a me consacrer ca serait sympa

Merci d'avance

céd

 

[mapomme]

Bonjour,

Pourquoi du VBA ? Une formule devrait suffire:

=RECHERCHEV(P2;Categories!$A$1:$B$99;2;FAUX)

 

[CedGP]

Bonjour,

Pourquoi du VBA ? Une formule devrait suffire:

=RECHERCHEV(P2;Categories!$A$1:$B$99;2;FAUX)

Bonjour Mapomme merci ( je vais deja tester de ce coté là)

alors deja je ne pensais pas que via une rehcercheV je pouvais en mettre autant en faisant des groupements ( merci pour ma connaissance perso ;-) ) mais sachant que chaque moi s j'ecrase le tableau depense pour le remplacer par un reprenant l'anteriorité + toutes les nouvelles utilisation de mes cartes, je trouvais que mettre un simple bouton pour faire l'ensemble de la manip etait plus ergonomique pour mes collegues

 

[CedGP]

Bonjour Mapomme merci ( je vais deja tester de ce coté là)

alors deja je ne pensais pas que via une rehcercheV je pouvais en mettre autant en faisant des groupements ( merci pour ma connaissance perso ;-) ) mais sachant que chaque moi s j'ecrase le tableau depense pour le remplacer par un reprenant l'anteriorité + toutes les nouvelles utilisation de mes cartes, je trouvais que mettre un simple bouton pour faire l'ensemble de la manip etait plus ergonomique pour mes collegues

En tout cas cette solution fonctionne parfaitement, seule la contrainte du report de formule a chaque modif qui me pose probleme

Le contexte est que je susi en train de goupillé un Enorme powerBI et j'essaye de fluidifier toutes les sources de ce dernier en faisant le minimum d'action ( perso je touche un peu a tout ca mais mes collegues sont en mode hyper debutant sur les outils excel and co) d'ou la demande en VBA ;-)

 

[mapomme]

Re,

Par VBA. Cliquer sur le bouton Hop! de la cellule Z1 dans la Feuilles "Depenses".
Je ne commente jamais mes codes sauf si on me le demande.

nota : si le produit est inconnu, on reourne la valeur d'erreur #N/A

Le code dans Module1 :

Sub Catégories()
Dim t, dicoCateg As New Dictionary, i&
With Sheets("categories")
   If .FilterMode Then .ShowAllData
   t = .Range(.Range("a1"), .Cells(.Rows.Count, "b").End(xlUp))
   Set dicoCateg = CreateObject("scripting.dictionary")
   dicoCateg.CompareMode = TextCompare
   For i = UBound(t) To 2 Step -1: dicoCateg(t(i, 1)) = t(i, 2): Next
End With
With Sheets("Depenses")
   If .FilterMode Then .ShowAllData
   t = .Range(.Range("p2"), .Cells(.Rows.Count, "p").End(xlUp))
   For i = 1 To UBound(t): t(i, 1) = IIf(dicoCateg.Exists(t(i, 1)), dicoCateg(t(i, 1)), CVErr(xlErrNA)): Next
   .Range("z2:z" & Rows.Count).ClearContents
   .Range("z2").Resize(UBound(t)) = t
End With
End Sub

 

[CedGP]

Re,

Par VBA. Cliquer sur le bouton Hop! de la cellule Z1 dans la Feuilles "Depenses".
Je ne commente jamais mes codes sauf si on me le demande.
Le code dans Module1 :

Sub Catégories()
Dim t, dicoCateg As New Dictionary, i&
With Sheets("categories")
   If .FilterMode Then .ShowAllData
   t = .Range(.Range("a1"), .Cells(.Rows.Count, "b").End(xlUp))
   Set dicoCateg = CreateObject("scripting.dictionary")
   dicoCateg.CompareMode = TextCompare
   For i = UBound(t) To 2 Step -1: dicoCateg(t(i, 1)) = t(i, 2): Next
End With
With Sheets("Depenses")
   If .FilterMode Then .ShowAllData
   t = .Range(.Range("p2"), .Cells(.Rows.Count, "p").End(xlUp))
   For i = 1 To UBound(t): t(i, 1) = IIf(dicoCateg.Exists(t(i, 1)), dicoCateg(t(i, 1)), CVErr(xlErrNA)): Next
   .Range("z2:z" & Rows.Count).ClearContents
   .Range("z2").Resize(UBound(t)) = t
End With
End Sub

je dois m'absenter quelques heures je regarde ca a mon retour et je te dis ( si je ne comprends pas une partie ) Encore merci

 

[CedGP]

Re,

Par VBA. Cliquer sur le bouton Hop! de la cellule Z1 dans la Feuilles "Depenses".
Je ne commente jamais mes codes sauf si on me le demande.

nota : si le produit est inconnu, on reourne la valeur d'erreur #N/A

Le code dans Module1 :

Sub Catégories()
Dim t, dicoCateg As New Dictionary, i&
With Sheets("categories")
   If .FilterMode Then .ShowAllData
   t = .Range(.Range("a1"), .Cells(.Rows.Count, "b").End(xlUp))
   Set dicoCateg = CreateObject("scripting.dictionary")
   dicoCateg.CompareMode = TextCompare
   For i = UBound(t) To 2 Step -1: dicoCateg(t(i, 1)) = t(i, 2): Next
End With
With Sheets("Depenses")
   If .FilterMode Then .ShowAllData
   t = .Range(.Range("p2"), .Cells(.Rows.Count, "p").End(xlUp))
   For i = 1 To UBound(t): t(i, 1) = IIf(dicoCateg.Exists(t(i, 1)), dicoCateg(t(i, 1)), CVErr(xlErrNA)): Next
   .Range("z2:z" & Rows.Count).ClearContents
   .Range("z2").Resize(UBound(t)) = t
End With
End Sub

 

[CedGP]

Re,

Par VBA. Cliquer sur le bouton Hop! de la cellule Z1 dans la Feuilles "Depenses".
Je ne commente jamais mes codes sauf si on me le demande.

nota : si le produit est inconnu, on reourne la valeur d'erreur #N/A

Le code dans Module1 :

Sub Catégories()
Dim t, dicoCateg As New Dictionary, i&
With Sheets("categories")
   If .FilterMode Then .ShowAllData
   t = .Range(.Range("a1"), .Cells(.Rows.Count, "b").End(xlUp))
   Set dicoCateg = CreateObject("scripting.dictionary")
   dicoCateg.CompareMode = TextCompare
   For i = UBound(t) To 2 Step -1: dicoCateg(t(i, 1)) = t(i, 2): Next
End With
With Sheets("Depenses")
   If .FilterMode Then .ShowAllData
   t = .Range(.Range("p2"), .Cells(.Rows.Count, "p").End(xlUp))
   For i = 1 To UBound(t): t(i, 1) = IIf(dicoCateg.Exists(t(i, 1)), dicoCateg(t(i, 1)), CVErr(xlErrNA)): Next
   .Range("z2:z" & Rows.Count).ClearContents
   .Range("z2").Resize(UBound(t)) = t
End With
End Sub

HEllo encore un grand merci je l'ai adapté sur mon fichier courant et apres quelques coquilles de mon coté ca marche ... et je pense avoir saisi la globalité du code ( que j'aurai été incapable a mettre en oeuvre) encore un gros merci @mapomme

 

[mapomme]

Re,

Oups! C'est ma très grande faute. J'explique :

On utilise une structure nommé dictionary (spécifique à windows - non disponible sur MAC).
Cette structure associe à une clef (qui peut-être n'importe quelle chaine de caractères) un item qui peut être un peu n'importe quoi (nombre, texte, array, range, un autre dictionnaire, etc.).

Cet objet dictionary n'accepte pas les clefs en doublon
La recherche d'une clef (et par conséquent de l'item associé) parmi l'ensemble des clefs est très très rapide.

Ici la clef est le "type de dépense" (colonne A de la feuille "Categories") est l'item associée à chaque clef est sa catégorie (colonne B de la feuille "Categories").

Mais pour pouvoir utiliser cette structure dictionary, il faut indiquer au "compilateur VBA" dans quelle bibliothèque se trouve cette structure. Pour cela, deux possibilités:

La première consiste à aller dans l'éditeur VBE, à lier la référence au moyen de l'éditeur puis à coder.

Dans ce cas, on déclare une variable comme étant de type dictionary en écrivant
Dim dico as new dictionary.

Un avantage de faire ainsi, c'est que pendant l'écriture du code, l'éditeur VBE propose les propriétés et procédures liées à cet objet.

L'inconvénient est qu'il faut lier la bibliothèque dans VBE avant de coder. Cela s'appelle du "early binding".

Un autre avantage de cette méthode, c'est que les opérations sur la variable dictionary sont plus rapides par rapport à le seconde (souvent cela ne se perçoit guère si peu de données).

Une fois la bibliothèque liée, si le classeur est transféré tel quel sur un autre ordinateur (même sans avoir lié la bibliothèque sur le nouvel ordinateur), le fichier fonctionne.

Mais si on ne copie que le code du classeur dans un autre, alors il faut absolument faire cette liaison dans le second classeur (une seule fois suffit).

Pour ton erreur, c'est sans doute ce qui est t'est arrivé.


La deuxième méthode (dite "late binding") pour lier la bibliothèque est la suivante:

• On déclare une variable dico (sans type explicite) : Dim dico
• Et dans le corps de la procédure, on écrit avant d'utiliser la variable dico :
  Set dicoCateg = CreateObject("scripting.dictionary")

Cette méthode n° 2 a comme avantages les inconvénients de la première et vice-versa:

• on n'a pas besoin de lier la bibliothèque manuellement dans l'éditeur VBE. On peut donc copier le code dans un autre classeur sans se soucier si cette liaison existe ou non
• Elle n'apporte pas l'auto-complétion pendant l'écriture du code pour la variable dico (il faut connaitre les propriétés et procédure de cet objet)
• elle est un petit moins rapide si on a un très très grand nombre d'éléments dans le dictionary

Dans un moment d'égarement , j'ai "doublonné" en déclarant le dictionary dans les déclarations en début de code (dicoCateg As New Dictionary) puis reconstruisant la variable dictionary dans le corps du code (Set dicoCateg = CreateObject("scripting.dictionary")).

Il y en a une inutilement en trop. A moins d'avoir des centaines de milliers de lignes, tu peux utiliser la méthode "late-binding" et donc déclarer simplement en tête de code Dim dico et conserver dans le code la ligne : Set dicoCateg = CreateObject("scripting.dictionary")

Si tu veux la première méthode ("ealy-binding"), suivre la démo en pièce jointe pour faire la liaison.

Voilà, voilou...

 

[CedGP]

Re,

Oups! C'est ma très grande faute. J'explique :

On utilise une structure nommé dictionary (spécifique à windows - non disponible sur MAC).
Cette structure associe à une clef (qui peut-être n'importe quelle chaine de caractères) un item qui peut être un peu n'importe quoi (nombre, texte, array, range, un autre dictionnaire, etc.).

Cet objet dictionary n'accepte pas les clefs en doublon
La recherche d'une clef (et par conséquent de l'item associé) parmi l'ensemble des clefs est très très rapide.

Ici la clef est le "type de dépense" (colonne A de la feuille "Categories") est l'item associée à chaque clef est sa catégorie (colonne B de la feuille "Categories").

Mais pour pouvoir utiliser cette structure dictionary, il faut indiquer au "compilateur VBA" dans quelle bibliothèque se trouve cette structure. Pour cela, deux possibilités:

La première consiste à aller dans l'éditeur VBE, à lier la référence au moyen de l'éditeur puis à coder.

Dans ce cas, on déclare une variable comme étant de type dictionary en écrivant
Dim dico as new dictionary.

Un avantage de faire ainsi, c'est que pendant l'écriture du code, l'éditeur VBE propose les propriétés et procédures liées à cet objet.

L'inconvénient est qu'il faut lier la bibliothèque dans VBE avant de coder. Cela s'appelle du "early binding".

Un autre avantage de cette méthode, c'est que les opérations sur la variable dictionary sont plus rapides par rapport à le seconde (souvent cela ne se perçoit guère si peu de données).

Une fois la bibliothèque liée, si le classeur est transféré tel quel sur un autre ordinateur (même sans avoir lié la bibliothèque sur le nouvel ordinateur), le fichier fonctionne.

Mais si on ne copie que le code du classeur dans un autre, alors il faut absolument faire cette liaison dans le second classeur (une seule fois suffit).

Pour ton erreur, c'est sans doute ce qui est t'est arrivé.


La deuxième méthode (dite "late binding") pour lier la bibliothèque est la suivante:

• On déclare une variable dico (sans type explicite) : Dim dico
• Et dans le corps de la procédure, on écrit avant d'utiliser la variable dico :
  Set dicoCateg = CreateObject("scripting.dictionary")

Cette méthode n° 2 a comme avantages les inconvénients de la première et vice-versa:

• on n'a pas besoin de lier la bibliothèque manuellement dans l'éditeur VBE. On peut donc copier le code dans un autre classeur sans se soucier si cette liaison existe ou non
• Elle n'apporte pas l'auto-complétion pendant l'écriture du code pour la variable dico (il faut connaitre les propriétés et procédure de cet objet)
• elle est un petit moins rapide si on a un très très grand nombre d'éléments dans le dictionary

Dans un moment d'égarement , j'ai "doublonné" en déclarant le dictionary dans les déclarations en début de code (dicoCateg As New Dictionary) puis reconstruisant la variable dictionary dans le corps du code (Set dicoCateg = CreateObject("scripting.dictionary")).

Il y en a une inutilement en trop. A moins d'avoir des centaines de milliers de lignes, tu peux utiliser la méthode "late-binding" et donc déclarer simplement en tête de code Dim dico et conserver dans le code la ligne : Set dicoCateg = CreateObject("scripting.dictionary")

Si tu veux la première méthode ("ealy-binding"), suivre la démo en pièce jointe pour faire la liaison.

Voilà, voilou...

Je vais checker ca plus en detail car je pense que ca pourra me re servir

J’avoue que du coup j’ai transposé avec ton fichier initial et y ai recollé mes donnees pour gagner du temps et avancer sur mon powerbi

En tout cas un gros merci promis je regarde le detail de ce que tu m’as indiqué et si j’ai des lacunes je le permettrais de revenir vers toi pour l’explication

Dans tout les cas un grans merci

 

[cp4]

Re,

Oups! C'est ma très grande faute. J'explique :

On utilise une structure nommé dictionary (spécifique à windows - non disponible sur MAC).
Cette structure associe à une clef (qui peut-être n'importe quelle chaine de caractères) un item qui peut être un peu n'importe quoi (nombre, texte, array, range, un autre dictionnaire, etc.).

Cet objet dictionary n'accepte pas les clefs en doublon
La recherche d'une clef (et par conséquent de l'item associé) parmi l'ensemble des clefs est très très rapide.

Ici la clef est le "type de dépense" (colonne A de la feuille "Categories") est l'item associée à chaque clef est sa catégorie (colonne B de la feuille "Categories").

Mais pour pouvoir utiliser cette structure dictionary, il faut indiquer au "compilateur VBA" dans quelle bibliothèque se trouve cette structure. Pour cela, deux possibilités:

La première consiste à aller dans l'éditeur VBE, à lier la référence au moyen de l'éditeur puis à coder.

Dans ce cas, on déclare une variable comme étant de type dictionary en écrivant
Dim dico as new dictionary.

Un avantage de faire ainsi, c'est que pendant l'écriture du code, l'éditeur VBE propose les propriétés et procédures liées à cet objet.

L'inconvénient est qu'il faut lier la bibliothèque dans VBE avant de coder. Cela s'appelle du "early binding".

Un autre avantage de cette méthode, c'est que les opérations sur la variable dictionary sont plus rapides par rapport à le seconde (souvent cela ne se perçoit guère si peu de données).

Une fois la bibliothèque liée, si le classeur est transféré tel quel sur un autre ordinateur (même sans avoir lié la bibliothèque sur le nouvel ordinateur), le fichier fonctionne.

Mais si on ne copie que le code du classeur dans un autre, alors il faut absolument faire cette liaison dans le second classeur (une seule fois suffit).

Pour ton erreur, c'est sans doute ce qui est t'est arrivé.


La deuxième méthode (dite "late binding") pour lier la bibliothèque est la suivante:

• On déclare une variable dico (sans type explicite) : Dim dico
• Et dans le corps de la procédure, on écrit avant d'utiliser la variable dico :
  Set dicoCateg = CreateObject("scripting.dictionary")

Cette méthode n° 2 a comme avantages les inconvénients de la première et vice-versa:

• on n'a pas besoin de lier la bibliothèque manuellement dans l'éditeur VBE. On peut donc copier le code dans un autre classeur sans se soucier si cette liaison existe ou non
• Elle n'apporte pas l'auto-complétion pendant l'écriture du code pour la variable dico (il faut connaitre les propriétés et procédure de cet objet)
• elle est un petit moins rapide si on a un très très grand nombre d'éléments dans le dictionary

Dans un moment d'égarement , j'ai "doublonné" en déclarant le dictionary dans les déclarations en début de code (dicoCateg As New Dictionary) puis reconstruisant la variable dictionary dans le corps du code (Set dicoCateg = CreateObject("scripting.dictionary")).

Il y en a une inutilement en trop. A moins d'avoir des centaines de milliers de lignes, tu peux utiliser la méthode "late-binding" et donc déclarer simplement en tête de code Dim dico et conserver dans le code la ligne : Set dicoCateg = CreateObject("scripting.dictionary")

Si tu veux la première méthode ("ealy-binding"), suivre la démo en pièce jointe pour faire la liaison.

Voilà, voilou...

Bonjour @CedGP , @mapomme ,

J'ai suivi cette discussion "en arrière plan", car ne m'étant pas encore mis sérieusement à étudier les collections. Je fais des efforts pour mieux comprendre et utiliser les dictionnaires.
Merci beaucoup @mapomme pour tes explications. Cependant, sur mon taco win7 et excel2010, le code plante (Démo en Gif). En modifiant TextCompare par vbTextCompare le code ne plante plus.

@CedGP : As-tu eu me même plantage que moi?


ps: j'ai pris l'habitude de déclarer Dico as Object

Bonne journée.

 

[CedGP]

Non

Bonjour @CedGP , @mapomme ,

J'ai suivi cette discussion "en arrière plan", car ne m'étant pas encore mis sérieusement à étudier les collections. Je fais des efforts pour mieux comprendre et utiliser les dictionnaires.
Merci beaucoup @mapomme pour tes explications. Cependant, sur mon taco win7 et excel2010, le code plante (Démo en Gif). En modifiant TextCompare par vbTextCompare le code ne plante plus.

@CedGP : As-tu eu me même plantage que moi?
Regarde la pièce jointe 1135078

ps: j'ai pris l'habitude de déclarer Dico as Object

Bonne journée.

moi ca plantait des la seconde ligne de code