[VBA] While et Vlookup

While et Vlookup [VBA] - VB/VBA/VBS - Programmation

Marsh Posté le 12-12-2005 à 18:10:24    

Lu all  :hello:  
 
 
Je veux faire une recherche V en colonne 37 tant que la colonne 20 n'est pas vide.
 
 
Je décalre la variable i comme ligne, la valeur recherchée est en colonne 20, et la matrice de recherche est dans fichier2.xls (déjà ouvert) de la ligne 3 colonne 1 à la ligne621 colonne 11.
 
Je tappe ce code :
 

Code :
  1. i = 2                                       'Déclaration de la variable i = ligne
  2. While Cells(i, 20) <> ""                         'Tant que ligne i et colonne 20 n'est pas vide
  3.    
  4. Cells(i, 37) = ActiveCell.FormulaR1C1 = VLOOKUP(Cells(i, 20),fichier2.xls!R3C1:R621C11,11,FALSE)
  5.     i = i + 1
  6.    
  7. Wend


 
mais j'ai une erreur de compilation syntaxe
 
 
Une soluce ?
Merci bien

Reply

Marsh Posté le 12-12-2005 à 18:10:24   

Reply

Marsh Posté le 13-12-2005 à 06:34:01    

bonjour,
ça devrait donner :

Code :
  1. Sub Test()
  2. i = 4
  3. With Workbooks("fichier2.xls" ).Worksheets("TaBase" )
  4. While Cells(i, 20) <> ""
  5. Cells(i, 37) = Application.WorksheetFunction.VLookup(Cells(i, 20), .Range("A3:K621" ), 11, False)
  6. i = i + 1
  7. Wend
  8. End With
  9. End Sub

Ne pas oublier de remplacer Worksheets("TaBase" ) par le nom de ta feuille source...
A+


Message édité par galopin01 le 13-12-2005 à 06:34:18

---------------
roger
Reply

Marsh Posté le 13-12-2005 à 11:59:49    

Merci beaucoup pour cette réponse matinale fonctionnelle !!!!!
 
J'aimerais l'améliorer en disant que le range prenne toutes les cellule de"TaBase" car il se peut que des données se trouveront en dessous de la ligne 621 ..
 
On peut tres bien mettre K10000 mais je pense que ça ralentira l'execution ...

Reply

Marsh Posté le 13-12-2005 à 12:25:43    

Bah !
ça se discute, mais je ne pense pas énormément car la boucle se fait sur  
While Cells(i, 20) <> ""
et non pas sur VLOOKUP.
Si on se base sur le fait que le résultat de ton VLOOKUP à une valeur moyenne de 5000, à mon avis la différence de traitement ne doit pas excéder 4 secondes pour une colonne 20 de 10000 lignes également
Enfin c'est le résultat que je trouve sur 10000 test VLOOKUP aléatoires sur 10000 lignes...
A+


---------------
roger
Reply

Marsh Posté le 14-12-2005 à 11:40:21    

exact, la différence n'est pas visible, ici le vlookup est instantané.
 
Pour que la selection du rechercherV prenne toutes les cellules, ont me dit :  
 
à al place du range .Range("A3:K621" ) de mettre .Range.SpecialCells(xlCellTypeLastCell)
 
mais j'obtiens une erreur d'exécution 450 : Nombre d'arguments incorrect ou affectation de propriété incorrecte.
 
Une idée ?

Reply

Marsh Posté le 14-12-2005 à 19:16:37    

En général on utilise SpécialCell pour déterminer la dernière ligne écrite dans la feuille active.
On l'utilise de cette manière :

Code :
  1. Sub test()
  2. k = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
  3. MsgBox k
  4. End Sub


appliqué à ton code ça doit donner quelque chose comme :

Code :
  1. '...
  2. With Workbooks("fichier2.xls" ).Worksheets("TaBase" )
  3. k = .Cells.SpecialCells(xlCellTypeLastCell).Row
  4. While Cells(i, 20) <> ""
  5. Cells(i, 37) = Application.WorksheetFunction.VLookup(Cells(i, 20), .Range("A3:K" & k ), 11, False)
  6. ...

Ok ?


---------------
roger
Reply

Marsh Posté le 15-12-2005 à 13:18:28    

Ça fonctionne super merci !
 
Mais s'il y a un ligne vide quelque part avant la derniere ligne, le range s'arrête où ?
 

Reply

Marsh Posté le 16-12-2005 à 22:07:58    

Ben,
A la dernière ligne je pense... Quoique je n'utilise guère cette méthode effectivement un peu floue.
On utilise plutot cette méthode beaucoup plus précise pour déterminer la dernière ligne non vide d'une colonne:

Code :
  1. i = Cells(65535, 1).End(xlUp).Row

(ici la 1ère colonne)
A+


Message édité par galopin01 le 16-12-2005 à 22:08:47

---------------
roger
Reply

Marsh Posté le 07-02-2006 à 22:47:17    

