RechercheV entre 2 feuil

Question

Bonjour, je voudrais faire une recherchev entre 2 feuilles, soit entre la feuil "Horaire Fruits" et "Fruits master2". Mon problème est que mon code semble très long et ne semble pas marché à 100%. voici le script:

Sheets("Horaire Fruits").Select ActiveWindow.SmallScroll Down:=-21 Range("H7").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R6C1,'Fruits master2'!R4C2:R6165C13,4,0)" Range("H7").Select Selection.AutoFill Destination:=Range("H7:T7"), Type:=xlFillDefault Range("H7:T7").Select Range("H7").Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(R[-1]C[-7],'Fruits master2'!R4C2:R6165C13,4,0)" Range("J7").Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(R[-1]C[-9],'Fruits master2'!R4C2:R6165C13,5,0)" Range("L7").Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(R[-1]C[-11],'Fruits master2'!R4C2:R6165C13,6,0)" Range("N7").Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(R[-1]C[-13],'Fruits master2'!R4C2:R6165C13,7,0)" Range("P7").Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(R[-1]C[-15],'Fruits master2'!R4C2:R6165C13,8,0)" Range("R7").Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(R[-1]C[-17],'Fruits master2'!R4C2:R6165C13,9,0)" Range("T7").Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(R[-1]C[-19],'Fruits master2'!R4C2:R6165C13,10,0)" Range("H7:T7").Select Selection.Copy Union(Range( _ "H106,H109,H112,H115,H118,H121,H124,H127,H130,H133,H136,H139,H142,H145,H148,H151,H154,H157,H160,H163,H166,H169,H172,H175,H178,H181,H184,H187,H190,H193,H196,H199" _ ), Range( _ "H202,H205,H208,H211,H214,H217,H220,H223,H226,H229,H232,H235,H238,H241,H244,H247,H250,H253,H256,H259,H262,H265,H268,H271,H274,H277,H280,H283,H286,H289,H292,H295" _ ), Range( _ "H298,H301,H304,H307,H310,H313,H316,H319,H322,H325,H328,H331,H334,H337,H340,H343,H346,H349,H352,H355,H358,H361,H364,H367,H370,H373,H376,H379,H382,H385,H388,H391" _ ), Range( _ "H394,H397,H400,H403,H406,H409,H412,H415,H418,H421,H424,H427,H430,H433,H436,H439,H442,H445,H448,H451,H454,H457,H460,H463,H466,H469,H472,H475,H478,H481,H484,H487" _ ), Range( _ "H490,H493,H496,H499,H502,H505,H508,H511,H514,H517,H520,H523,H526,H529,H532,H535,H538,H541,H544,H547,H550,H553,H556,H559,H562,H565,H568,H571,H574,H577,H580,H583" _ ), Range( _ "H586,H589,H592,H595,H598,H601,H604,H607,H610,H613,H616,H619,H622,H625,H628,H631,H634,H637,H640,H643,H646,H649,H652,H655,H658,H661,H664,H667,H670,H673,H676,H679" _ ), Range( _ "H682,H685,H688,H691,H694,H697,H700,H703,H706,H709,H712,H715,H718,H721,H724,H727,H730,H733,H736,H739,H742,H745,H748,H751,H754,H757,H760,H763,H766,H769,H772,H775" _ ), Range( _ "H778,H781,H784,H787,H790,H793,H796,H799,H802,H805,H808,H811,H814,H817,H820,H823,H826,H829,H832,H835,H838,H841,H844,H847,H850,H853,H856,H859,H862,H865,H868,H871" _ ), Range( _ "H874,H877,H880,H883,H886,H889,H892,H895,H898,H901,H10,H13,H16,H19,H22,H25,H28,H31,H34,H37,H40,H43,H46,H49,H52,H55,H58,H61,H64,H67,H70,H73" _ ), Range("H76,H79,H82,H85,H88,H91,H94,H97,H100,H103")).Select Range("H901").Activate Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Range("H6:T2145").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False

Je crois que je ne me suis pas bien pris avec cette rechercheV! Pouvaez-vous m'aider svp?

avyrex1926 · Answer

ps: se script provient de ma macro que je veux utiliser dans un bouton vba

cs_MPi · Answer

Salut Avyrex,

Lorsque tu utilises l'enregistreur de macro, il y a plein de choses que tu peux modifier ou améliorer.
Par exemple, les "Select"
Aussi, les Formula R1C1 peuvent être remplacés par des Formula.

Personnellement, je n'y comprend rien à ces R1C1 et je préfère utiliser le même  format que si je les écrivais dans la cellule directement.
Le seule différence est de les écrire en anglais et de mettre des virgules plutôt que des points-virgules.

Pour un début:
 '   ActiveWindow.SmallScroll Down:=-21    'inutile
'    Range("H7").Select    'inutile
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R6C1,'Fruits master2'!R4C2:R6165C13,4,0)" ' peut être remplacé par ce qui suit
'    Range("H7").Select    'inutile
'    Selection.AutoFill Destination:=Range("H7:T7"), Type:=xlFillDefault    'inutile
'    Range("H7:T7").Select    'inutile

Range("H7:H" & nbLignes).Formula = "=VLOOKUP(A6,'Fruits master2'!B:M,4,0)"
*** nbLignes étant le nombre de lignes où copier la formule (que tu peux remplacer par 6165, si je comprends bien)

MPi

JM247L · Answer

Salut,
Ca n'est valable que si la plage de recherche ne varie jamais ... Ce qui est quand même extrêmement rare dans une base de données !
Pour bien faire il faudrait définir la plage de données avant d'affecter la formule ... 
Le problème c'est quid des formules déjà existantes lorsque la plage de données va évoluer ... 
   Evolution en nombre de lignes mais aussi en 'Colonne ajoutée ou supprimée

