RechercheV en VBA avec gestion des #N/A

Bonjour,

Je souhaite pouvoir gérer les valeurs non trouvées "#N/A" dans ma formule rechercheV en VBA et je bloque.

Voici ma formule actuelle :

Code:

---

1 2 3 4 5 6 7

Sub RUN() Dim DerLig As Long With Sheets("Feuil1") DerLig = .UsedRange.Rows.Count .Range("L1:L" & DerLig).Value = WorksheetFunction.VLookup(.Range("C1:C" & DerLig).Value, Sheets("Feuil2").Range("A1:A" & DerLig, "B1:B" & DerLig), 2, False) End With End Sub

---

J'ai essayé plusieurs tests notamment avec Iff(IsError( mais ça ne fonctionne pas.

Je souhaite juste remplacer #N/A par "Non Trouvé".

Pourriez-vous m'aider ?

Merci

Bonjour,

déclare une variable Variant
n'utilises pas Vlookup de la classe Worksheetfunction mais celle de l'objet application
affecte à ce variant le résultat de la formule
teste le type de la variable retournée
si c'est une erreur, tu affiches "Non Trouvé" dans la cellule, si tu affiches le résultat de la variable

exemple illustratif, j'ai fixé derlig à 10 arbitrairement et je travaille sur la feuille active, je te laisse adapter

Code:

---

1 2 3 4 5 6 7

Sub ioi() Dim Resultat As Variant, Derlig As Long Derlig = 10 Resultat = Application.VLookup("tonton", Range("A1:A10"), 1, False) If IsError(Resultat) Then Resultat = "non précisé" Range("B1") = Resultat End Sub

---

Salut joe.levrai,

Une petite question pour éclaire ma petite bougie.

Quelle différence y a t'il entre Application.Vlookup et Application.WorksheetFunction.Vlookup ?

De plus, dans l'explorateur d'objet, quand on va dans WorksheetFunction, on trouve bien le Vlookup. Par contre dans l'objet Application ... rien.

Dans ce cas, comment savoir que le Vlookup de la classe Application est utilisable ?

Merci pour de faire briller les étoiles sur les nuits de mon ignorance (c'est issue de ce proverbe chinois : "L'ignorance est la nuit de l'esprit, et cette nuit n'a ni lune ni étoiles")

Bonjour jerome.vaussenat ,
La différence entre Application. et Application.WorksheetFunction est la gestion d'erreur. Je ne sais pas très bien l'expliquer, mais en gros :

• Si tu utilises WorksheetFunction, il faut gérer les erreurs avec On Error ...
• Si tu utilises Application seulement, tu peux déclarer ta variable de renvoie comme Variant et tester avec IsError.

Peut-être que joe.levrai saura donner plus de précisions !

Citation:

---

Dans ce cas, comment savoir que le Vlookup de la classe Application est utilisable ?

---

En fait, tout ce qui existe après Application.WorksheetFunction existe aussi avec Application. Du coup, si tu veux travailler avec Application seulement et que tu ne te souviens plus trop des syntaxes, il suffit d'écrire ton instruction avec Application.WorksheetFunction, puis d'enlever WorksheetFunction ;)

salut Riaolle,

Merci d'avoir été ma lune. On verra si le soleil vient finir le travail !

Mais je le note, je le note dans mes tablettes.

Juste une question, Riaolle, çà à une signification. A chaque fois, je dois aller vérifier pour ne pas faire d'erreur en le saisissant :oops:

Bonjour,

La documentation expliquant précisément les deux comportements est relativement pauvre.
J'ai appris "sur le tas" (comme d'habitude) surtout au travers des exemples et explications de Marc-L.

Worksheetfunction est une méthode de l'objet application, qui utilise les fonctions Excel. Une erreur dans l'évaluation de la fonction utilisée est impossible à neutraliser sans gestion d'erreur.

