Utilisation de variables avec VLookup

Utilisation de variables avec VLookup - VB/VBA/VBS - Programmation

Marsh Posté le 09-02-2017 à 11:03:41    

Bonjour! Bonjour! [:ignition]  
 
Je me permets de vous solliciter comme la fonction VLookup que je cherche à utiliser me donne un peu de fil à retordre...  [:humanrage_2]  
 
Mais! Avant cela! Je vais vous toucher deux mots sur ce que j’essaie de faire...
Je dispose d'un fichier Excel qu'on appellera Commun et divers fichiers sources qu'on appellera pour la peine FichierSource1, FichierSource2, FichierSource3,... Les noms des fichiers pouvant varier. Ils ne sont pas sous la forme Libellé+Nombre.
Chaque fichier source FichierSourceX ressemble à :
Libellés | Valeur
Libellé1 | Valeur1
Libellé2 | Valeur2
Libellé3 | Valeur3
...
 
Je voudrais copier les valeurs de chaque FichierSourceX et pour chaque Libellé dans le fichier Commun.
Sachant que les libellés sont communs à tous les fichiers, je voudrais structurer le fichier Commun de la manière suivante :
Libellés | Source1 |Source2 |Source3 | ...
Libellé1 | Valeur1Source1 |Valeur1Source2 | Valeur1Source3 | ...
Libellé2 | Valeur2Source1 |Valeur2Source2 | Valeur2Source3 | ...
Libellé3 | Valeur3Source1 |Valeur3Source2 | Valeur3Source3 | ...
 
Plutôt que de faire une copie bête et méchante... Suis parti dans l'idée d'utiliser l'équivalent du RechercheV des fois qu'une ligne vienne à disparaitre d'un FichierSource, cela ne me décalerait pas tous les résultats.
 
Voici donc rapidement ce que je fais pour cela :

Code :
  1. Sub CopieAutoValeursSources()
  2. 'Déclaration des Variables
  3.     Dim ModeDebug As Boolean
  5.     Dim Chemin_FichierExcelSource 'As String
  6.     Dim FichierSource As Workbook
  8.     Dim Marche As String
  9.    
  10.     Dim ValeurRecherche As String
  11.     Dim PlageRecherche As Range
  13. 'Selection du fichier "Source"
  14.         Chemin_FichierExcelSource = Application.GetOpenFilename("Fichiers Excels, *.xls; *.xlsx", 1, "Selectionner le document Excel issu de Navision..." )
  15.        
  16.         If ModeDebug = True Then
  17.             If Chemin_FichierExcelSource <> False Then
  18.                 MsgBox "1 - Fichier sélectionné : " & Chemin_FichierExcelSource
  19.             Else
  20.                 MsgBox "1b - Vous n'avez pas sélectionné de fichier."
  21.             End If
  22.         End If
  24. 'Ouverture du fichier "Source"
  25.         Set FichierSource = Workbooks.Open(Chemin_FichierExcelSource, 0)
  26.         Windows(FichierSource.Name).Visible = False
  28. ' Identification du FichierSource grace à la valeur stockée dans la cellule B5 du classeur Général.
  29.         Marche = Workbooks(Chemin_FichierExcelSource).Sheets("Général" ).Range("B5" ).Value
  30.        
  31.         If ModeDebug = True Then
  32.             MsgBox "2 - B5 vaut : " & Marche
  33.         End If
  35. 'Définition en dur des variables utilisées pour la fonction VLookup
  36.         ValeurRecherche = ThisWorkbook.Worksheets("Produits" ).Range("A2" ).Value
  37.         Set PlageRecherche = FichierSource.Worksheets("Modifier - Prix vente - Groupe" ).Range("C:D" )
  39. 'Traitement
  40. Select Case Marche
  41.             'Si le fichier sélectionné est FichierSource1
  42.             Case Is = "FichierSource1"
  43.                         With ThisWorkbook
  44.                             .Sheets("Produits" ).Range("B2" ).Value = Application.WorksheetFunction.VLookup(ValeurRecherche, PlageRecherche, 2, False)
  46.                         If ModeDebug = True Then
  47.                             'MsgBox "La valeur retenue pour " & .Sheets("Produits" ).Range("A2" ).Value & " est : " &  .Sheets("Produits" ).Range("B2" ).Value
  48.                         End If
  49.                         End With
  50.                     'End Sub
  51.            
  52.             Case Is = "FichierSource2"
  53.                 MsgBox "3 - Traitement FichierSource3"
  54.            
  55.             Case Is = "FichierSource3"
  56.                 MsgBox "3 - Traitement FichierSource3"
  57.            
  58.             Case Is = "FichierSource4"
  59.                 MsgBox "3 - Traitement FichierSource4"
  60.            
  61.         End Select
  63.     ' Fermeture du document Excel Source
  64.     FichierSource.Close
  66. End Sub


 
