Récupérer chaine de caractères

Bonjour,

Tout d'abord, j'ai un petit niveau de connaissance avec Excel et ses macros.

Depuis un fichier Excel, je dois faire un traitement afin de récupérer les lignes d'un tableau qui contiendrait les "références" que je recherche.

J'ai besoin de rechercher dans le contenu de chaque cellule d'un tableau si une de ses 180 références que je recherche est présente (toutefois, ses références peuvent être noyées dans le texte d'une cellule) et ensuite j'ai besoin de faire quelques traitements.

Dans ce fichier j'ai 2 feuilles :

-> Sur la feuille 1, j'ai un tableau1, "(A1:AM15000 avec en-tête)". Dans ce tableau1, les colonnes contiennent des données comme par exemple des dates, du texte, des références...etc, c'est assez diversifié.

-> Sur la feuille2, j'ai un tableau2 "(A1:AM15000 avec en-tête)" identique au tableau1 mais qui est vide et un 2ième tableau qui contient la liste des 180 références que je recherche, qui sont du type : 12AZ158963, 101457896, 123456-2, 123ab456 (je pense mettre ce tableau en feuille2("AL1:AL181" avec en-tête), mais à voir où il serait plus judicieux de placer ce tableau (enfin c'est qu'une colonne)).

Après avoir étudié le problème, je penserais faire de la manière suivante :

Sub Macro1()
Dim x As Long
Dim y As Long
Dim cell As Range
Dim tableau1 As Range
Dim tableau2 As Range
Dim tableau180ref As Range

' Déterminer la dernière ligne de la Feuille 1 et la dernière ligne de la Feuille 2 + 1
x = Feuil1.Range("A65536").End(xlUp).Row
y = Feuil2.Range("A65536").End(xlUp).Row + 1

' Déterminer la Plage de référence du tableau1, tableau2, tableau3
Set tableau1 = Feuil1.Range("A2:AM15000")
Set tableau2 = Feuil2.Range("A2:AM15000")
Set tableau180ref = Feuil2.Range("AL2:AL181")

' Effacer les anciennes lignes des traitements précédent dans tableau2
If tableau2 >= 2 Then tableau2.ClearContents

' Boucle pour rechercher les 180 références
For Each cell In tableau180ref

     -> Je récupère la chaine de caractère de la 1er cellule du tableau180ref Feuil2.Range("AL2")
     -> Je recherche si la chaine de caractère est présente dans le contenu de chaque cellules du tableau1
     -> Si oui, je modifie la police de la chaine de caractère trouvé dans le tableau1 en gras et rouge (uniquement cette chaine et pas tout le texte d'une cellule)
     -> Et je rempli la cellule où la chaine caractère a été trouvée dans tableau1, en jaune
     End If

'Je passe à la 2ieme cellule du tableau180ref et jusqu'à avoir vérifié toutes les références du tableau180ref.
Next cell

' Boucle pour rechercher les cellules rempli du tableau1
For Each Row In tableau1

-> Si une ou plusieurs cellules sont rempli en jaune alors
-> Copier la ligne et coller dans tableau2 colonne "A"
-> Trouver la prochaine ligne vide du tableau2
 End If

' Recalculer la première ligne libre de tableau2
tableau2 = tableau2.End(xlUp).Row + 1

'Je passe à la 2ieme ligne du tableau1 et jusqu'à avoir vérifié toutes les lignes du tableau1.
Next Row

End Sub

Pourriez-vous m'aider à compléter cette macro, ca fait quelques temps que je suis là-dessus sans trouver la solution (transformer ma pensée en code VBA ).

Merci par avance.

J'ai ajouté quelques lignes dans la macro, mais bien évidement ca ne fonctionne pas... je me rend bien compte que je suis complétement à la ramasse :

Sub Copier2()
Dim x As Long
Dim y As Long
Dim cell As Range
Dim tableau1 As Range
Dim tableau2 As Range
Dim tableau180ref As Range
Dim strtableau180ref As String
Dim strtableau1 As String
Dim strcheck As String

' Déterminer la dernière ligne de la Feuille 1 et la dernière ligne de la Feuille 2 + 1?
x = Feuil1.Range("A65536").End(xlUp).Row
y = Feuil2.Range("A65536").End(xlUp).Row + 1

' Déterminer la Plage de référence du tableau1, tableau2, tableau180ref
Set tableau1 = Feuil1.Range("A2:AM15000")
Set tableau2 = Feuil2.Range("A2:AM15000")
Set tableau180ref = Feuil2.Range("AQ2:AQ181")
Set strtableau180ref = Feuil2.Columns(38, 2) 'Détermine la plage de String
Set strtableau1 = Feuil1.Range("A2:AM15000") 'Détermine la plage de String

' Effacer les anciennes lignes des traitements précédent dans tableau2
If tableau2 >= 2 Then tableau2.ClearContents

' Boucle pour rechercher les 180 références
For Each cell In tableau180ref

    ' Je compare la chaine de caractère de la cellule du tableau180ref
    strcheck = StrComp(strtableau180ref, strtableau1)

    ' Si la chaine de caractère est identique
    If strcheck = 0 Then

    ' Alors,je modifie la police de la chaine de caractère trouvé dans le tableau1 en gras et rouge (et uniquement cette chaine, pas l'ensemble du texte de la cellule)
    cell.String.ColorIndex = vbRed

    'Je rempli la cellule où la chaine a été trouvée dans tableau1, en jaune
    cell.Interior.Color = vbYellow

    End If

'Je passe à la 2ieme cellule du tableau180ref et jusqu'à avoir vérifié toutes les références du tableau180ref.
Next cell

' Boucle pour rechercher les cellules rempli du tableau1?
For Each Row In tableau1

'-> Si une ou plusieurs cellules sont rempli en jaune alors
'-> Copier la ligne et coller dans tableau2 colonne "A"
    If cell.Interior.Color = vbYellow Then
    tableau1.Range("A" & cell.Row & ":AM" & cell.Row).Copy Destination:=tableau2.Range("A" & y)

' Recalculer la première ligne libre de tableau2
    tableau2 = tableau2.End(xlUp).Row + 1

    End If

'Je passe à la 2ieme ligne du tableau1 et jusqu'à avoir vérifié toutes les lignes du tableau1.
Next Row

End Sub

Et désolé si mon code vous brule les yeux

Merci pour l'aide que vous pourrez m'apporter.

Bonjour

je ne sais pas si j'ai bien compris la demande..... mais voilà une proposition et qui a le fonctionnement que j'ai compris :

Il y a 2 macros :

• une 1e macro qui recherche une valeur unique (présente en AO2 de la feuil2)
• une 2e macro qui va faire la recherche de toutes les valeurs qui sont dans la liste dans la colonne AQ..

les deux macros ont le même principe, on regarde ligne par ligne du tableau de la feuille 1 et on recherche la valeur souhaitée dans toutes les cellules de cette ligne et si la valeur recherchée est trouvée alors on copie la ligne entière dans la feuille 2...

Fred

Si il faut mettre des couleurs dans les cellules... je verrais cela plus tard... car je ne sais pas si la mise en couleur était ta méthode d'identification des lignes concernées...

Fred

Hello Fred,

Merci beaucoup pour ton aide.

Finalement, j'était partis sur une autre manière de faire, qui ne fonctionne toujours pas totalement : test-excel-copie.xlsm

Autrement, Il y a bien 2 macros :

1er macro : recherche sur une valeur unique, je colorie en rouge la cellule où la valeur a été trouvée et je copie la ligne concernée dans le tableau de la feuille2 (en évitant les doublons lors des copies de lignes, car dans mon code si 2 valeurs sont trouvée sur une même ligne et cela me copie 2 fois la même ligne)

2-ieme macro : même traitement que macro1 sauf que cette fois, comme tu l'as bien compris, je souhaite faire la recherche sur toutes les valeurs qui sont dans la liste dans la colonne AQ .

Pour les macros, je dois faire la recherche dans le contenu de la cellule, car si tu regardes le fichier ci-joint sur la feuille 1 en "A8", la cellule contient du texte et dans ce texte une référence que je recherche est présente et finalement je n'arrive pas à détecter ces cellules, à colorier la cellule et copié la ligne dans le tableau de la feuiile2.

J'ai essayé le fichier que tu as joint, merci pour cela, mais j'obtiens une erreur "incompatibilité de type" que je ne comprend pas sur la même ligne des 2 macros :

If Not IsError(EstDans) Then Feuil2.Range("A" & i).Resize(, UBound(tbs, 2)) = Application.Index(tbs, lg): i = i + 1

Merci par avance.

Pour les couleurs du texte, j'ai abandonnée l'idée, c'est bien trop compliqué pour moi

Déjà si j'arrive a obtenir le résultat voulu, en remplissant de couleur rouge les cellules où les valeurs recherchées sont trouvées + la copie des lignes dans la feuille2 et sans doublons ca serais vraiment formidable.

Re bonjour...

Dans ton 2e fichier je ne vois le code que j'ai fournit....

de plus il y a des choses qui ne vont pas... par rapport au premier...

Dans le premier fichier tu avais des tableaux structurés... définit dans le gestionnaire de nom... chose que tu n'as plus dans le 2e fichier... hors moi je me suis basé la dessus...

Si je copie/colle en valeur les données de ton 2e fichier dans le fichier que j'ai fourni.... je ne rencontre pas d'erreur... donc comprend pas....

sinon pour :

en évitant les doublons lors des copies de lignes, car dans mon code si 2 valeurs sont trouvée sur une même ligne et cela me copie 2 fois la même ligne)

