RechercheV entre 2 feuil

Signaler
Messages postés
360
Date d'inscription
dimanche 3 décembre 2006
Statut
Membre
Dernière intervention
3 janvier 2012
-
Messages postés
443
Date d'inscription
mardi 27 mars 2007
Statut
Membre
Dernière intervention
1 mars 2011
-
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:

<hr size="2" width="100%" />
 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

<hr size="2" width="100%" />Je crois que je ne me suis pas bien pris avec cette rechercheV!

Pouvaez-vous m'aider svp? 

11 réponses

Messages postés
360
Date d'inscription
dimanche 3 décembre 2006
Statut
Membre
Dernière intervention
3 janvier 2012
3
ps: se script provient de ma macro que je veux utiliser dans un bouton vba
Messages postés
3877
Date d'inscription
mardi 19 mars 2002
Statut
Membre
Dernière intervention
23 août 2018
18
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
Messages postés
443
Date d'inscription
mardi 27 mars 2007
Statut
Membre
Dernière intervention
1 mars 2011
2
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
Messages postés
797
Date d'inscription
mardi 7 juin 2005
Statut
Membre
Dernière intervention
23 février 2011
5
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
Messages postés
360
Date d'inscription
dimanche 3 décembre 2006
Statut
Membre
Dernière intervention
3 janvier 2012
3
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).?
Messages postés
797
Date d'inscription
mardi 7 juin 2005
Statut
Membre
Dernière intervention
23 février 2011
5
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
Messages postés
360
Date d'inscription
dimanche 3 décembre 2006
Statut
Membre
Dernière intervention
3 janvier 2012
3
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?
Messages postés
443
Date d'inscription
mardi 27 mars 2007
Statut
Membre
Dernière intervention
1 mars 2011
2
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
Messages postés
797
Date d'inscription
mardi 7 juin 2005
Statut
Membre
Dernière intervention
23 février 2011
5
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
Messages postés
360
Date d'inscription
dimanche 3 décembre 2006
Statut
Membre
Dernière intervention
3 janvier 2012
3
merci.

Je fais des tests et je vous reviens
Messages postés
443
Date d'inscription
mardi 27 mars 2007
Statut
Membre
Dernière intervention
1 mars 2011
2
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