VBA Méthode de Recherche parmi des milliers de données [Résolu]

Signaler
Messages postés
9
Date d'inscription
mercredi 3 mars 2021
Statut
Membre
Dernière intervention
20 mai 2021
-
Messages postés
24106
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
15 juin 2021
-
Bonjour,

Dans mon programme VBA, je vais chercher des informations dans un autre fichier EXCEL fermé. Ce dernier est une immense base de données (sans commentaires svp) dans laquelle se trouve plusieurs informations sur toute la ligne concernée.

L'utilisateur rentre un numéro d'affaire "aff", et le programme va parcourir plus de 3000 lignes dans la colonne A pour retrouver le numéro de la ligne "ligne" qui lui correspond. Ce processus prend énormément de temps (peut être 1 ou 2 min).

Quelle est donc, selon vous, la méthode de recherche la plus optimisée dans une grande base de données svp ?

Ci-dessous vous trouverez le code que j'utilise pour faire ma recherche. Pour détailler un peu, je rentre les valeurs des cases de la colonne A du fichier excel fermé dans la case A1 de mon fichier excel ouvert. C'est dans cette dernière que je peux comparer l'entrée "aff" avec la valeur du tableau, avant de "clear" la case A1. Si vous avez d'autres solutions, plus optimales, je suis également preneur.


Sub Bouton1()
Dim aff As Variant
Dim path_CSC, Fichier As String

aff = InputBox("RENSEIGNEZ VOTRE NUMERO D'AFFAIRE", "NUMERO D'AFFAIRE")

path_CSC = "Mon chemin vers le EXCEL fermé"
Fichier = "Mon fichier EXCEL fermé"

Dim i, ligne As Integer

For i = 1 To 3000 'Parcours la colonne concernée du fichier EXCEL fermé
ThisWorkbook.Names.Add "plage", _
RefersTo:="='" & path_CSC & "[" & Fichier & "]CSC'!$A$" & i
With Sheets("Sheet1")
.Range("A1") = "=plage"
.Range("A1").Copy
Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValues
If Sheets("Sheet1").Range("A1") = aff Then
ligne = i
.[A1].Clear
MsgBox ("ligne = " & ligne)
Exit For
End If
.[A1].Clear
End With
Next

5 réponses

Messages postés
12251
Date d'inscription
jeudi 15 mai 2008
Statut
Modérateur
Dernière intervention
17 mars 2021
2 620
Bonjour,
1- ouvrir le fichier,
2- chercher la ligne,
3- copier la ligne,
4- fermer le fichier

Sub Bouton1()
Dim aff As Variant
Dim path_CSC As String
Dim Fichier As Workbook
Dim Rng As Range
Dim ligne As Long

aff = InputBox("RENSEIGNEZ VOTRE NUMERO D'AFFAIRE", "NUMERO D'AFFAIRE")

'ici vérifier si annulation ou autre bêtise utilisateur...

path_CSC = "Mon chemin vers le EXCEL fermé"
Set Fichier = Workbooks.Open(path_CSC)

With Fichier.Worksheets("CSC")
   Set Rng = .Columns(1).Cells.Find(aff)
End With
If Not Rng Is Nothing Then
    ligne = Rng.Row
    MsgBox ligne

'ICI Copier la ligne ou action  à réaliser

Else
   MsgBox "non trouvé"
End If
Fichier.Close False
End Sub

Messages postés
9
Date d'inscription
mercredi 3 mars 2021
Statut
Membre
Dernière intervention
20 mai 2021

Merci pour la réponse,

J'aimerai éviter d'ouvrir le fichier excel justement. Il se trouve sur un serveur partagé, utilisé par plusieurs personnes.

Je vais tout de même me pencher sur la fonction FIND qui me semble intéressante mais qui, à vue d'oeil, ne me permet pas d'utiliser le fichier EXCEL fermé.
Messages postés
24106
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
15 juin 2021
6 804
Bonjour,
Equiv() et Index() fonctionnent dans un classeur fermé.
Recherchev() également
Tu as tout ce qu'il te faut.
eric

En essayant continuellement, on finit par réussir. 
Donc plus ça rate, plus on a de chances que ça marche.(les Shadoks)
En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
Messages postés
9
Date d'inscription
mercredi 3 mars 2021
Statut
Membre
Dernière intervention
20 mai 2021

Je n'avais jamais entendu parler de Equiv (Match en VBA?) et Index merci beaucoup ! Cela va grandement m'aider pour le traitement des données dans un classeur.

Cependant, je n'arrive pas à voir/comprendre comment on choisit le classeur avec Equiv et Index. Pourriez-vous m'aider/me guider svp ?

Merci pour vos réponses !
Messages postés
24106
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
15 juin 2021
6 804
Bonjour,

oui, je ne me prend plus la tête et j'utilise une feuille intermédiaire masquée pour simplifier.
Tu prépares et testes tes formules sur feuille puis :
    With Worksheets("Feuil1")
.[B2].FormulaLocal = "=RECHERCHEV(A2;'E:\tmp\[Classeur1.xlsm]Feuil1'!$A$1:$E$11;2;FAUX)"
If Not IsError(.[B2]) Then
MsgBox .[B2]
Else
' si besoin
MsgBox .[A2] & " non trouvé"
End If
End With

tu peux traiter la chaine de la formule dans une boucle.

Si tu risques d'avoir des lenteurs en cas de grosse formules matricielles et de pb réseau il faut synchroniser pour attendre le résultat avec :
    Do Until Application.CalculationState = xlDone
DoEvents
Loop

DoEvents est lui-même consommateur de temps, c'est mieux si tu arrives à t'en passer.
A mettre donc avant la récup
eric
Messages postés
9
Date d'inscription
mercredi 3 mars 2021
Statut
Membre
Dernière intervention
20 mai 2021

J'ai finalement trouvé comment récupérer l'index d'une valeur dans un classeur fermé sur EXCEL. Merci à tous pour vos réponses.

Pour ceux que ça intéresse :

=EQUIV(E1,[FichierSourceFerme.xslm]Feuille!$A$1:$A$4000,0)
Messages postés
24106
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
15 juin 2021
6 804
Bonjour,

oui, je ne me prend plus la tête et j'utilise aussi une feuille intermédiaire masquée pour simplifier.
Tu prépares et testes tes formules sur feuille puis :
    With Worksheets("Feuil1")
.[B2].FormulaLocal = "=RECHERCHEV(A2;'E:\tmp\[Classeur1.xlsm]Feuil1'!$A$1:$E$11;2;FAUX)"
If Not IsError(.[B2]) Then
MsgBox .[B2]
Else
' si besoin
MsgBox .[A2] & " non trouvé"
End If
End With

tu peux traiter la chaine de la formule dans une boucle.

Si tu risques d'avoir des lenteurs en cas de grosse formules matricielles et de pb réseau il faut synchroniser pour attendre le résultat avec :
    Do Until Application.CalculationState = xlDone
DoEvents
Loop

DoEvents est lui-même consommateur de temps, c'est mieux si tu arrives à t'en passer.
A mettre donc avant la récup
eric