Pourrais tu préciser.. 2 valeurs trouvées c'est la même référence qui peut être sur la même ligne ? ou c'est 2 références différentes recherchées qui peuvent se trouver sur la même ligne ?

Fred

Oui mon 2ieme fichier c'est l'évolution que j'ai suivi entre depuis l'ouverture de ce topic et ton 1er message (sans retouche avec le code que tu as transmis).

Désolé pour le bazard, disons que j'ai testé avec tableaux structurés et sans car je savais pas trop si ca avait une influence sur les erreurs.

J'en déduis, qu'il est préférable d'avoir des tableaux structurés que pas du tout ?

Concernant les 2 valeurs trouvées, c'est par exemple si sur la ligne 2 je retrouve 1,2,3... références, quelles soient les mêmes ou différentes, je voudrais avoir qu'une seule ligne dans le tableau2 de résultat avec les cellules, où les références ont été trouvées, remplies en rouge.

Pour l'erreur que j'ai :

Et je me demandais (parmi une multitude de questions à propos de ton code) à quoi servait la déclaration de "tdS", car sauf erreur je ne le revois pas dans le code ?

Encore merci

Bon

ci joint un nouvel essai, en ayant optimisé pas mal les choses (du moins je penses).... dans le fichier fournit le code a tourné avec les valeurs de ton 2e tableau.

pour les valeurs trouvées la cellule est mis en rouge et le texte en jaune. si tu vois la case rouge sans jaune c'est que c'est un problème d'affichage et que l'on ne voit pas tous les caractères de la cellule.

pour répondre a tes questions il toujours bien de travaillé avec des tableaux structurés. Les bonnes pratiques voudrait qu'il faut (faudrait) déclarer toutes les variables dans un code et dans ton image ... j'ai justement fait une erreur de déclaration

en mettant en début de code

Option explicit

Cela ne ce serait pas produit...

Je le répète le fichier fournit fonctionne chez moi... (avec les données fournies) si il ne marche pas chez toi... je ne vois pas pourquoi car je ne reproduit pas le problème..

Fred

Après un peu plus d'une heure de route...enfin rentré

J'ai ouvert ton nouveau fichier sur 2 pc différents avec office2013 et office2016 sur l'autre mais rien n'y fait, toujours cette satanée ligne qui revient en erreur :

SHd.Range("A" & i).Resize(, UBound(tbs, 2)) = Application.Index(tbs, lg)

Comme tu n'as pas cette erreur avec O365Pro-2019, je suppose que cela vient de mes versions d'office, peut-être un complément manquant ou quelque chose à activer, je vais creuser par là et modifier un peu le code pour voir les réactions.

Merci et Bon weekend Fred

Bonsoir

je viens de faire un nouvel essai... en décomposant l'action et en faisant d'une manière légèrement différente..

voir ce site pour plus d'information sur les variables tableaux

http://boisgontierj.free.fr/pages_site/tableaux.htm#Tri2DSansIndex

Fred

Bonjour

je me suis connecté à distance sur un PC au boulot qui a la version 2013... et je reproduit le problème... Donc ce qui pose problème est :

Application.index (tbs, lg)

je vais essayé de creuser...

ce qui est bizarre c'est que cet article qui date de 2013 utilise la même chose que moi... comprend pas.. .

https://usefulgyaan.wordpress.com/2013/06/12/vba-trick-of-the-week-slicing-an-array-without-loop-app...

Edit... je creuse je creuse... visiblement il n'accepte pas ma variable tableau à 2D tbs et cela posera aussi problème quelques lignes plus haut.. car j'utilise la même instruction... )

Place_trouver = Application.Match(a_Trouver, Application.Index(tbs, lg), 0)

Fred

Nouvelle version

a tester

Fred

Bonjour Fred,

Avant de lire ton dernier message d'hier, j'ai fait une recherche google sur la fonction index (car c'est bien sur elle que j'avais l'erreur) et je suis tombé sur un fil de discussion (Office 365, encore lui) où il mentionnait justement le site de Jacques (waouh une vrai mine d'or).

Autrement, le même problème apparait aussi sur Office2016.

Je ne comprend pas encore 100% de ton code, néanmoins maintenant je suis armé (boisgontierj.free.fr) et je vais pouvoir avancé sereinement (déjà une chose est certaine, lire les bases ) afin de pouvoir comprendre tout ca.

J'ai testé ton nouveau fichier (où la fonction INDEX n'est plus utilisée), et en effet il n'y a plus du tout d'erreur.

Le résultat est exactement ce que je voulais faire, des jours que je m'arrache les cheveux là-dessus et toi en moins de 24h, c'est fait, bravo je ne peux que .

Je teste dès Lundi avec les vrais données mais il n'y a aucune raison pour que cela ne fonctionne pas (y'a juste un peu plus de données). Je ferais un retour concernant le résultat... Du coup, je pense qu'en attendant je peux quand même mettre le topic comme résolu.

Tu as désobscurci mon weekend Fred, merci beaucoup pour le temps que tu as passés sur mon problème.

Encore une fois Merci beaucoup et un bon weekend à toi

Si tu as besoin d'explication du code.. demande...

Sinon pour mettre résolu le fil :

Fred

Bon en faisant un dernier essai je me suis aperçu d'une coquille..

lors d'une recherche sur la valeur 12AZ158963

sur la feuille 1 il y 5 cellules sur 5 lignes différentes qui sont coloriées alors que dans la feuille 2 seulement 3 lignes ont été copiées...

Il y a donc une erreur quelque part.

Fred

Bon a priori dernière version avec un code plus simple encore... mais certainement plus long dans son exécution car utilisation de boucles... (pas retesté sous Excel 2013) mais devrait fonctionner

j'ai aussi apporté quelques commentaires pour la compréhension du code

Fred

Edit : mauvaise version de fichier je remet la bonne :

Bonsoir Fred,

J'ai pu tester ton code avec seulement quelques données, pas trop eu le temps aujourd'hui, néanmoins le test est concluant

Je devrais pouvoir le mettre à l'épreuve demain...avec le peu que j'ai testé, le temps d'exécution est instantané.

Et les commentaires dans le code, ca aide bien. J'ai pu modifier les tableaux (ajout de colonnes) et déplacer les éléments de recherche en ne modifiant que 8 caractères dans le code... et si j'ai réussi à faire ca simplement, c'est que le code est proprement bien écrit.

Je te dirais le résultat sur du fichier avec plus ou moins 30000 lignes.

Encore merci.