Macro de recherche sous Excel

Question

Bonjour,

Je débute en programmation et mes connaissances étant vraiment faibles, je m'adresse à tous ceux qui seraient susceptibles de m'aider; voilà mon (gros) problème : il faut que je réalise une macro de recherche de valeurs (texte) dans un fichier excel que l'on va nommer "TOTO" pour les placer dans un fichier excel "BIBI". Je m'explique :

- j'ai dans mon fichier "BIBI" un tableau, il y a une colonne B nommée "Transaction" (la seule qui a un întérêt pour ma recherche), et 3 colonnes C, D et E étant vides...;

- j'ai un fichier "TOTO" avec un tableau (où plutôt une arborescence avec dans la colonne A "Scénario", colonne B "Process", colonne C "Etape" et colonne D "Transaction"); ce que j'entends par arborescence : par exemple: pour le scénario "TOTOFAITDUSKI" (colonne A), j'aurai 1 ou X process (colonne B), pour chaque process, j'aurai 1 ou X étape (colonne C), et à chaque étape, il y aura 0,1 ou X transactions...

Il faudrait que je créé une macro qui, comparant la colonne B de mon fichier "BIBI" (les transactions en fait...) et la colonne D de mon fichier "TOTO", répercute dans "BIBI" en colonne C le scénario associé, en colonne D le Process et en colonne E l'étape correspondant à cette transaction; sachant que dans certains cas, une même transaction peut être présente dans 1 ou plusieurs étapes ou process ou scénario de TOTO ou pas du tout...

Aussi autre chose, bien entendu les 2 tableaux ne font pas le même nombre de ligne (3200 pour le tableau A et 6700 pour le tableau B)...

Voilà; j'espère que c'est assez clair malgrès le nombre d'info, sinon, n'hésitez pas à me demander d'autres information. J'ai parcouru le forum et commencé à faire quelque chose à partir de morceaux de codes que j'ai pu récupérer ça et là mais je manque cruellement de connaissances pour réaliser cette macro...
Vraiment merci par avance à tout ceux qui pourront à m'aider.

cs_mabrouklepoux · Answer

t'y connais rien ?
t'es dans la mouiz.
mais ce que je peux faire pour toi :
je vais te poster une source qui prend un fichier arbo comme tu dis, et qui en fait des lignes, ca va déjà t'aider, j'ai fait ca cette année, ca doit trainer qq part.

