Erreur 13 sur une macro de type recherche V

[chris6999]

Bonjour le FORUM

Il ya quelques mois une bonne âme du FORUM m'avait donné un code pour réaliser une rechercheV sur une plage non vide que j'avais gardée précieusement dans ma boite à outil Excel.
J'ai essayé de reprendre cette macro sur un cas différent mais je me heurte à quelques petits soucis qui bloquent mon projet.

Ce que j'attends :
Sur toutes lignes à partir de la ligne 6 où I non vide
Dans ma feuille Données brutes agents j'essaye de remplir les colonnes J, K,L, M : rechercheV à partir du critère qui se trouve dans I vers la feuille BD où le même critère se situe dans la colonne A.
Les valeurs renvoyées sont celles des colonnes suivantes (colonne B, C, D, E).

Mon code adapté est le suivant :
Public Sub Transfert()
Sheets("Données brutes agents").Select
num = Cells.Find("*", , , , , xlPrevious).Row
'opérations à réaliser à partir de la ligne 6
For i = 6 To num
'le critère de référence est dans la 3ème colonne
valeur = Cells(i, 9)
Dim d As Variant
'le critèe commun dans la BD est dans la colonne K
d = Application.Match(valeur, Sheets("BD").Range("A:A"), 0)
'Met dans la colonne 10 (H) la valeur de la BD colonne 3 (C)
Cells(i, 10) = Sheets("BD").Cells(d, 3)
'Met dans la colonne 9 (I) la valeur de la BD colonne 4 (D)
Cells(i, 11) = Sheets("BD").Cells(d, 4)
Cells(i, 12) = Sheets("BD").Cells(d, 5)
Cells(i, 13) = Sheets("BD").Cells(d, 6)
Next i
End Sub

Ce qui coince :
Lorsque la valeur recherchée n'existe pas dans BD j'ai une erreur d'exécution de type 13 (voir ce qui se passe dans la ligne 18 de mon fichier joint).
Je ne sais pas trop quoi faire pour éviter ce blocage je souhaiterais peut-être que dans ce cas précis renvoyer vers la notion "INCONNU".

En dehors de ce bug des lenteurs d'exécution:
Lorsque toutes les valeurs sont connues dans BD mon tableau se remplit mais très très lentement (le code semble tourner dans le vide). En fait j'ai environs 6000 lignes à traiter sans jamais savoir le nombre exact.
Dans mon fichier joint je me suis limitée à 1000 lignes.
Peut-être est-ce normal ?

Je mets un fichier en PJ qui en dira plus que de longs discours.
Si quelqu'un a une solution je suis preneuse.

Bon dimanche à tous
Cordialement

 

[john]

Re : Erreur 13 sur une macro de type recherche V

Bonjour,

Il suffit de mettre une condition dans ton code comme ci-dessous et pour la vitesse d'exécution, il faut annuler la mise à jour de l'écran pendant l'exécution, ça te permettra de gagner un peu de temps...

Public Sub Transfert()
Application.ScreenUpdating = False ' permet de figer l'écran pendant l'exécution du programme
Sheets("Données brutes agents").Select
num = Cells.Find("*", , , , , xlPrevious).Row
'opérations à réaliser à partir de la ligne 6
For i = 6 To num
'le critère de référence est dans la 3ème colonne
valeur = Cells(i, 9)
If LCase(valeur) <> "inconnu dans bd" Then 'condition pour éviter l'erreur, ici je transforme le contenu de "valeur" minuscule au cas ou la phrase dans la cellule serait écrite différemment ...
Dim d As Variant
'le critère commun dans la BD est dans la colonne K
d = Application.Match(valeur, Sheets("BD").Range("A:A"), 0)
'Met dans la colonne 10 (H) la valeur de la BD colonne 2 (B)
Cells(i, 10) = Sheets("BD").Cells(d, 2)
'Met dans la colonne 9 (I) la valeur de la BD colonne 3 (C)
Cells(i, 11) = Sheets("BD").Cells(d, 3)
Cells(i, 12) = Sheets("BD").Cells(d, 4)
Cells(i, 13) = Sheets("BD").Cells(d, 5)
End If
Next i
Cells(6, 1).Select
Application.ScreenUpdating = True
End Sub

Bonne journée.
John

 

[Bebere]

Re : Erreur 13 sur une macro de type recherche V

bonjour Chris,le forum
à tester

Public Sub Transfert()
    Dim d As Long, i As Long, num As Long

    Application.ScreenUpdating = False
    Sheets("Données brutes agents").Select
    num = Cells.Find("*", , , , , xlPrevious).Row
    'opérations à réaliser à partir de la ligne 6
    For i = 6 To num
        'le critère de référence est dans la 3ème colonne
        valeur = Cells(i, 9)
        '        i = ActiveCell.Row
        'le critèe commun dans la BD est dans la colonne K
        d = Application.Match(valeur, Sheets("BD").Range("A:A"), 0)
        If IsError(d) Then
            Cells(i, 10) = "Pas Trouvé"
        Else
            'Met dans la colonne 10 (H) la valeur de la BD colonne 2  (B)
            Cells(i, 10) = Sheets("BD").Cells(d, 2)
            'Met dans la colonne 9 (I) la valeur de la BD colonne 3 (C)
            Cells(i, 11) = Sheets("BD").Cells(d, 3)
            Cells(i, 12) = Sheets("BD").Cells(d, 4)
            Cells(i, 13) = Sheets("BD").Cells(d, 5)
        End If
    Next i
    Application.ScreenUpdating = True