En fait une solution serait non pas d'affecter une formule dans une cellule 
   - Ce qui en fonction de la taille de la base peut faire un fichier très lourd 
mais de n'affecter que le résultat d'une boucle qui pacourerait la plage de donnée sur "Sub Worksheet_Change(ByVal Target As Range)" ou sur le clic d'un bouton
JML
 Partageons notre savoir et nos acquis

Molenn · Answer

Ou alors, pour s'éviter les pbs de zone de recherche, il suffit d'affecter ta formule de RecehrcheV sur une zone de Nom (Menu Insertion\Nom\définir).
Tu n'as pas qu'à gérer la taille de la zone de nom à la modification de ta base de données (recréer la zone à l'initialisation de la procédure par ex., etc ...)

Molenn

avyrex1926 · Answer

Salut à tous et merci de vos réponses.

Peux-tu m'expliquer svp Molenn quand tu dit suffit d'affecter ta formule de RecehrcheV sur une zone de Nom (Menu Insertion\Nom\définir).?

Molenn · Answer

Et bien, en parlant EXCEL, la première formule que tu as posé, si je ne me trompe pas, c'est 
H7: = RECHERCHEV(I13,'Fruits master2'!J11:U6172,4,0)
(au passage, ton dernier 0 en argument me semble étrange, ça devrait TRUE ou FALSE dans ton code, et VRAI ou FAUX dans EXCEL normalement, non ?

Enfin, ça ne change rien à mon cas.
Au lieu de désigner la plage par ses références, tu peux créer une zone de nom.
Dans ta feuille 'Fruits master2', tu sélectionnes la zone J11:U6172 et tu fais Menu Insertion\Nom\Définir. Tu donnes un nom (Fruits par exemple) et tu fais ajouter.
Maintenant, quand tu sélectionneras la plage complète, dans la zone d'information (juste au dessus de A1 quand tu es dans EXCEL), au lieu d'avoir le nom d'une cellule, tu as le nom de la plage.
Et si tu sélectionnes avec la combo le nom, ta plage sera automatiquement sélectionnée.

Tu peux utiliser ce nom pour faire référence à ta plage, ta formule deviendrait :
H7: = RECHERCHEV(I13,Fruits,4,FAUX)
Plus besoin de s'occuper de savoir quelle est ta base de données, c'est toujours Fruits.

Et quand la base change, il te suffit d'aller redéfinir la zone de nom avec les nouvelles coordonnées (manuellement ou via un code VBA, c'est toi qui sait ce dont tu as besoin ^^).

Molenn

avyrex1926 · Answer

OK, par contre, tu te retrouves avec des
formules dans le fichier Excel et sur toutes les 3 lignes donc ça alourdit
beaucoup le fichier Excel non?

JM247L · Answer

Les risques sont : D'oublier le redimensionnement de la plage
 et aussi d'avoir un fichier lourd de formule ... à partir du moment ou on utilise VBA il est plus léger de programmer
Concernat le 0 en fin de formule il indique que l'on recherche une valeur exacte en opposition à 1 et -1
JML
 Partageons notre savoir et nos acquis

Molenn · Answer

Deux choses que je ne comprends pas : 

926087  avyrex1926 : je me retrouve avec des formules dans le fichier EXCEL et sur toutes les 3 lignes.
Ben, c'est aussi ce que tu fais non ? Parce que ce code : 
ActiveCell.FormulaR1C1  = "=VLOOKUP(R6C1,'Fruits master2'!R4C2:R6165C13,4,0)"
    Range("H7").Select
    Selection.AutoFill Destination:=Range("H7:T7"), Type:=xlFillDefault
place une formule dans une cellule et la copie partout.
J'ai marqué la formule en EXCEL parce que c'était plus court que le VBA, je n'ai jamais dit qu'il valait mieux le faire en formule qu'en VBA. Si tu écris : 
ActiveCell.FormulaR1C1 = "=VLOOKUP(R6C1,Fruits,4,0)"
    Range("H7").Select
    Selection.AutoFill Destination:=Range("H7:T7"), Type:=xlFillDefault
Ca revient exactement au même.

JM247L : Les risques sont : D'oublier le redimensionnement de la plage
Ben, pas plus que dans sa macro, dans laquelle il faudrait penser à remettre à jour "R4C2:R6165C13" dans chaque formule. Alors qu'avec la zone de nom, on ne touche jamais aux formules une fois écrite. Et on remet à jour la zone de nom au début, toujours pas macro VBA, rien de bien compliqué là non plus.
Personnellement, je préfère remettre à jour dans mon code VBA que 4-5 lignes que prévoir une modification sur 10 ou 15 lignes.

Pour le 0, je ne savais pas ... J'utilise toujours VRAI ou FAUX ce qui revient exactement au même à priori ^^


 


Molenn

avyrex1926 · Answer

merci.

Je fais des tests et je vous reviens

JM247L · Answer

Molenn, Pas de souci, mon commentaire sur les riques d'oubli était lié à la possibilité de faire le redimensionnement manuellement
Ma proposition était de faire une boucle sur les données afin de systématiquement mettre à jour les résultats équivalents à la formule de RECHERCHEV
Ce qui évite le dimenstionnement de plage et la lourdeur d'un fichier plein de formules

En fait utilisant depuis longtemps RECHERCHEV et je viens de constater dans l'écran d'aide qu'effectivement il préconisait VRAI ou FAUX
alors que j'en était encore à 0 et  1 ou  -1 suivant l'ordre de tri du fichier ... Mais tout cela remonte à la première version d'excel
JML
 Partageons notre savoir et nos acquis

RechercheV entre 2 feuil

11 réponses

Votre réponse

Discussions similaires