après loop sur colonne B de bibi
pour chaque cellule, regarder dans colonne D de toto, si pareil, tu renseignes les colonnes de bibi facilement (c'est cellule C, B, A de toto)

si vraiment urgent, envois moi un mp, avec ton mail, et envois tes fichiers
je suis dans mon jour de bonté, que je m'étais promis de faire depuis des années, venir ici donner un coup de mainn, à ce site qui m'a tant aidé, donc profites, c'est open bar en qq sortes !

ps : c'est pas une matrice d'auth SAP dont tu te sers ? parceque si oui, ya dejà pléthore d'outils pour faire ca !

Gwad063 · Answer

Merci mabrouklepoux; Effectivement, j'y connais rien et du coup, c'est l'occasion pour moi de me mettre à Visual Basic vu que ça pourrait me servir par la suite (la progammation, c'est pas ma partie mais il est toujours bon d'en connaître un minimum); si tu peux me poster la source pour faire de mon arbo des lignes, ce serait déjà un 1er pas salvateur vers la lumière. Quand tu parles de matrice SOD de SAp, tu n'es pas loin de la vérité mais ce que je cherche à faire ne concerne pas directement les autorisations...et pour le coup, je n'ai pas d'outil me permettant de faire ça... Je vais suivre tes conseils et essayer avec une boucle; j'avais déjà commencer à faire qqchose dans lequel il y a des erreurs que je galère à rectifier vu qu'il y a beaucoup d'erreur dans mon code (forcément...) mais ça donnait ça : Sub MaMacroDeRechercheAMoi() Dim Nom as string Dim condition as boolean Dim i, j as Integer Workbooks("BIBI").worksheets("Proposition BPR XXX").select i=1 do Nom=Workbooks("BIBI").worksheets("Proposition BPR XXX")cells(i,2).value j=1 condition = False do if Nom = Workbooks("TOTO").worksheets("ZXXX").Cells(j,4).value then Workbooks("BIBI").worksheets("Proposition BPR XXX")cells(i,6).value=Workbooks("TOTO").worksheets("ZXXX").Cells(j,3).value Workbooks("BIBI").worksheets("Proposition BPR XXX")cells(i,7).value=Workbooks("TOTO").worksheets("ZXXX").Cells(j,2).value Workbooks("BIBI").worksheets("Proposition BPR XXX")cells(i,8).value=Workbooks("TOTO").worksheets("ZXXX").Cells(j,1).value Condition = True ENd if j=j+1 Loop While (Cells(j-1,4).value<>""And condition=False) i=i+1 Loop While (Cells(i-1,1).Value<>"" End Sub Si tu as des conseils comme ça, à vue de nez, ça pourrait le faire (je suis pas à 1 ou 2 jours près); dans le pire des cas, je t'enverrai mes fichiers mais je préfèrerai que ce soit en dernier recours.. En tout cas, merci pour ton aide.

cs_mabrouklepoux · Answer

je t'ai postéée la source, recherche avcec [auteur/GWAD063/1234485.aspx Gwad063], tu devrais trouver, ou alors dans mes sources directos

pas mal le code pour une déb !! ca devrait aller, ca me semble bon
juste un conseil fait des 
for i = 1 to limite
next i

c'est plus lisible que des 
do

while blabla,
et evites les erreurs quand on oublies d'incrémenter le compteur

Gwad063 · Answer

Okay, j'ai trouvé la source, normalement, je devrait avoir toutes les billes pour m'en sortir; merci encore...

Gwad063 · Answer

mabrouklepoux, en fait, le code que tu m'as transmis ne marche pas dans mon cas parce que dans ma feuille excel, il s'agit vraiment d'une arborescence...
sinon, si je passe ça outre, mon code ne marche pas pour ma recherche (même si je test avec un fichier cible qui est un tableau classique); je t'avoue que là également, je suis un peu bloqué...
je viens de me rendre compte également que puisque une même transaction pouvait être dans 2 process par exemple, le mieux pour moi était peut être de créer un code me permettant sur une feuille vierge, d'aller chercher, à chaque fois qu'il trouve la transaction en question, le scénario, le process et l'étape correspondant, puis, si il retrouve la transaction un peu plus bas, qu'il rajoute une autre ligne et qu'il mette à quel autre scénario,  autre process, autre étape, etc...

Pour l'instant, j'ai fait quelques modif à mon code qui donne ça : 

Sub MaMacroDeRechercheAMoi()
Dim Nom As String
Dim condition As Boolean
Dim i, j As Integer


For i = 1 To limit
Nom = Workbooks("BIBI").Worksheets("Proposition BPR XXX").Cells(i, 2).Value
For j = 1 To limit
condition = False
If Nom = Workbooks("TOTO").Worksheets("ZXXX").Cells(j, 4).Value Then
Workbooks("BIBI").Worksheets("Proposition BPR XXX").Cells(i, 6).Value = Workbooks("TOTO").Worksheets("ZXXX").Cells(j, 3).Value
Workbooks("BIBI").Worksheets("Proposition BPR XXX").Cells(i, 7).Value = Workbooks("TOTO").Worksheets("ZXXX").Cells(j, 2).Value
Workbooks("BIBI").Worksheets("Proposition BPR XXX").Cells(i, 8).Value = Workbooks("TOTO").Worksheets("ZXXX").Cells(j, 1).Value
condition = True
End If
Next j
Next i
End Sub

Mais ça marche pas...
Si t'as encore un peu de temps pour me donner un coup de main (allez, dis-moi que c'est ton 2ème jour de bonté...)
Ou quelqu'un d'autre qui passe par là...
Merci.