Oui! Oui! Le code est incomplet... C'est normal! J'y travaille peu à peu ;)
 
Je bloque pour l'instant sur la ligne :
.Sheets("Produits" ).Range("B2" ).Value = Application.WorksheetFunction.VLookup(ValeurRecherche, PlageRecherche, 2, False)
 
A l’exécution de cette ligne, Microsoft Visual Basic me renvoie :
http://www.kirikoo.net/images/7Arnaud42-20170209-105259.png
 
Toutefois, si je remplace la variable ValeurRecherche qui récupère la valeur de la cellule A2 dans lequel est stocké mon Libellé1 par ledit Libellé1 en dur, mon traitement s'effectue sans encombre... J'ai donc l'impression que ma variable ValeurRecherche met à mal la fonction VLookup. Je ne comprends pas pourquoi!   [:canaille]  
 
D'autant que la ligne suivante :

Code :
  1. MsgBox "Valeur recherchée dans la fonction VLookup : " & ValeurRecherche

Renvoie bien Libellé1.
 
J'ai regardé sur la Toile. Les variables ne semblaient pas perturber le fonctionnement de la fonction VLookup pour ce que j'en ai lu. Et le message est trop générique pour en tirer quelque chose :/ Z'auriez une idée?  
En vous remerciant par avance :jap:


Message édité par Arnaud42 le 09-02-2017 à 17:35:02

---------------
[VDS] Enermax MaxRevo 1350W - TV 22" - Samsung 913V - Dock Lenovo USB-C - Clavier/Souris MX5500 [ACH] Xeon E5-1680v2
Reply

Marsh Posté le 09-02-2017 à 11:03:41   

Reply

Marsh Posté le 09-02-2017 à 11:22:50    

 
            Bonjour,
 
            si la variable contient bien le bon libellé alors c'est peut-être la plage de recherche qui n'est pas bonne …
 
            WorksheetFunction est inutile sauf pour faire planter le code en cas d'erreur !
 
            De mon côté sans souci pour un tableau commençant en A1 :  MsgBox Application.VLookup(3, [A1].CurrentRegion, 2, False)  …
 
            Aucun souci en remplaçant le 3 par une variable …     Idem pour la première colonne en alphanumérique …

Message cité 1 fois
Message édité par Marc L le 09-02-2017 à 11:26:05
Reply

Marsh Posté le 09-02-2017 à 12:07:11    

 
            L'erreur peut aussi être au début de la ligne de code, à savoir la destination de l'affectation,
            la feuille "Produits" existe-elle et au moins non protégée, à minima la cellule ?!
 

Message cité 1 fois
Reply

Marsh Posté le 09-02-2017 à 14:12:05    

Bonjour Marc L,
 
Je te remercie d'avoir jeté un oeil :jap:

Marc L a écrit :

 
            Bonjour,
 
            si la variable contient bien le bon libellé alors c'est peut-être la plage de recherche qui n'est pas bonne …