En passant par l'objet Application (= le parent de Worksheetfunction), on évite de passer dans la classe et en cas d'erreur d'évaluation, on peut récupérer un "objet Error", d'où le fait de toujours tester le retour avec un Variant et de regarder ce qu'il contient ensuite.

Je ne suis pas expert pointu, mais je pense que la classe Worksheetfunction ne sait pas renvoyer un objet erreur, ce qui génère l'erreur d'exécution

Bonjour à vous, bonjour au forum,

Si je peux me permettre.

Pour ma pat, je n'apprécie pas cette fonction VLOOKUP.
Ce à cause de ses contraintes (sauf erreur, 1ère colonne de matrice et tri ce cette colonne).

Je proposerais volontiers les méthodes

Code:

---

Find

---

pour la valeur cherchée, et

Code:

---

Offset

---

pour la valeur retournée.
Voir l'aide en ligne de la méthode

Code:

---

Find

---

Un retour de valeur "Non trouvé" peut alors être envisagé si

Code:

---

c is nothing

---

Revenir si nécessaire.

Merci pour vos réponses.

Merci Joe. J'ai adapté ton code mais j'ai toujours ces fameux #N/A

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

Sub ioi() Dim Resultat As Variant Dim Derlig As Long   With Feuil1 Derlig = .UsedRange.Rows.Count Resultat = Application.VLookup(Range("A1:A" & Derlig), Range("D1:D" & Derlig, "E1:E" & Derlig), 2, False) If IsError(Resultat) Then Resultat = "non précisé" Range("B1:B" & Derlig) = Resultat   End With End Sub

---

Pièce jointe 284299

Il faudrait que tu vérifies résultat par résultat si c'est une erreur.
Ou alors, tu fais comme tu fais pour l'instant, puis tu remplaces tous les #N/A par "non précisé".
--> Ctrl + H avec enregistreur de macro.

Bonjour,

ici tu appliques sur un ensemble de cellule cette mécanique, ce qui cause le problème

le mieux est donc de remplacer ensuite les #N/A comme judicieusement suggéré juste au dessus de mon message


Ou alors (ça revient quasi au même) : d'évaluer sur la feuille, de figer les résultats, puis remplacer les #N/A

Code:

---

1 2 3 4 5 6 7 8 9 10 11

Sub ioi() Dim Derlig As Long With Feuil1 Derlig = .UsedRange.Rows.Count With Range("B1:B" & Derlig) .Formula = "=VLOOKUP(A1,$D$1:$E$" & Derlig & ",2,FALSE)" .Value = .Value .Replace "#N/A", "Non Trouvé", xlWhole, , True End With End With End Sub

---

Merci beaucoup à tous et tout particulièrement à Joe.levrai.

ça fonctionne avec le dernier code.

Bonjour,

sinon tu pouvais aussi utiliser le sierreur() qui est prévu pour ça dans ta formule :

Code:

---

.Range("L1:L" & DerLig).Value = Application.IfError(WorksheetFunction.VLookup(.Range("C1:C" & derlig).Value, Sheets("Feuil2").Range("A1:A" & derlig, "B1:B" & derlig), 2, False), "non trouvé")

---

eric

Citation:

---

Envoyé par joe.levrai

Bonjour,

ici tu appliques sur un ensemble de cellule cette mécanique, ce qui cause le problème

le mieux est donc de remplacer ensuite les #N/A comme judicieusement suggéré juste au dessus de mon message


Ou alors (ça revient quasi au même) : d'évaluer sur la feuille, de figer les résultats, puis remplacer les #N/A

Code:

---

1 2 3 4 5 6 7 8 9 10 11

Sub ioi() Dim Derlig As Long With Feuil1 Derlig = .UsedRange.Rows.Count With Range("B1:B" & Derlig) .Formula = "=VLOOKUP(A1,$D$1:$E$" & Derlig & ",2,FALSE)" .Value = .Value .Replace "#N/A", "Non Trouvé", xlWhole, , True End With End With End Sub

---

---

Bonjour Joe,

Comment ferais-tu si on devait appliquer ce code sur 2 feuilles séparés s'il te plaît ?

