Code VBA pour recherchev sur des gros fichiers
Bonjour à tous,
J'espère que vous allez pouvoir m'aider
J'aimerais un code VBA qui fasse la même fonction que la formule Recherchev, je m'explique.
J'ai plusieurs fichier dans un dossier qui ont tous la même configuration c'est à dire que la première feuille de chaque fichier est la BASE, dans cette BASE il y a 2 colonnes:
- la colonne A qui contient les valeurs qui correspondent aux données qu'il faudra chercher
- et la colonne B qui correspond à la valeur qu'il faudra inscrire dans les autres feuilles à partir de la cellule B11.
Les autres feuilles ont plusieurs données qui commencent à la colonne B jusqu'à la fin de la feuille.il peut y avoir des données de la ligne 1 à 3 pour certains fichiers ou 1 à 6 pour d'autres.Et dans chaque fichier il peut y avoir à peu près une quarantaine de feuille et chaque feuille peut avoir à peu près 17000 colonnes.
j'aimerais avoir un code VBA:
- qui cherche une valeur spécifique (dans la cellule B1 à partir de la 2ème feuilles de chaque fichier car la première feuille contient la base), et les fichiers se trouve dans le dossier C:\Users\Desktop\ESSAIE\4- R 2020
-il cherche cette valeur spécifique dans la colonne A de la première feuille de chaque fichier qui s'appelle BASE (de A3 à A52)
- il renvoie la valeur correspondante de la même ligne de la colonne B dans la même colonne de la recherche de la valeur spécifique (en l'occurrence on cherche la valeur de la B1), Et il met cette valeur dans la cellule que j'ai désigné B11 (qui est de la même colonne que la B1 la valeur qu'on recherche).
Ensuite il fait la même chose pour la cellule B2 exemple:
- il cherche une valeur spécifique (dans la cellule B2 à partir de la 2ème feuilles de chaque fichier car la première feuille contient la base), et les fichiers se trouve dans le dossier C:\Users\Desktop\ESSAIE\4- R 2020
- il cherche cette valeur spécifique dans la colonne A de la première feuille de chaque fichier qui s'appelle BASE (de A3 à A52)
- il renvoie la valeur correspondante de la même ligne de la colonne B dans la même colonne de la recherche de la valeur spécifique (en l'occurrence on cherche la valeur de la B2), Et il met cette valeur dans la cellule que j'ai désigné B12.
Ensuite il fait la même chose pour la cellule B3:
- qui cherche une valeur spécifique (dans la cellule B3 à partir de la 2ème feuilles de chaque fichier car la première feuille contient la base), et les fichiers se trouve dans le dossier C:\Users\Desktop\ESSAIE\4- R 2020
- il cherche cette valeur spécifique dans la colonne A de la première feuille de chaque fichier qui s'appelle BASE (de A3 à A52)
- il renvoie la valeur correspondante de la même ligne de la colonne B dans la même colonne de la recherche de la valeur spécifique (en l'occurrence on cherche la valeur de la B3), Et il met cette valeur dans la cellule que j'ai désigné B13 etc...
lorsqu'il n'y a plus de données dans la colonne B, il change de colonne et fait la même chose pour la valeur C1 exemple:
- il cherche une valeur spécifique (dans la cellule C1 à partir de la 2ème feuilles de chaque fichier car la première feuille contient la base), et les fichiers se trouve dans le dossier C:\Users\Desktop\ESSAIE\4- R 2020
- il cherche cette valeur spécifique dans la colonne A de la première feuille de chaque fichier qui s'appelle BASE (de A3 à A52)
- il renvoie la valeur correspondante de la même ligne de la colonne B dans la même colonne de la recherche de la valeur spécifique (en l'occurrence on cherche la valeur de la C1), Et il met cette valeur dans la cellule que j'ai désigné C11.
Ensuite il fait la même chose pour la cellule C2 exemple:
- il cherche une valeur spécifique (dans la cellule C2 à partir de la 2ème feuilles de chaque fichier car la première feuille contient la base), et les fichiers se trouve dans le dossier C:\Users\Desktop\ESSAIE\4- R 2020
- Il cherche cette valeur spécifique dans la colonne A de la première feuille de chaque fichier qui s'appelle BASE (de A3 à A52)
- il renvoie la valeur correspondante de la même ligne de la colonne B dans la même colonne de la recherche de la valeur spécifique (en l'occurrence on cherche la valeur de la C2), Et il met cette valeur dans la cellule que j'ai désigné C12.
Ensuite il fait la même chose pour la cellule C3:
- il cherche une valeur spécifique (dans la cellule C3 à partir de la 2ème feuilles de chaque fichier car la première feuille contient la base), et les fichiers se trouve dans le dossier C:\Users\Desktop\ESSAIE\4- R 2020
- il cherche cette valeur spécifique dans la colonne A de la première feuille de chaque fichier qui s'appelle BASE (de A3 à A52)
- il renvoie la valeur correspondante de la même ligne de la colonne B dans la même colonne de la recherche de la valeur spécifique (en l'occurrence on cherche la valeur de la C3), Et il met cette valeur dans la cellule que j'ai désigné C13 etc...
Lorsqu'il n'y a plus de données dans la colonne C, il change de colonne et fait la même chose pour la valeur D1 jusqu'à ce qu'il n' y ait plus de donnée à chercher dans la feuille.
Dès qu'il n'y a plus de données dans la feuille, il passe à la feuille suivante et refait la même chose.
Ce code doit le faire pour tous les fichiers qui se trouve dans le dossier C:\Users\Desktop\ESSAIE\4- R 2020
Par contre il faut que le code s'exécute en moins de 30 minutes pour tout le dossier sachant que les fichiers sont très lourds, les fichiers contiennent une quarantaine de feuilles et chaque feuille contient à peu 17000 colonnes de valeur spécifique à faire avec le code.
C'est assez répétitif.
Est ce que vous pouvez m'aidez à trouver un code qui sera très efficace svp?
Je vous remerci d'avance,
Lilie
Bonjour et bienvenue
Française n'est pas une version Excel : précise dans ton profil 2019, 2021, 365... et joins un exemple représentatif anonymisé soit 2 fichiers : celui à remplir et une source.
A priori faisable par POwerQuery également
Bonjour,
Ma version d'excel est : 2402 j'ai microsoft 365 pack family
Voici 2 fichiers qui sont dans le dossier.
il faut savoir que dans mon dossier j'ai à peu près 20 fichiers, et j'ai beaucoup plus de feuilles qui sont rempli de données, j'ai du supprimer pas mal de feuille dans les fichiers car ça ne passé pas pour l'envoi.
Comme vous voyez, la première page est la base, et toutes les autres page contiennent des données, ces données correspondent à une valeur dans la BASE.
Merci pour votre aide.
RE
2402 est une révision s'appliquant à plusieurs versions Excel.
Ta version est 365. A mettre dans ton profil
En fait tu écris dans chacun des fichiers, tu ne synthétise pas n fichiers dans un si j'ai bien compris.
J'aimerais que l'on parte de vierge-num-7
On cherche la valeur en BASE!A3 soit R2020 :R1 RAM dans Feuil1 S4 7 et le trouve en B1 de cette feuille : après on fait quoi ?
Peux préciser pas à pas avec cet exemple car j'ai du mal à suivre ce qu'on copie et où exactement ?
Bonjour à tous
Une version "test" du cœur du traitement sur un seul classeur. Est-ce que cela répond au traitement désiré ?
- Cliquez sur Init pour initialiser 40 feuille de données
- Puis cliquez sur Hop! pour lancer le test
Sur ma bécane pour 40 feuilles -> environ 10 s de temps d'exécution.
78 chris, la cellule B1 correspond à R2020 :R1 RAM, lorsqu'on va chercher dans la BASE mais dans la colonne A le texte R2020 :R1 RAM. dans la colonne B cela correspond au nombre 1
J'aimerais que le nombre 1 soit écrit dans la cellule B11.
pour la cellule B2 de la Feuil1 S4 7, la donnée à chercher est: R2020 :R1 MED C, il faut qu'il aille chercher cet donnée dans la première feuille (BASE) dans la colonne A, et qu'il mette dans la cellule B12 de la Feuil1 S4 7 le chiffre 1 et ainsi de suite...
Pour la cellule C1, je veux qu'il me mette le chiffre trouvé dans la cellule C11 et ainsi de suite.
J'aimerais qu'il me fasse la même chose pour toutes les données de toutes les feuilles de tous les fichiers qui se trouve dans mon dossier C:\Users\Desktop\ESSAIE\4- R 2020. en un temps record si possible :-)
Bonjour mafraise,
j'ai essayé la version test et il n'y a pas de données qui en ressort malheureusement
Mais merci pour ton aide.
Re,
Bonjour mafraise,
j'ai essayé la version test et il n'y a pas de données qui en ressort malheureusement
Mais merci pour ton aide.
Un peu court comme réponse
Le but est de savoir si le traitement correspond à ce que vous désirez ; après on avancera pour traiter tous les fichiers un par un.
- Avez-vous cliqué sur INIT ? Que c'est-il passé ?
- Avez-vous ensuite cliqué sur Hop! ? Que s'est-il passé ?
nota : vous n'avez toujours pas complété votre profil pour préciser la version d'Excel et si c'est sur PC ou MAC.
Le traitement n'est pas ce que je désire car je pense qu' il faudra que j'ouvre chaque fichier pour que j'appuie sur les boutons (peut-être que je me trompe).
J'aimerais que tout le dossier se mette à jours sans forcément ouvrir tous les fichiers de mon dossier.
Je sais que les macros peuvent le faire mais c'est très très lent. Environ 3h pour me traiter tous mon dossier. Et comme j'ai plusieurs dossier du même type c'est compliqué
Re,
Le traitement n'est pas ce que je désire car je pense qu' il faudra que j'ouvre chaque fichier pour que j'appuie sur les boutons (peut-être que je me trompe).
Je vais essayer d'être encore plus clair
Le classeur que je vous ai fourni est juste un classeur pour vérifier que le traitement que vous désirez pour chaque classeur est bien celui que j'ai codé.
- Si c'est oui, on continuera la mise au point pour une ouverture automatique des fichiers, leur traitement puis leur fermeture
- Si c'est non, il faut dire pourquoi le traitement ne vous convient pas et je modifierai le code en conséquence et vous ferai vérifier à nouveau
C'est plus clair maintenant ?
Re
Merci d'avoir complété votre profil
Il serait intéressant de préciser si vous êtes sur PC ou MAC. Par exemple dans mon code j'utilise une structure (dictionary) spécifique aux versions sous windows et non disponible sur MAC (je sais aussi faire un code qui fonctionne dans les deux cas). Pour ma pomme, j'ai mis une information du type : 365 FR (Win)
Bonjour
78 chris, la cellule B1 correspond à R2020 :R1 RAM, lorsqu'on va chercher dans la BASE mais dans la colonne A le texte R2020 :R1 RAM. dans la colonne B cela correspond au nombre 1
J'aimerais que le nombre 1 soit écrit dans la cellule B11.
pour la cellule B2 de la Feuil1 S4 7, la donnée à chercher est: R2020 :R1 MED C, il faut qu'il aille chercher cet donnée dans la première feuille (BASE) dans la colonne A, et qu'il mette dans la cellule B12 de la Feuil1 S4 7 le chiffre 1 et ainsi de suite...
Pour la cellule C1, je veux qu'il me mette le chiffre trouvé dans la cellule C11 et ainsi de suite.
J'aimerais qu'il me fasse la même chose pour toutes les données de toutes les feuilles de tous les fichiers qui se trouve dans mon dossier C:\Users\Desktop\ESSAIE\4- R 2020. en un temps record si possible :-)
Si je comprends bien, sur Feuil1 S4 7 c'est l'équivalent d'une formule =RECHERCHEV(B1;BASE!$A:$B;2;0) de B11 à WFC17
mafraise,
J'ai mis dans ma description que j'utilisé un ordinateur HP laptop.
pour le fichier lilie3887 test, dès que je clique sur Init voici le message d'erreur que j'ai:
78 Chris,
C'est exactement cette formule que je voudrais pour toutes les feuilles de mes fichiers sauf les feuilles BASE.
J'ai retrouvé la macro que j'utilisais et qui mettais 3h à s'exécuter, c'est celle la:
Sub CopierDonnees()
Dim cheminDossier As String
Dim nomFichier As String
Dim wb As Workbook
Dim wsBase As Worksheet
Dim wsOther As Worksheet
Dim lastRowBase As Long
Dim lastRowOther As Long
Dim lastColumnBase As Long
Dim lastColumnOther As Long
Dim rowIndex As Long
Dim colIndex As Long
Dim cell As Range
Dim searchRange As Range
Dim foundCell As Range
' Spécifier le chemin du dossier contenant les fichiers à traiter
cheminDossier = "C:\Users\Desktop\ESSAIE\4- R 2020\"
' Obtenir le premier fichier dans le dossier
nomFichier = Dir(cheminDossier & "*.xlsx")
' Boucler à travers tous les fichiers dans le dossier
Do While nomFichier <> ""
' Ouvrir le fichier
Set wb = Workbooks.Open(cheminDossier & nomFichier)
' Référence à la première feuille de chaque fichier
Set wsBase = wb.Sheets("BASE")
' Trouver la dernière ligne avec des données dans la feuille "BASE"
lastRowBase = wsBase.Cells(wsBase.Rows.Count, 1).End(xlUp).Row
' Trouver la dernière colonne avec des données dans la feuille "BASE"
lastColumnBase = wsBase.Cells(1, wsBase.Columns.Count).End(xlToLeft).Column
' Boucler à travers toutes les autres feuilles du fichier
For Each wsOther In wb.Sheets
' Vérifier si la feuille est différente de la feuille "BASE"
If wsOther.Name <> wsBase.Name Then
' Trouver la dernière colonne avec des données dans la feuille autre que "BASE"
lastColumnOther = wsOther.Cells(1, wsOther.Columns.Count).End(xlToLeft).Column
' Copier les données de la première feuille vers la deuxième feuille
For colIndex = 2 To lastColumnOther
' Trouver la dernière ligne avec des données dans la colonne actuelle
lastRowOther = wsOther.Cells(wsOther.Rows.Count, colIndex).End(xlUp).Row
' Boucler à travers chaque ligne de la colonne actuelle
For rowIndex = 1 To lastRowOther
' Référence à la cellule dans la ligne actuelle de la colonne actuelle
Set cell = wsOther.Cells(rowIndex, colIndex)
' Référence à la plage de recherche dans la colonne A de la feuille "BASE"
Set searchRange = wsBase.Columns(1)
' Rechercher la valeur dans la feuille "BASE"
Set foundCell = searchRange.Find(What:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
' Si la valeur est trouvée, copier la valeur correspondante de la colonne B de la feuille "BASE"
If Not foundCell Is Nothing Then
wsOther.Cells(rowIndex + 10, colIndex).Value = wsBase.Cells(foundCell.Row, 2).Value
End If
Next rowIndex
Next colIndex
End If
Next wsOther
' Enregistrer et fermer le fichier
wb.Close True
' Obtenir le prochain fichier dans le dossier
nomFichier = Dir
Loop
End Sub
Voila le code que j'utilisais, ce code fonctionne mais il est très très très lent à s'executer environ 3h pour 1seul dossier
mafraise,
Il y a une Macro qui est bloquée, et je n'arrive pas à débloquer.
ça me mets exactement le même message d'erreur.
RE
J'obtiens sur mon PC 140 secondes pour les 2 fichiers
A tester après avoir adapter le chemin (en jaune)
Ne pas stocker ce classeur avec ceux à traiter
A noter qu'avec les nouvelles protection MS il faut enregistrer sous le fichier reçu, le fermer et le ré-ouvrir en autorisant les macros
RE
J'en ai une autre plus rapide à 28 secondes mais j'attends de savoir si c'est bien le résultat attendu
Re 78 Chris
Avec ce code pour les 2 fichiers je mets 50 secondes:
Sub CopierDonneesRapide2()
Dim cheminDossier As String
Dim nomFichier As String
Dim wb As Workbook
Dim wsBase As Worksheet
Dim wsOther As Worksheet
Dim lastRowBase As Long
Dim lastRowOther As Long
Dim lastColumnBase As Long
Dim lastColumnOther As Long
Dim rowIndex As Long
Dim colIndex As Long
Dim cell As Range
Dim searchRange As Range
Dim foundRow As Variant
' Spécifier le chemin du dossier contenant les fichiers à traiter
cheminDossier = "C:\Users\Desktop\ESSAIE\4- R 2020\"
' Obtenir le premier fichier dans le dossier
nomFichier = Dir(cheminDossier & "*.xlsx")
' Boucler à travers tous les fichiers dans le dossier
Do While nomFichier <> ""
' Ouvrir le fichier
Set wb = Workbooks.Open(cheminDossier & nomFichier)
' Référence à la première feuille de chaque fichier
Set wsBase = wb.Sheets("BASE")
' Trouver la dernière ligne avec des données dans la feuille "BASE"
lastRowBase = wsBase.Cells(wsBase.Rows.Count, 1).End(xlUp).Row
' Trouver la dernière colonne avec des données dans la feuille "BASE"
lastColumnBase = wsBase.Cells(1, wsBase.Columns.Count).End(xlToLeft).Column
' Boucler à travers toutes les autres feuilles du fichier
For Each wsOther In wb.Sheets
' Vérifier si la feuille est différente de la feuille "BASE"
If wsOther.Name <> wsBase.Name Then
' Trouver la dernière colonne avec des données dans la feuille autre que "BASE"
lastColumnOther = wsOther.Cells(1, wsOther.Columns.Count).End(xlToLeft).Column
' Copier les données de la première feuille vers la deuxième feuille
For colIndex = 1 To lastColumnOther
' Trouver la dernière ligne avec des données dans la colonne actuelle
lastRowOther = wsOther.Cells(wsOther.Rows.Count, colIndex).End(xlUp).Row
' Référence à la plage de recherche dans la colonne A de la feuille "BASE"
Set searchRange = wsBase.Columns(1)
' Boucler à travers chaque cellule de la colonne actuelle de la feuille autre que "BASE"
For rowIndex = 1 To lastRowOther
' Référence à la cellule dans la ligne actuelle de la colonne actuelle
Set cell = wsOther.Cells(rowIndex, colIndex)
' Rechercher la valeur dans la feuille "BASE"
foundRow = Application.Match(cell.Value, searchRange, 0)
' Si la valeur est trouvée, copier la valeur correspondante de la colonne B de la feuille "BASE"
If Not IsError(foundRow) Then
wsOther.Cells(rowIndex + 10, colIndex).Value = wsBase.Cells(foundRow, 2).Value
End If
Next rowIndex
Next colIndex
End If
Next wsOther
' Enregistrer et fermer le fichier
wb.Close True
' Obtenir le prochain fichier dans le dossier
nomFichier = Dir
Loop
End Sub
Mais il me faudrait encore raccourcir le temps.
Je serais intéressée par la macro à 28 secondes :-)
RE