Yep! C'est une idée! Je ne pense toutefois pas que cela provienne de la variable PlageRecherche non plus. Ce qui me fait dire cela, c'est que lorsque je renseigne la valeur recherchée en dur :

Code :
  1. With ThisWorkbook
  2.           .Sheets("Produits" ).Range("B2" ).Value = Application.WorksheetFunction.VLookup("Libellé1", PlageRecherche, 2, False)
  3. End With


 
La cellule A2 du fichier Commun prenait bien pour valeur celle associée à Libellé1 dans le document FichierSource. Libellé1 qui se trouve d'ailleurs à la ligne 1108 (Oui! J'ai voulu faire un tests :D)
La variable PlageRecherche semble donc pleinement fonctionnelle.
 

Marc L a écrit :

 
            WorksheetFunction est inutile sauf pour faire planter le code en cas d'erreur !


 
Euh... Tu ne crois pas si bien dire! :o :D
Je viens de retirer la mention WorksheetFunction. et tout a fonctionné normalement... J'imagine que Microsoft Visual Basic refusait simplement d'affecter un Objet (WorksheetFunction) comme valeur de la cellule B2?  [:arn0]  
 

Marc L a écrit :


            De mon côté sans souci pour un tableau commençant en A1 :  MsgBox Application.VLookup(3, [A1].CurrentRegion, 2, False)  …
 
            Aucun souci en remplaçant le 3 par une variable …     Idem pour la première colonne en alphanumérique …


Ouep! Ouep! J'ai lu pas mal de topic où étaient utilisées des variables. Cela ne posait de problème à personne! C'pour cela que cela m'étonnait que Microsoft Visual Basic râle à l'utilisation de deux petites variables  [:thektulu]  
 

Marc L a écrit :

 
            L'erreur peut aussi être au début de la ligne de code, à savoir la destination de l'affectation,
            la feuille "Produits" existe-elle et au moins non protégée, à minima la cellule ?!
 


Yep! J'ai bien vérifié de ne pas faire d'erreur lors de l'appel des différentes feuilles ;)
 
Bon! Et bien il n'y a plus qu'à continuer vu que cela fonctionne bien sans le WorksheetFunction. La suite au prochaine épisode!
Merci en tout cas :jap:


Message édité par Arnaud42 le 09-02-2017 à 14:17:20

---------------
[VDS] Enermax MaxRevo 1350W - TV 22" - Samsung 913V - Dock Lenovo USB-C - Clavier/Souris MX5500 [ACH] Xeon E5-1680v2
Reply

Marsh Posté le 02-03-2017 à 12:47:04    

Bonjour! Bonjour!  [:ignition]

 

Je reviens vers vous comme Visual Basic me donne encore un peu de fil à retordre!
Suite à mon dernier message (Faut lire ce qui est au dessus :o), j'ai quelque peu avancé et suis arrivé à quelque chose de pleinement fonctionnel  [:volta]

 

J'vous rappelle rapidement et dans les grandes lignes que je cherche à automatiser la copie de données d'un fichier Excel FichierSourceX dans un fichier Excel Commun à l'aide d'une fonction VLookUp.
Sur le papier, cela donnait :

Code :
  1. Set PlageRecherche = FichierSource.Sheets("Modifier - Prix vente - Groupe" ).Range("C:D" )
  2. ValeurRecherche = .Worksheets("Produits" ).Range("A" & NumLign).Value
  3. ...
  4. .Worksheets("Produits" ).Range(LettreColonne & NumLign).Value = Application.VLookup(ValeurRecherche, PlageRecherche, 2, False)
 

Avec cela, je récupérais le prix situé dans mon FichierSourceX, dans le classeur "Modifier - Prix vente - Groupe", à la colonne D correspondant au libellé situé dans mon fichier Commun, classeur "Produits", colonne A.
Sauf qu'on vient de me prévenir que le format des FichierSourceX a changé. :D Le Libellé se trouve à présent dans la colonne D (A la place de C jusque là) et la Valeur se retrouve dans la colonne K (A la place de D jusqu'ici).
Qu'à cela ne tienne! Je pensais alors qu'il suffisait d'adapter la fonction VLookup avec une plage qui va de D à K et lui dire de récupérer les données de la colonne 8 de cette plage (Colonne K) :