cs_mabrouklepoux · Answer

franchement, je comprends pas tout
sur le code en lui meme
limit c'est quoi ?
pourquoi les deux boucles ont elles la meme alors que tu as précisé que les deux fic avaient pas emem nombre de lignes ?
a quoi ca sert le condition = false ou true ?

as tu bien le option explicit en tete de ton code ?
ca dit au compilateur de ne pas tolérer les variables on définies, c'est chiant, mais très utile, ca évite les erreurs difficiles à voir, comme une faute de frappe sur un nom de variable !

pour ce qui est du code posté, il faut que tu le rende récursif, il fait ce que tu veux, mais pour un seul étage de ton arbo.
donc, en gros, faut le faire se lancer sur la colonne C, pour une hauteur de 1, puis le lancer sur le résultat obtenu, cette fois ci sur la colonne B, toujours une hauteur de 1, et idem, colonne A, hauteur de 1
pas forcément évident, mais je vois pas d'autres solutions.

rapport à ca :
je viens de me rendre compte également que puisque une même transaction
pouvait être dans 2 process par exemple, le mieux pour moi était peut
être de créer un code me permettant sur une feuille vierge, d'aller
chercher, à chaque fois qu'il trouve la transaction en question, le
scénario, le process et l'étape correspondant, puis, si il retrouve la
transaction un peu plus bas, qu'il rajoute une autre ligne et qu'il
mette à quel autre scénario,  autre process, autre étape, etc...

comprends pas bien, tu veux une feuille par transaction ?
remember nbfeuille < 256 en excel ...

Gwad063 · Answer

okay, merci pour tes précisions:
- "limit", je vais rectifier...
- effectivement, je vais zapper a condition...
- je vais essayer le option explicit..
- pour le code posté, je vais tenter de le modifier comme tu le dis...
 
non,  je veux pas une feuille par transaction...ce que j'essayais d'expliquer :dans une feuille vierge, le mieux (sachant que certaines transactions peuvent être dans 1, 2 voire 3 scénarios ou process ou étape) serait peut-être qu'il me rajoute une ligne à chaque fois qu'il retrouve la même transaction dans un autre scénar, process, et étape...exemple :

TransactionA1  Scénar1   Process1.1   Etape1.1.1
TransactionA1  Scénar5   Process5.2   Etape5.2.5
TransactionA2   Scénar1  Process1.3   Etape1.3.1
TranscationA3 etc....

Voilà pour synthètiser : dans ma recherche, si la transaction revient dans plusieurs lignes, qu'une nouvelle ligne en question soit insérée...
ça te paraît plus clair?
Je vais déjà commencer par essayer d'appliquer tes remarques..

cs_mabrouklepoux · Answer

ouauis plius clair
dans ce cas, c'est d'ailleur toujours plus propre et pratique :

créer une variable :

Dim cursheet As Excel.Worksheet

tu crées une feuille
ThisWorkbook.Sheets.Add Before:=ThisWorkbook.Worksheets(1)
Set cursheet = ActiveSheet 
'le set est très important, on instancie un obj, pas juste assigner une variable, si tu l'oublies pas d'erreur de compil, mais 'marchera pas !

dérrière tu utilises comme une feuille normale :

cursheet.Rows("1:3").Delete shift:=xlUp
cursheet.cells("1:3").Formular1C1 = "cooool"

Gwad063 · Answer