End Sub

 

[Dranreb]

Re : Erreur 13 sur une macro de type recherche V

Bonjour.

Vous auriez intéret à utiliser un Dictionary (voire un dictionnaire arborescent pour y conserver les numéros de lignes), et à tout faire avec des tableaux en mémoire.

 

[Robert]

Re : Erreur 13 sur une macro de type recherche V

Bonjour le fil, bonjour le forum,

Même si très en retard sur ce coup, je te propose une autre solution avec la méthode Find (parce que j'ai du mal avec Match...) :

Public Sub Macro1()
Dim D As Object 'déclare la variable D (onglet Données brutes agents)
Dim DLD As Integer 'déclare la variable DLD (Dernière Ligne de l'onget D)
Dim PLD As Range 'déclare la variable PLD (PLage de l'onglet D)
Dim B As Object 'déclare la variable B (onglet BD)
Dim DLB As Integer 'déclare la variable DLB (Dernière Ligne de l'onget B)
Dim PLB As Range 'déclare la variable PLB (PLage de l'onglet B)
Dim CEL As Range 'déclare la variable CEL (CELlule)
Dim R As Range 'déclare la variable R (Recherche)
Dim I As Byte 'déclare la variable I (Incrément)

Application.ScreenUpdating = False 'masque les rafraîchissements d'écran
Set D = Sheets("Données brutes agents") 'définit l'onglet D
DLD = D.Cells(Application.Rows.Count, 9).End(xlUp).Row 'définit la dernière ligne édité DLD de la colonne 9 (=I) de l'onglet D
Set PLD = D.Range("I6:I" & DLD) 'définit la plage PLD
Set B = Sheets("BD") 'définit l'onglet B
DLB = B.Cells(Application.Rows.Count, 1).End(xlUp).Row 'définit la dernière ligne édité DLB de la colonne 1 (=A) de l'onglet B
Set PLB = B.Range("A4:A" & DLB) 'définit la plage PLB
For Each CEL In PLD 'boucle sur toutes les cellules CEL de la plage PLD
    'si la cellue n'est pas vide, définit la recherche R (recherche la valeur entière de la cellue CEL dans la plage PLB)
    If CEL.Value <> "" Then Set R = PLB.Find(CEL.Value, , xlValues, xlWhole)
    If Not R Is Nothing Then 'condition : si il existe au moins une occurrence trouvée
        For I = 1 To 4 'boucle sur 4 colonnes
            CEL.Offset(0, I).Value = R.Offset(0, I).Value 'récupère les valeurs des 4 colonnes décalées vers la droite de la recherche R
        Next I 'prochaine colonne
    End If 'fin de la condition
Next CEL 'prochaine cellule de la boucle
Application.ScreenUpdating = False 'affiche les rafraîchissements d'écran
End Sub

 

[Dranreb]

Re : Erreur 13 sur une macro de type recherche V

Mon code :

Public Sub Transfert()
Dim D As Dictionary, TBD(), PlgDon As Range, TCrit(), TRés(), L&, LBD&, C&
Set D = DictionnArbo(ColUti(Feuil9.[A4]))
TBD = ColUti(Feuil9.[B4:E4]).Value
Set PlgDon = PlgUti(Feuil1.Rows(6))
TCrit = PlgDon.Columns("I").Value
ReDim TRés(1 To UBound(TCrit, 1), 1 To 4)
For L = 1 To UBound(TCrit)
   If D.Exists(TCrit(L, 1)) Then
      LBD = D(TCrit(L, 1))(1)
      For C = 1 To 4: TRés(L, C) = TBD(LBD, C): Next C
   Else: TRés(L, 1) = "(INCONNU)": End If: Next L
PlgDon.Columns("J:M").Value = TRés
End Sub

Reprenez du classeur DicArbChris6999.xls que je vous avais joint, datant du 5/10/2014, les modules de services MdictionnArbo et Utilit, ainsi que le module de classe TableIndex, et cochez la référence "Microsoft Scripting Runtime".

 

[chris6999]

Re : Erreur 13 sur une macro de type recherche V

Bonjour Jean-Marcel, John, Bebere, Dranreb et Robert.

Merci pour vos propositions que je m'en vais tester.
Je vous fais un retour dès que possible.

Bonne fin d'après-midi

 

[chris6999]

Re : Erreur 13 sur une macro de type recherche V

Re

Après avoir fait mes petits tests, j'ai finalement opté pour la proposition de Jean-Marcel avec quelques petites adaptations.
Les temps de traitement sont équivallents.

Merci à tous car grâce à votre aide, j'avance (très lentement certes) sur le chemin de la compréhension.

Bonne fin de dimanche à tous les 5

 

[chris6999]

Re : Erreur 13 sur une macro de type recherche V

Merci Jean-Marcel
Ce document va m'être très utile.
Bonne journée

Bonsoir
Pour info la fonction VBA Match correspond à la fonction excel : Equiv et non pas une rechercheV
Ci joint un fichier pour t'aider