VBA Méthode de Recherche parmi des milliers de données

Résolu/Fermé

Stephfun Messages postés 9 Date d'inscription mercredi 3 mars 2021 Statut Membre Dernière intervention 20 mai 2021 - 3 mars 2021 à 15:53
eriiic Messages postés 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 - 4 mars 2021 à 11:15

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

A voir également:

VBA Méthode de Recherche parmi des milliers de données
Recherche musique - Guide
Recherche automatique des chaînes ne fonctionne pas - Guide
Effacer les données de navigation - Guide
Google recherche par image - Guide
Reinstaller windows sans perte de données - Guide

5 réponses

Réponse 1 / 5

pijaku Messages postés 12263 Date d'inscription jeudi 15 mai 2008 Statut Modérateur Dernière intervention 4 janvier 2024 2 743
3 mars 2021 à 16:25

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

Stephfun Messages postés 9 Date d'inscription mercredi 3 mars 2021 Statut Membre Dernière intervention 20 mai 2021 1
3 mars 2021 à 17:03

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é.

Réponse 2 / 5

eriiic Messages postés 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 212
Modifié le 3 mars 2021 à 23:29

Bonjour,
Equiv() et Index() fonctionnent dans un classeur fermé.
Recherchev() également
Tu as tout ce qu'il te faut.
eric

Réponse 3 / 5

Stephfun Messages postés 9 Date d'inscription mercredi 3 mars 2021 Statut Membre Dernière intervention 20 mai 2021 1
Modifié le 4 mars 2021 à 09:36

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 !

eriiic Messages postés 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 212
4 mars 2021 à 11:13

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

Réponse 4 / 5

Stephfun Messages postés 9 Date d'inscription mercredi 3 mars 2021 Statut Membre Dernière intervention 20 mai 2021 1
4 mars 2021 à 10:16

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)

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question

Réponse 5 / 5

eriiic Messages postés 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 212
4 mars 2021 à 11:15

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

    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

Discussions similaires

Exemple de base de données access

[référencement] Ne pas être référencé

Trouver un nom avec un numéro de cellulaire

Erreur d'exécution 13 Incompatibilité de type

Bonjour, impossible de trouver le pilote Acquisition de données PCI

Discussions similaires

Newsletters