Bonjour et bienvenue au forum :)
Il faudra faire la même chose en rajoutant juste une boucle sur les 2 feuilles pour que les actions s'exécutent sur les 2 feuilles l'une après l'autre.

Citation:

---

Envoyé par riaolle

Bonjour et bienvenue au forum :)
Il faudra faire la même chose en rajoutant juste une boucle sur les 2 feuilles pour que les actions s'exécutent sur les 2 feuilles l'une après l'autre.

---

Bonjour Riaolle,

Tout d'abord merci pour ton retour.

Pourrais-tu jeter un œil sur mon fichier en PJ s'il te plaît ? Avec le dernier post de joe.levrai, j'ai dû mal à appliquer sa fonction dans mon cas (rechercheV sur deux feuilles dans le meme Workbook)

Je m'explique : étant positionné dans la feuille "BBB", je voudrais faire une recherche V en VBA qui aille chercher des infos dans la feuille "AAA" pour les transcrire dans la feuille "BBB". Il s'agit de récupérer des dates et autre info au fur et à mesure que l'on fait de nouvelles extractions de données.

En Excel la rechercheV équivaudrait à : Pièce jointe 556848
=sierreur(recherchev(A2;AAA!A:AI;9;faux);"") => Cette formule s'affichera dans la colonne "I" de la feuille "BBB"
=sierreur(recherchev(A2;AAA!A:AI;10;faux);"") => Cette formule s'affichera dans la colonne "J" de la feuille "BBB"
=sierreur(recherchev(A2;AAA!A:AI;11;faux);"") => Cette formule s'affichera dans la colonne "K" de la feuille "BBB"
=sierreur(recherchev(A2;AAA!A:AI;34;faux);"") => Cette formule s'affichera dans la colonne "AH" de la feuille "BBB"
=sierreur(recherchev(A2;AAA!A:AI;35;faux);"") => Cette formule s'affichera dans la colonne "AI" de la feuille "BBB"

Désolée, pour des raisons de sécurité, je n'ouvre pas les PJ => https://www.developpez.net/forums/d8...s-discussions/

Est-ce que tu peux nous expliquer avec :