Code :
  1. Set PlageRecherche = FichierSource.Sheets("Modifier - Prix vente - Groupe" ).Range("D:K" )
  2. ValeurRecherche = .Worksheets("Produits" ).Range("A" & NumLign).Value
  3. ...
  4. .Worksheets("Produits" ).Range(LettreColonne & NumLign).Value = Application.VLookup(ValeurRecherche, PlageRecherche, 8, False)
 

Mais cela ne se passe pas aussi bien qu'espéré puisque Visual Basic me retourne :
http://img4.hostingpics.net/pics/578931VisualBasicErreur13IncompatibilitdeType.png

 


Incompatibilité de Type... Okay! Mais! Comment puis-je arriver à une pareille erreur en modifiant juste la plage de recherche et la colonne de résultat? D'autant que je stocke cela dans le .Value d'une cellule de mon classeur Commun. Parce que VLookup ne retourne pas un Integer ou un String qui puisse être stockée dans ma cellule?

 

Je viens pourtant de faire un RechercheV sur ce nouveau fichier FichierSource qui m'a retourné le résultat attendu.

 

Une idée?


Message édité par Arnaud42 le 02-03-2017 à 12:57:12

---------------
[VDS] Enermax MaxRevo 1350W - TV 22" - Samsung 913V - Dock Lenovo USB-C - Clavier/Souris MX5500 [ACH] Xeon E5-1680v2
Reply

Marsh Posté le 02-03-2017 à 13:40:42    

 
            Bonjour,
 
            que renvoie le MsgBox du VLookup comme déjà indiqué plus haut ?
 
            Et que se passe-t-il s'il n'y a pas de correspondance ?
 
            Vérifier la formule entrée manuellement avec le contenu des variables de la ligne déclenchant l'erreur …


Message édité par Marc L le 02-03-2017 à 13:57:15
Reply

Marsh Posté le 02-03-2017 à 15:04:47    

Je te remercie :jap:
 
Le MsgBox du VLookup renvoie le même message que celui cité au dessus, à savoir :
http://img4.hostingpics.net/pics/578931VisualBasicErreur13IncompatibilitdeType.png
 
S'il n'y a pas de correspondance, ce qui peut tout à fait arriver si un Libellé n'est plus répertorié, j'ai pallié à cela grâce à la commande :

Code :
  1. On Error GoTo Erreurs


J'y traitais ainsi l'erreur 13 que me générait le VLookup lorsqu'il ne trouvait aucune occurrence de mon libellé dans PlageRecherche.
 
Si je remplace les variables par leur contenu dans l'expression du VLookup, le résultat est le même.
Se pourrait-il que le format du résultat du VLookup ne soit pas au format attendu?


---------------
[VDS] Enermax MaxRevo 1350W - TV 22" - Samsung 913V - Dock Lenovo USB-C - Clavier/Souris MX5500 [ACH] Xeon E5-1680v2
Reply

Marsh Posté le 02-03-2017 à 15:54:28    

 
            De mon côté et sans On Error s'il n'y a pas de correspondance cela ne déclenche pas d'erreur
            mais renvoie juste une valeur d'erreur #N/A comme renverrait une formule RECHERCHEV dans une cellule …
 
            On Error est inutile avec Application.VLookup sinon autant en rester avec WorksheetFunction !
 
            Avant la ligne déclenchant l'erreur, afficher le contenu de chaque variable la constituant
            via Debug.Print, l'affichage étant effectué dans la fenêtre Exécution …
 

Reply

Marsh Posté le 02-03-2017 à 18:15:35    

 
            Lève moi le doute :  c'est bien une version Windows d'Excel ?
 

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed