[VBA]Erreur fonction VLookup

Question

Bonjour à tous,

J'utilise depuis peu VBA, et j'essaye de faire une fonction qui recherche une celulle une feuille puis écrit dans une autre feuille le résultat.

J'utilise donc une fonction "VLookup" qui doit me renvoyer le résultat j'ai testé sur un petit tableau sa marche.
Le problème lorsque j'essaye de l'utiliser sur le vrai tableau (environ 20 colonne et environ 500 ligne) sa me met une erreur au lancement:

Erreur d'éxecution '1004':

Erreur définie par l'application ou par objet

Quelqu'un peut t-il me dire d'ou vien le problème ?

je met le code de la fonction vous verez peut-être mieux ^^


Function RechercheV()
    Dim libelle As String
    Dim ressource As String

    For i = DEBUT_TAB To LIGNE_MAX Step 1
        If (Not IsEmpty(Cells(i, 2))) Then
            libelle = Worksheets("Grille de Saisie").Cells(i, 3).Value
        Else
            ressource = Worksheets("Grille de Saisie").Cells(i, 8).Value

            For j = DEBUT_TAB2 To LIGNE_MAX2 Step 1
                If Worksheets("Import Clarity").Range("B" & j).Value ressource And Worksheets("Import Clarity").Range("C" & j).Value libelle Then
                    Worksheets("Grille de Saisie").Cells(i, NUM_COLONNE).Value = Application.WorksheetFunction.VLookup(j, Worksheets("Import Clarity").Range("A7:G500"), 7)
                End If
            Next j
        End If
    Next i
End Function


Merci.

bigfish_le vrai · Answer

Salut,

pour commencer tu n'es pas dans le bon thème... pour le VBA il existe un thème dédié !

RechercheV est un nom réservé à excel! l'utiliser en tant que nom de variable ou de sub aurait pu passer mais en nom de fonction c'est une source de conflit ! Une fonction VBA est reconnue comme une des fonctions excel, donc ATTENTION !

Sur quelle ligne est renvoyée l'erreur ?

si Application.WorksheetFunction.VLookup n'a rien trouvé que se passera t-il ? 

A+

jey1319 · Answer

Oui en effet, je pensais que j'étais dans le forum VBA.

Lorsque je lance la macro sur le petit tableau il n'y a pas d'erreur donc je ne sais pas si c'est à cause du nom de la fonction je vais essayé de changer le nom.

Ensuite, l'erreur est renvoyé sur la ligne:

Worksheets("Grille de Saisie").Cells(i, NUM_COLONNE).Value = Application.WorksheetFunction.VLookup(j, Worksheets("Import Clarity").Range("A7:G500"), 7)

Si il n'a rien trouver (normalement il est obliger de trouvé sinon c'est pas normal),
il doit écrire 0 dans la case qu'il faut.

Merci pour ta réponse.

ucfoutu · Answer

Bonjour,
Ouvre ton aide VBA sur Workheetfunction ===>> membres ===>> vlookup ===>> lis cette rubrique et plus particuluèrement son paragraphe "remarques". Il y est question de l'utilisation facultative d'un quatrième argument (que tu n'as pas utilisé). 
Je doute par contre que tu puisse lui faire retourner directement- un 0 forcé.
Rien ne t'empêche par contre d'utiliser cette fonction en deux temps : 1 pour voir ce qu'elle retourne et 2 pour décider de la valeur à mettre !


________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

jey1319 · Answer

Bonjour,

Oui j'ai déjà regardé l'aide pour la fonction le dernier argument sert à rechercher une valeur approximative ou exacte, j'ai mis false pour l'instant mais quand j'execute la macro sa me met :

erreur d'éxecution '1004'
Impossible de lire la propriété Vlookup de la classe WorksheetFunction

jey1319 · Answer

Comment utilise t-on la fonction Vlookup svp ?
Car je dois mal utilisé un des paramètres.

ucfoutu · Answer

Je ne comprends pas ta question. Que te dire de plus sur cette fonction que ce qu'en dit l'aide VBA, d'une part, et la mention que j'ai faite plus haut :
Rien ne t'empêche par contre d'utiliser cette fonction en deux temps : 1 pour voir ce qu'elle retourne et 2 pour décider de la valeur à mettre !

pour gérer en fonction de l'erreur éventuellement retournée ?

Je veux bien te communiquer un lien t'en parlant et montrant l'application de ce que je t'ai dit ===>>
Tapez le texte de l'url ici.
Mais il ne t'apportera rien de plus que déjà exprimé et je ne vois d'ailleurs pas qu'en dire de plus ! Tout y est et le reste : la gestion d'erreur, c'est à toi et selon ce que TU veux retourner (ton 0, si j'ai bien compris ?)
________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

ucfoutu · Answer

Je te fais par contre remarquer que lorsque tu rentres dans ton cas else, libelle n'est pas définie, alors que tu l'utilises dans ta boucle j.
Pire : si la 1ère condition If a été vérifiée (et que donc libelle a reçu une valeur, lors d'un i, mais qu'elle ne l'est pas au passage suivant, libelle conservera la valeur du passage précédent, avec les aleas que cela peut comporter.
Et si, au tout premier i, ton premier If n'est pas vérifié (et que tu rentres donc dans ton else, libelle = "" (carrément! )


________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

jey1319 · Answer

oui c'est normal qu'il n'est pas définie dans le else jusqu'a la c'est bon c'est sur.
En revanche j'ai regardé la page que tu m'a donné, j'ai fait pareil et en faite il me trouve 1004 erreur je ne comprend pas.
Et lorque je veux afficher la variable de Vlookup c'est vide..

Je ne comprend pas trop sur le coup.


Function RechercheRess()
    Dim res As Variant
    Dim libelle As String
    Dim ressource As String

    For i = DEBUT_TAB To 39 Step 1
        If (Not IsEmpty(Cells(i, 2))) Then
            libelle = Worksheets("Grille de saisie").Cells(i, 3).Value
        Else
            ressource = Worksheets("Grille de saisie").Cells(i, 8).Value

            For j = DEBUT_TAB2 To LIGNE_MAX2 Step 1
                If Worksheets("Import Clarity").Range("B" & j).Value ressource And Worksheets("Import Clarity").Range("C" & j).Value libelle Then
                    On Error Resume Next
                    Err.Clear
                    
                    res = Application.WorksheetFunction.VLookup(j, Worksheets("Import Clarity").Range("A7:G500"), 7)
                    MsgBox res
                    If Err.Number = 0 Then
                        Worksheets("Grille de saisie").Cells(i, NUM_COLONNE).Value = res
                    End If
                End If
            Next j
        End If
    Next i
End Function


Sa rentre bien dans la boucle j
La condition est remplie quand il faut
Mais je ne rentre pas dans le dernier If (rouge)
car il me met 1004 dans Err.Number.

ucfoutu · Answer

1) Mauvaise gestion d'erreur.
2) je me demande par ailleurs pourquoi tu tiens tant à cette fonction Vlookup pour faire ce que tu veux faire.
Existe la méthode Find (ouvre ton aide VBA sur ce mot ===>> pouis rubrique Range.find ===>> lis, ainsi que l'exemple ===>> applique 



________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

ucfoutu · Answer

Et où et comment sont déclarées et définies (leur valeur) tes variables :
DEBUT_TAB, DEBUT_TAB2, LIGNE_MAX2 et NUM_COLONNE
et i et j (bien que non responsables ici) ?
?
T'es-tu mis en Option Explicit ? Si non ===>> fais-le, déjà !

Et passe donc en mode debug ! vérifie alors le contenu de chacune de tes variables !

Reste que la méthode Find me parait de toutes manières plus légère et appropriée dans ton cas.
________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

jey1319 · Answer

J'ai regardé la fonction Find, pour le moment je trouve qu'il y a plus de ligne à écrire pour la même chose.

Par contre, j'arrive à récupérer la valeur que je cherche mais je n'arrive pas à récupérer une valeur qui est sur la même ligne mais 3 colonne plus loin.

Comment dois-je faire ?

Comment je peux modifier l'adresse de la cellule ?


Function RechercheRess()
    Dim i As Integer
    Dim j As Integer
    Dim var As Variant
    Dim d As Integer
    Dim res As Integer
    Dim libelle As String
    Dim ressource As String

    For i = DEBUT_TAB To 39 Step 1
        If (Not IsEmpty(Cells(i, 2))) Then
            libelle = Worksheets("Grille de saisie").Cells(i, 3).Value
        Else
            ressource = Worksheets("Grille de saisie").Cells(i, 8).Value

            For j = 321 To 323 Step 1
                If Worksheets("Import Clarity").Range("B" & j).Value ressource And Worksheets("Import Clarity").Range("C" & j).Value libelle Then
                    'On Error Resume Next
                    'Err.Clear
                    'With ActiveWorkbook.Worksheets("Import Clarity").Range("A7:G500")
                        'Set var = .Find("PAE-TASK-FORCE-SEXTANT F-ft-4-h-mic")
                    'End With
                    
                    With Worksheets("Import Clarity").Range("A7:G500")
                        Set var = .Find("PAE-TASK-FORCE-SEXTANT F-ft-4-h-mic")
                        If Not var Is Nothing Then
                        Worksheets("Grille de saisie").Cells(i, NUM_COLONNE).Value = Worksheets("Import Clarity").Range(var.Address).Value
                        End If
                    End With
                End If
            Next j
        End If
    Next i
End Function


Merci.

jey1319 · Answer

En faite j'ai trouvé, mais je ne peux pas utilisé Find pour ce que je veux faire c'est pas fait pour.

Je veux recherché 2 String dans une plage, si les 2 String que je cherche sont trouvés (sur la même ligne), j'écris la valeur qui est 3 colonne plus loin dans une autre feuille.

La fonction précédente était plus simple non ?

ucfoutu · Answer

Je crois surtout qu'il te faut être très précis dans l'exposé de taz problèmatique.
Quand, par exemple, tu écris :
Je veux recherché 2 String dans une plage, si les 2 String que je cherche sont trouvés (sur la même ligne), j'écris la valeur qui est 3 colonne plus loin dans une autre feuille

1) Que veux-tu exactement dire ? Car plusieurs interprétations possibles, puisque tu emploies le mot "string" (chaîne de caractères) et non "valeur"
2) quelles que soient les colonnes ou dans deux colonnes bien précises ? (ce qui ne conduit pas au choix des mêmes méthodes).
3) j'aurais par ailleurs aimé que tu répondes à mes questions et remarques ! Pour mémoire :
Et où et comment sont déclarées et définies (leur valeur) tes variables :
DEBUT_TAB, DEBUT_TAB2, LIGNE_MAX2 et NUM_COLONNE
et i et j (bien que non responsables ici) ?
?
T'es-tu mis en Option Explicit ? Si non ===>> fais-le, déjà ! 
4) Dim var As Variant est très maladroit, puisqie var est finalement un objet (une cellule) !
-------------------------------------------------------
Pour conclure : si tu veux mon aide === >>
1) réponds aux questions et remarques
2) expose avec clarté et la plus grande précision et de manière purement technique  les tenants et aboutissants exacts . Fais-le de telle sorte qu'aucune question complémentaire n'ait à t'être posée pour y voir tout-à-fait clair. Un tel exposé technique ne demande en général que quelques lignes (10 au maximum).


________________________
Réponse exacte ?  => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

ucfoutu · Answer

Regarde par exemple ce que tu sembles avoir, mais n'en dit pas un mot (je devine) :
Dans ta colonne des libellés, des "trous" qui séparent un "libellé" du suivant (constituant ainsi une espèce de groupes de libellés).
Dans ta colonne des "ressources" des valeurs pour chaque groupe "libellé".
Et te voilà lancé dans un drôle de micmac assez acrobatique avec ta condition If Then Else pour pas grand-chose.
Le tout pour obtenir deux chaines (pour chaque ligne) : yune chaîne libelle et une chaine ressource.

Ouvre un projet vierge et, sur une feuille, simule donc ainsi  :
en colonne A des "libellés" (avec tes "trous" pour séparer les groupes)
en colonne B : des valeurs (avec ou sans "trous", comme tu veux - on va s'en moquer)
Un bouton de commande et ce code :
Private Sub CommandButton1_Click()
 For Each c In Range("B1:B" & Rows.Count).SpecialCells(xlCellTypeConstants)
   MsgBox "pour ma ressource de valeur " & c.Value & ", j'ai le libellé " & Range("A" & Range("A" & c.Row + 1).End(xlUp).Row).Value
 Next
End Sub

Tu vois ? Pas un seul If là-dedans, déjà 

________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

jey1319 · Answer

Escusez moi c'est vrai que je suis un peu speed et que je détaille mal.
J'ai un tableau dans une feuille (feuille A) et dans celle-ci je dois remplir uniquement une colonne.
Pour cela, je vais avoir besoin dans cette feuille de 3 colonnes, une colonne libelle, une colonne ressource et une colonne pour le résultat.

Le résultat est dans une autre feuille (feuille B), c'est un tableau et en faite pour chaque case Resultat de ma feuille A, je voulais faire :

stocker le libelle de la feuille A dans une variable
stocker la ressource de la feuille A dans une variable

rechercher le libelle dans la colonne libelle de la feuille B
et commencer la recherche de la ressource a partir de la ligne ou on trouve le libelle.
Ensuite prendre le resultat a la ligne ou se trouve le libelle et l'inscrire dans la case resultat qui convient de la feuille A.

je sais pas si c'est clair..

Donc pour répondre à tes questions c'est bien des chaines de caractères que je recherche et le résultat est du type int ou float.

J'effectue une recherche dans 2 colonne bien précise

J'ai mis Option Explicit au début (A quoi sa sert ?)

j'ai fais un GROSSIER schéma:

/url/img

code:

Option Explicit
    'Mois courant Grille de saisie
    Dim NUM_COLONNE As Integer
    
    'Mois courant Import Clarity
    Dim NUM_COLONNE2 As Integer

    'Longueur maximun du tableau
    Dim DEBUT_TAB As Integer
    
    'Longueur maximun du tableau
    Dim DEBUT_TAB2 As Integer

    'Longueur maximun du tableau
    Dim LIGNE_MAX As Integer
    
    'Longueur maximun du tableau
    Dim LIGNE_MAX2 As Integer

        NUM_COLONNE2 = 3
        
        LIGNE_MAX = 350
        LIGNE_MAX2 = 500
        
        DEBUT_TAB = 37
        DEBUT_TAB2 = 7
'Pour que tu vois les variables globales.

Function RechercheRess()
    Dim i As Integer
    Dim j As Integer
    Dim resRecherche As Variant
    Dim libRecherche As Variant
    Dim libLigne As Integer
    Dim libFinLigne As Integer
    Dim resLigne As Integer
    Dim resColonne As Integer

    Dim libelle As String
    Dim ressource As String

    For i = DEBUT_TAB To LIGNE_MAX Step 1
        If (Not IsEmpty(Cells(i, 2))) Then
            libelle = Worksheets("Grille de saisie").Cells(i, 3).Value
        Else
            ressource = Worksheets("Grille de saisie").Cells(i, 8).Value

            With Worksheets("Import Clarity").Range("C1:C500")
                Set libRecherche = .Find(libelle)
                
                If Not libRecherche Is Nothing Then
                    libLigne = libRecherche.Row - 1
                    libFinLigne = libRecherche.Row + 40
                    
                    With Worksheets("Import Clarity").Range("B" & libLigne & ":B" & libFinLigne)
                        Set resRecherche = .Find(ressource)
                        
                        If Not resRecherche Is Nothing Then
                            resLigne = resRecherche.Row
                            resColonne = resRecherche.Column + ((Month(Now) - 1) Mod 12)
                            Worksheets("Grille de saisie").Cells(i, NUM_COLONNE).Value = Worksheets("Import Clarity").Cells(resLigne, resColonne).Value
                        End If
                    End With
                End If
            End With
        End If
    Next i
End Function

ucfoutu · Answer

Désolé, mais je te laisse alors là.
Pourquoi, d'après toi ? (cherche la réponse ! si tu la trouves, tu auras fait un premier grand pas).
Bonne chance, sans moi.



________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

jey1319 · Answer

pourquoi je ne comprend pas

jey1319 · Answer

tu peux me dire vers ou chercher ?

ucfoutu · Answer

Je ne veux pas répondre au reste (et je t'avais prévenu)
Juste à cette question :
tu peux me dire vers ou chercher
Réponse : vers une remise en cause de la conception de ton projet. Car, avec ce que tu montres enfin des tenants, tes aboutissants, bien qu'à portée d'un algorithme (on peut toujours faire des "zig-zags"), nécessiteraient l'emploi d'encore une boucle, avec les lenteurs et lourdeurs que l'on peut imaginer. Et ta méthode de recherche, telle que tu la "vois" et avec ces tenants, serait loin d'aboutir à du "fiable". Voilà.
 

________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

jey1319 · Answer

Tu as raison c'est un peu n'importe quoi, j'ai commencé à coder sans vraiment réfléchir.
En faite je n'ai pas besoin de la fonction find ou quoi que se soit.

Sa marche très bien mais tu peux juste me dire si c'est déjà mieux


Function Libelle(ByRef ress As String, i As Integer) As String
    Dim j As Integer
    j = -1
    
    ress = Worksheets("Grille de saisie").Cells(i, 8).Value
    
    If Not ress "" And Not ress "XXX" Then
        Libelle = Cells(i, 8).Offset(j, -5).Value
        
        Do While Libelle = ""
            j = j - 1
            Libelle = Cells(i, 8).Offset(j, -5).Value
        Loop
    ElseIf ress = "" Then
        ress = "VIDE"
        Libelle = "DEBUT"
    ElseIf ress = "XXX" Then
        ress = "VIDE"
        Libelle = "FIN"
    Else
        MsgBox "Une erreur s'est produite"
    End If
End Function

Function Recherche()
    Dim lib As String
    Dim ress As String
    Dim ligne As Integer
    Dim cellule As Range
    
    For ligne = DEBUT_TAB To LIGNE_MAX Step 1
        lib = Libelle(ress, ligne)
        
        If Not ress = "VIDE" Then
            For Each cellule In Worksheets("Import Clarity").Range("B" & DEBUT_TAB2 & ":B" & LIGNE_MAX2)
                If Not cellule = "" Then
                    If cellule ress And cellule.Offset(0, 1) lib Then
                        Worksheets("Grille de saisie").Cells(ligne, NUM_COLONNE) = cellule.Offset(0, 5)
                    End If
                End If
            Next cellule
        End If
    Next ligne
End Function

[VBA]Erreur fonction VLookup

20 réponses

Votre réponse

Discussions similaires