Aie, cela ne fonctionne plus,
 
j'ai integré la feuille "tabase" dans le fichier excel.
 
donc je mets :
 

Code :
  1. Sub Test()
  2. i = 4
  3. With Worksheets("TaBase" )
  4. k = .Cells.SpecialCells(xlCellTypeLastCell).Row
  5. While Cells(i, 20) <> ""
  6. Cells(i, 37) = Application.WorksheetFunction.VLookup(Cells(i, 20), .Range("A3:V" & k), 11, False)
  7. i = i + 1
  8. Wend
  9. End With
  10. End Sub


 
et j'ai une erreur 1004 à la ligne de code  6
impossible de lire la proriété VLookup de la classe WorksheetFunction
 
(je suis passé d'Excel XP à Excel 2002 )


Message édité par tibot le 07-02-2006 à 23:52:04
Reply

Marsh Posté le 07-02-2006 à 23:45:12    

j'ai l'impression que ça ne cherche pas dans la bonne worksheet

Reply

Marsh Posté le 07-02-2006 à 23:45:12   

Reply

Marsh Posté le 08-02-2006 à 07:39:28    

Bonjour,
Ben, tout dépend de la feuille cible et de la feuille source
La feuille "source" du Vlookup est "adressée par :
With Worksheets("TaBase" ) tous les ".Range se réfèrent à "la source"
Les autres Range (sans le "." ) se réfèrent à la FeuilleActive au moment du lancement de la macro.
Le cas échéant, il conviendrait de préciser la feuille cible.
While Worksheets("XXXX" ).Cells(i, 20)
Worksheets("XXXX" ).Cells(i, 37) = Application.WorksheetFunction.VLookup(Worksheets("XXXX" ).Cells(i, 20)....
(à vue de nez !)
Sinon, je ne vois pas...  
Une piste, pourquoi dans :  
...Range("A3:V" & k), 11, False)  
avoir mis "V" si tu te réfères toujours à la colonne "K" (11) ?
En bonne logique V = 22
A+


Message édité par galopin01 le 08-02-2006 à 07:51:33
Reply

Marsh Posté le 08-02-2006 à 18:43:59    

bonjour et merci galopin01,
 
Le resultat de la rechercheV est dans la feuille "D1_simulact" (active au lancement de la macro = la feuille cible) colonne S (19) et valeur à chercher en colonne B (2) de la même feuille
La valeur à trouver est dans la feuille "Approv" ( source si je respecte tes notations) colonne V (22)
 
 

Code :
  1. Sub re()
  2. Dim r As Integer
  4. r = 4
  5. With Worksheets("Approv" )
  6. k = .Cells.SpecialCells(xlCellTypeLastCell).Row
  7. While Worksheets("D1_simulact" ).Cells(r, 3) <> ""
  8. Worksheets("D1_simulact" ).Cells(r, 19) = Application.WorksheetFunction.VLookup(Worksheets("D1_simulact" ).Cells(r, 2), .Range("A4:V" & k), 22, False)
  9. r = r + 1
  10. Wend
  11. End With
  12. End Sub


 
toujours la même erreur


Message édité par tibot le 08-02-2006 à 18:45:09
Reply

Marsh Posté le 08-02-2006 à 22:51:37    

Tu es bien dans un Module Standart (Module1...) ?


---------------
roger
Reply

Marsh Posté le 09-02-2006 à 00:37:40    

oui
 
macro sub dans un module
 
http://img504.imageshack.us/img504/5328/macro2ev.jpg

Reply

Marsh Posté le 09-02-2006 à 06:13:17    

Ben...  
je vois pas et puis j'ai pas trop le temps de m'amuser à monter un classeur test.
si c'est pas confidentiel ni trop volumineux, tu balances ton classeur "zippé" sur cjoint je déboguerai.
A +


---------------
roger
Reply

Marsh Posté le 11-02-2006 à 14:45:14    

ouf !
J'ai un élément d'explication :
 
En fait ça fonctionne mais s'il ne trouve pas la valeur correspondante, au lieu de mettre un #N/A ça bug comme précedemment
 
exemple tout bete :
 
http://cjoint.com/data/cloPISzU47.htm
 
Une idée pour que ça fonctionne ?
Rechercher si la valeur recherché est présente dans l'autre feuille si oui faire le recherche V sinon afficher " pas de valeur" ?
Est-ce une bonne piste ?

Reply

Marsh Posté le 11-02-2006 à 16:46:08    

bonjour,
pour  ton classeur démo remplace la ligne VLookup par :

Code :
  1. If Application.IsNA(Application.VLookup(Cells(i, 1), .Range("A2:B" & k), 2, False)) Then
  2. Cells(i, 2) = ""
  3. Else
  4. Cells(i, 2) = Application.VLookup(Cells(i, 1), .Range("A2:B" & k), 2, False)
  5. End If


---------------
roger
Reply

Sujets relatifs:

Leave a Replay

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