Mabrouklepoux, bon, j'ai fais quelques modif suite à ce que tu m'as dis (en bleus, là où il me trouve des erreurs...et je vois pas trop où est le problème...);
Quelques petites choses ont évoluées depuis (le nom des feuilles principalement); 
Modifier le code que tu m'as transmis sera fait dans un second temps; dans ce cas précis, j'ai utilisé quelquechose qui se rapproche d'un tableau normal et non pas d'un référentiel (c'est le fichier "TOTO") pour tester ma macro...
Je galère déjà suffisamment donc chaque chose en son temps...A propos de galère, j'ai pas trop compris "cursheet", notamment dans la façon de l'utiliser et je l'ai pas trouvé dans l'aide visual basic...Bon, je sais, ça peut te paraître idiot mais bon, je suis deb...
Ci joint ma macro (avant que je décroche pour cursheet):

Sub MaMacroDeRechercheAMoi()
Dim Nom As String
Dim i, j As Integer
Dim cursheet As Excel.Worksheet
ThisWorkbook.Sheets.Add Before:=ThisWorkbook.Worksheets(1)
ThisWorkbook("BIBI").Worksheets("Transactions par rôles").Select
Set cursheet = ActiveSheet


For i = 1 To 3130
Nom = ThisWorkbook("BIBI").Worksheets("Transactions par rôles").cells(i, 2).Value
For j = 1 To 6846
If Nom = ThisWorkbook("TOTO").Worksheets("ZSGD").cells(j, 4).Value Then
ThisWorkbook("BIBI").Worksheets("Transactions par rôles").cells(i, 6).Value = ThisWorkbook("TOTO").Worksheets("BPR SGD").cells(j, 3).Value
ThisWorkbook("BIBI").Worksheets("Transactions par rôles").cells(i, 7).Value = ThisWorkbook("TOTO").Worksheets("BPR SGD").cells(j, 2).Value
ThisWorkbook("BIBI").Worksheets("Transactions par rôles").cells(i, 8).Value = ThisWorkbook("TOTO").Worksheets("BPR SGD").cells(j, 1).Value
End If
Next j
Next i
End Sub

Merci.

cs_mabrouklepoux · Answer

erreur : envoi le decriptif, parceque je vois pas trop non plus

cursheet : juste une varaible que j'ai défninie,

Dim cursheet As Excel.Worksheet

 et dans laquelle je vais mettre une nouvelle feuille,
' oncrée une nouvelle feuille dans le classeur
ThisWorkbook.Sheets.Add Before:=ThisWorkbook.Worksheets(1)
' on assigne cette nouvelle feuille à notre variable cursheet. quand on ajoute une feuille dans un classeur, elle devient la 'feuille active, donc référencée par Activesheet
Set cursheet = ActiveSheet

 tu pourras donc écrire et lire dans cette feuille
cursheet.cells(1,1).formular1C1 = "ertert"

un truc archi important pour le dev en général
quand tu comprends pas quelques lignes, il faut les isoler
crée un nouveau classeur xls, colle ce code dedans, et excute le
tu vverras alors bien ce qu'il fait

sub test
Dim cursheet As Excel.Worksheet

' oncrée une nouvelle feuille dans le classeur

ThisWorkbook.Sheets.Add Before:=ThisWorkbook.Worksheets(1)

' on assigne cette nouvelle feuille à notre variable cursheet. quand on
ajoute une feuille dans un classeur, elle devient la 'feuille active,
donc référencée par Activesheet

Set cursheet = ActiveSheet
'on écrit erter en A1 de la feuille crée

cursheet.cells(1,1).formular1C1 = "ertert"
end sub

autre chose, quand tu sais faire quelque chose dans excel, mais ne connais pas le code pour le faire (genre trier une colonne)

tu vas dans outil macro nouvellemacro
tu tapes entrée

ceci lance le recorder (enregistre ce que tu fais)

tu fais ton action dont tu cherches le code

tu vas recliquer au meme endroit sur un carré pour arreter l'enregistreur (arreter l'enregistrement)

tu tapes alt + F11 (afficher le code d'un fic excel)

tu regardes dans un des modules sur la gauche de l'écran, et tu vas trouver le code associé.

Gwad063 · Answer

okay, je vais regarder ce que ça fait...utiliser l'enregistrement peut être une bonne idée effectivement;
pour les erreurs dans ma macro (en bleu toujours), c'est une erreur du genre "Proppriété ou méthode non gérée par cet objet" erreur 438. Pourtant, aussi bien "ThisWorkbook" que "Worksheets" peuvent bien être associés à ".select" ou ".value", non?

Sub MaMacroDeRechercheAMoi()
Dim Nom As String
Dim i, j As Integer
Dim cursheet As Excel.Worksheet
ThisWorkbook.Sheets.Add Before:=ThisWorkbook.Worksheets(1)
ThisWorkbook("BIBI").Worksheets("Transactions par rôles").Select
Set cursheet = ActiveSheet
For i = 1 To 3130
Nom = ThisWorkbook("BIBI").Worksheets("Transactions par rôles").cells(i, 2).Value
For j = 1 To 6846
If Nom = ThisWorkbook("TOTO").Worksheets("ZSGD").cells(j, 4).Value Then
ThisWorkbook("BIBI").Worksheets("Transactions par rôles").cells(i, 6).Value = ThisWorkbook("TOTO").Worksheets("BPR SGD").cells(j, 3).Value
ThisWorkbook("BIBI").Worksheets("Transactions par rôles").cells(i, 7).Value = ThisWorkbook("TOTO").Worksheets("BPR SGD").cells(j, 2).Value
ThisWorkbook("BIBI").Worksheets("Transactions par rôles").cells(i, 8).Value = ThisWorkbook("TOTO").Worksheets("BPR SGD").cells(j, 1).Value
End If
Next j
Next i
End Sub

Gwad063 · Answer

En tout cas, quoiqu'il arrive, merci encore, tu me sauves la vie sur ce coup-là.

cs_mabrouklepoux · Answer

utilises workbooks("bibi") plutot que thisworkbook("bibi") (juste parceque je trouve ca plus logisue)

et surtout :
bibi.xls !!!
et pas bibi tout court !
 

après pour la forme, fais la meem chose avec tes classeurs qu'aves tes feuilles, déclare des variables et mets les dedans

ceci :
Workbooks("classeur1.xls").Sheets("Feuil1").Cells(1, 1).FormulaR1C1 = Workbooks("classeur2.xls").Sheets("Feuil1").Cells(1, 1).FormulaR1C1

est pareil que cela
Dim W1 As Excel.Workbook

Dim W2 As Excel.Workbook
Set W1 = Workbooks("classeur1.xls")
Set W2 = Workbooks("classeur2.xls")
W1.Sheets("Feuil1").Cells(1, 1).FormulaR1C1 = W2.Sheets("Feuil1").Cells(1, 1).FormulaR1C1

de meme 
dim S1 as excel.worksheet

set S1 = W1.Sheets("Feuil1").

et donc 
S1.Cells(1, 1).FormulaR1C1 = W2.Sheets("Feuil1").Cells(1, 1).FormulaR1C1
est encore équivalent

mais dans la deuxième forme, tu evites certains soucis que tu tarderas pas à rencontrer, et ton code évoluera plus simplement, par exemple si tes noms de classeurs changent

lis un peu de doc sur la structure objet excel, workbook, worksheet, cell, range, ....
ca 'aideras a comprendre ce que tu fais

je suis clair ?

Gwad063 · Answer

Okay, c'est clair; merci.

Gwad063 · Answer

Mabrouklepoux, je reviens vers toi parce que, même si les choses s'éclaircissent dans ma tête, encore beaucoup de flous, 
Après, j'ai suivi tes conseils (c'est clair que lorsqu'elle marchera, cette macro ne résoudra pas l'intégralité de mes pbs mais au moins, je pourrai m'appuyer dessus, notamment en mettant le résultat de ma recherche dans une nouvelle feuille, cf ce que tu m'as montré dans un post précédent); voilà ce que ça donne :

Sub MaMacroDeRechercheAMoi()
Dim W1 As Excel.Workbooks
Dim W2 As Excel.Workbooks
Dim S1 As Worksheet
Dim S2 As Worksheet
Dim i, j As Integer
Set W1 = Workbooks("BIBI.xls ")
Set W2 = Workbooks("TOTO.xls ")
Set S1 = W1.Sheets("Proposition BPR SGD")
Set S2 = W2.Sheets("BPR SGD")
S1.Select

For i = 1 To 3131
For j = 1 To 6846
If S1.Cells(i, 1).Value = S2.Cells(j, 4).Value Then
S1.Cells(i, 6).FormulaR1C1 = S2.Cells(j, 3).FormulaR1C1
S1.Cells(i, 7).FormulaR1C1 = S2.Cells(j, 2).FormulaR1C1
S1.Cells(i, 8).FormulaR1C1 = S2.Cells(j, 1).FormulaR1C1
End If
Next j
Next i
End Sub

Il galère au niveau de "sheets", j'ai essayé notamment avec worksheet, c'est la même erreur ("erreur de compilation : membre de méthode ou de donnée introuvable"), pourtant, pour le coup, mon code (me) semble assez logique;
2/3 questions (qui vont peut-être te sembler stupides) :
- il est bien possible d'assigner plusieurs variables (avec "set")?
- la seule véritable différence entre .value et .formular1c1, c'est que .formular1c1 va permettre de récupérer un formule de calcul dans une cellule, n'est ce pas? dans ces cas-là, autant que j'utilise .value...

Gwad063 · Answer

Bonjour,
J'en profite pour clore ce post, voici ma macro définitive de recherche dans une arborescence, ça marche; si ça peut en inspirer certains...
Bien sûr, pour vous donner une idée remplacer BIBI par "Proposition finale par rapport BPR Standard" et TOTO par "BPR_Standard", les 2 fichiers doivent être dans le même répertoire...

Option Explicit


Private Sub CommandButton1_Click()


Dim cell As Range, k As Long, i As Long
Dim Wb2 As Workbook
Dim Ws As Worksheet


 Application.ScreenUpdating = False
 Application.DisplayStatusBar = True
 Application.StatusBar = "Traitement en cours, veuillez patienter..."


 Workbooks.Open Filename:=ThisWorkbook.Path & "\BPR_Standard.xls"
 Workbooks("Proposition finale par rapport BPR Standard.xls").Activate
     Set Wb2 = Workbooks("BPR_Standard.xls")
     Set Ws = Wb2.Sheets("ZSGD")
     Sheets("Transactions BPR").Range("A3:D" & Sheets("Transactions BPR").Range("A65536").End(xlUp).Row + 1) = ""
     k = Sheets("Transactions BPR").Range("A65536").End(xlUp).Row + 1
     For i = 3 To Range("B65536").End(xlUp).Row
         For Each cell In Ws.Range("D4:D" & Ws.Range("D65536").End(xlUp).Row)
             If Range("B" & i) = cell Then
                With Sheets("Transactions BPR")
                   .cells(k, 1) = Range("B" & i)
                      If cell.Offset(0, -1) = "" Then
                          .cells(k, 2) = Ws.cells(cell.Offset(0, -1).End(xlUp).Row, cell.Offset(0, -1).Column).Value
                      Else
                          .cells(k, 2) = cell.Offset(0, -1).Value
                      End If
                   .cells(k, 3) = Ws.cells(cell.Offset(0, -2).End(xlUp).Row, cell.Offset(0, -2).Column).Value
                   .cells(k, 4) = Ws.cells(cell.Offset(0, -3).End(xlUp).Row, cell.Offset(0, -3).Column).Value
                End With
             k = k + 1
             End If
         Next
     Next
  Sheets("Transactions BPR").Activate
  Application.StatusBar = False
  Application.ScreenUpdating = True
End Sub

Macro de recherche sous Excel

16 réponses

Votre réponse

Discussions similaires