• Des copies d'écrans pour comprendre la structure de ton classeur.
• Ton code entre balises code (bouton #)
• Là où ça bloque => erreur dans le code ? Laquelle ? Code ne fait pas tout à fait ce que tu veux ? Que fait-il ? Que faudrait-il qu'il fasse ?

Bonjour Riaolle,

Le code VBA dans le dernier post de joe.levrai est exécutable pour une seule Sheet.

J'ai essayé de le travailler dans mon fichier perso pour qu'il aille chercher des infos sur la Sheet("AAA") pour les transcrire dans la Sheet("BBB") mais je galère à trouver la syntaxe correcte me permettant de faire un VLookup sur 2 Sheets. Du coup, en parcourant youtube, j'ai pu trouver un script que j'ai adapté et appliqué pour mon fichier. Voir plus bas.

Si tu vois qu'il est possible de faire plus simple je veux bien que tu le modifies. Parce que lorsque je l'exécute il mets un temps fou pour terminer et je vois pourquoi. Trop de VLookup et mon vrai fichier (celui de mon taff) a environ 35,000 lignes évolutives.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

Sub MAJ_WB_2020()   Dim Sh1 As Worksheet, Sh2 As Worksheet Dim Sh1LstRw As Long, Sh2LstRw As Long, x As Long Dim DataSh1 As Range   Set Sh1 = ThisWorkbook.Worksheets("AAA") Set Sh2 = ThisWorkbook.Worksheets("BBB")     Sh1LstRw = Sh1.Range("A" & Rows.Count).End(xlUp).Row Sh2LstRw = Sh2.Range("A" & Rows.Count).End(xlUp).Row   Set DataSh1 = Sh1.Range("A2:AI" & Sh1LstRw)   For x = 2 To Sh2LstRw On Error Resume Next   Sh2.Range("L" & x).Value = Application.WorksheetFunction.VLookup( _ Sh1.Range("A" & x).Value, DataSh1, 12, False)   Sh2.Range("H" & x).Value = Application.WorksheetFunction.VLookup( _ Sh1.Range("A" & x).Value, DataSh1, 8, False)   Sh2.Range("I" & x).Value = Application.WorksheetFunction.VLookup( _ Sh1.Range("A" & x).Value, DataSh1, 9, False)   Sh2.Range("J" & x).Value = Application.WorksheetFunction.VLookup( _ Sh1.Range("A" & x).Value, DataSh1, 10, False)   Sh2.Range("K" & x).Value = Application.WorksheetFunction.VLookup( _ Sh1.Range("A" & x).Value, DataSh1, 11, False)   Sh2.Range("AG" & x).Value = Application.WorksheetFunction.VLookup( _ Sh1.Range("A" & x).Value, DataSh1, 33, False)   Sh2.Range("AH" & x).Value = Application.WorksheetFunction.VLookup( _ Sh1.Range("A" & x).Value, DataSh1, 34, False)   Sh2.Range("AI" & x).Value = Application.WorksheetFunction.VLookup( _ Sh1.Range("A" & x).Value, DataSh1, 35, False)   Next x   End Sub

---

Pièce jointe 557240

Pièce jointe 557241

Je ne garantie pas que ce soit beaucoup plus rapide, mais au lieu de faire une boucle, tu peux faire avec AutoFill.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

Sub MAJ_WB_2020()   Dim Sh1 As Worksheet, Sh2 As Worksheet Dim Sh1LstRw As Long, Sh2LstRw As Long, x As Long Dim DataSh1 As Range   Set Sh1 = ThisWorkbook.Worksheets("AAA") Set Sh2 = ThisWorkbook.Worksheets("BBB")     Sh1LstRw = Sh1.Range("A" & Rows.Count).End(xlUp).Row Sh2LstRw = Sh2.Range("A" & Rows.Count).End(xlUp).Row   Set DataSh1 = Sh1.Range("A2:AI" & Sh1LstRw)   Sh2.Range("L2").Value = Application.WorksheetFunction.VLookup( _ Sh1.Range("A2").Value, DataSh1, 12, False) Sh2.Range("L2").AutoFill Destination:=Range("L2:L" & Sh2LstRw), Type:=xlFillDefault   '[...] même logique pour les autres   End Sub

---

Au passage, petite question : pourquoi y a-t-il besoin de passer par une macro ? Tu peux écrire le RECHERCHEV sur ta feuille et tirer la formule jusqu'en bas.

Bonjour Riaolle,

Merci pour ta proposition. J'ai fait un test pour la colonne "F". Le code reproduit uniquement la valeur de la cellule F2 de la Sheet AAA sur l'ensemble des lignes de la colonne "F" en Sheet BBB. J'ai essayé de le bidouiller mais j'ai des erreurs de syntaxe.

Pièce jointe 557639

Pièce jointe 557641


J'ai un problème avec les syntaxes en VBA. En générale je fait du copié-collé des codes que je trouve puis modifie les infos en fonction de mes fichiers.

Je suis plutôt dans la maintenance industriel en tant que planificateur dans le secteur minier et je cherche à améliorer mon quotidien en passant par des macros de façon à n'appuyer que sur quelques boutons pour faire mes mises à jour suite aux extractions de données que je fait.
J'ai longtemps travaillé comme tu me la suggéré, avec les formules stocker dans un fichier texte puis fait des copié-collé et les étendre ensuite. Mais je travaille sur 7 fichiers pour différent secteur. Et ça on me l'impose de le faire au moins 4 fois par jour. Donc je te laisses imaginer ma lassitude.

J'apprécie ton aide. Si tu arrives à corriger le souci que je rencontre j'en serai ravi. Mais je ne voudrai pas abuser de ton temps. C'est la dernière macro que je souhaiterai mettre en place.

Je t'en remercie

J'ai ajouté un autre screenshot de ma feuille BBB afin tu puisse voir correctement la colonne A (cellule de référence => Concaténé) en comparaison avec me feuille AAA.

Pièce jointe 557649

Bonjour,

Votre précédente macro de recherchev réécrite

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

Sub MAJ_WB_2020() Dim Sh1 As Worksheet, Sh2 As Worksheet Dim Sh1LstRw As Long, Sh2LstRw As Long, Col As Long Dim DataSh1 As Range Application.ScreenUpdating = False Set Sh1 = ThisWorkbook.Worksheets("AAA") Set Sh2 = ThisWorkbook.Worksheets("BBB") Sh1LstRw = Sh1.Range("A" & Rows.Count).End(xlUp).Row Sh2LstRw = Sh2.Range("A" & Rows.Count).End(xlUp).Row Set DataSh1 = Sh1.Range("A2:AI" & Sh1LstRw) For i = 1 To 5 Col = Application.WorksheetFunction.Choose(i, 9, 10, 11, 34, 35) Sh2.Range(Sh2.Cells(2, Col), Sh2.Cells(Sh2LstRw, Col)).FormulaR1C1 = "=IFERROR(VLOOKUP(RC1,AAA!C1:C36," & Col & ",0),"""")" Sh2.Range(Sh2.Cells(2, Col), Sh2.Cells(Sh2LstRw, Col)).Value = Sh2.Range(Sh2.Cells(2, Col), Sh2.Cells(Sh2LstRw, Col)).Value Next i Set Sh1 = Nothing Set Sh2 = Nothing End Sub

---

Cdlt

OK, je viens de voir mon erreur.
Pour que tu comprennes l'erreur, reprenons le code ci-dessous :

Code:

---

1 2 3

Sh2.Range("L2").Value = Application.WorksheetFunction.VLookup( _ Sh1.Range("A2").Value, DataSh1, 12, False) Sh2.Range("L2").AutoFill Destination:=Range("L2:L" & Sh2LstRw), Type:=xlFillDefault

---

En orange, je dis qu'il faut donner à la cellule L2 la valeur que renvoie le Vlookup. Donne je donnes une valeur fixe à L2 et non pas une formule.
Or le AutoFill permet de tirer la formule. Donc si je tire la formule d'une cellule avec une valeur fixe, je tire la valeur fixe.
Pour utiliser AutoFill, il faut donc mettre une formule dans L2.

Au passage, je viens de voir autre chose : je m'étonne un peu que la valeur recherchée dans le VLOOKUP est dans une feuille différente de celle dans laquelle on retranscrit le résultat. En général, quand on utilise VLOOKUP c'est qu'on a une donnée et que à côté on veut retranscrire une autre donnée provenant d'une base de donnée. Explication :
Pièce jointe 557668
Du coup, je pense que ton VLOOKUP ne devrait pas faire une recherche de Sh1.Range("A2").Value, mais de Sh2.Range("A2").Value.

Je dirais donc qu'il faudrait qqc comme ça :

Code:

---

1 2	Sh2.Range("L2").FormulaR1C1 = "=VLOOKUP(R2C1, AAA!R2C1:R" & Sh1LstRw & "C35,12,FALSE)" Sh2.Range("L2").AutoFill Destination:=Range("L2:L" & Sh2LstRw), Type:=xlFillDefault

---

FormulaR1C1 permet d'écrire une formule dans une cellule => voir enregistreur de macro pour avoir la syntaxe de la formule.

Bonjour Riaolle et Arturo83,

Je vous remercie pour votre temps consacré sur mon problème. Merci pour votre support.
De ce fait, le temps d'exécution de vos code est plus rapide. Donc problème résolu.

Vos codes sont bon. Je les ai enregistré dans mon fichier perso de script pour les utiliser sur des fichiers à venir.

Encore merci à tout les 2 et bon confinement


Salutations de la Nouvelle-Calédonie