Insérer variable dans VLOOKUP en MACRO

Version imprimable

Bonjour,
Je rédige une MACRO.
Je souhaite automatiser une RECHERCHEV d'un fichier à un autre.

Une donnée du fichier F1 doit être rapatriée dans le fichier F2.

Nécessairement, d'un lancement à l'autre, la MACRO sera confrontée à des fichiers différents donc à des nombres de lignes différents.

Préalable :
Code:

1 2 3 4 5 Windows("F1.xlsx").Activate derniereLigneF1 = Cells(Rows.Count, 1).End(xlUp).Row Windows("F2.xlsx").Activate derniereLigneF2 = Cells(Rows.Count, 1).End(xlUp).Row
Puis :
Code:

1 2 3 4 5 6 7 Windows("F2.xlsx").Activate Range("B2").Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(RC[-1],[F1.xlsx]Feuil1!R1:R" & derniereLigneF1 & ",1,FALSE)" Selection.AutoFill Destination:=Range("B2:B" & derniereLigneF2)
La MACRO plante avec cette notation & derniereLigneF1 &.
Effectivement, si le numéro de dernière ligne était inscrite, la MACRO ne planterait pas, mais ce n'est plus dynamique.

Comment rendre dynamique l'instruction VLOOKUP ?

Merci par avance pour votre aide.

Essayer d'exploiter cet exemple en l'adaptant à vos besoins:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub vlookupDynamique()
    Dim fichier As String
    Dim sh As Worksheet
    Dim zone As String
    Dim derniereLigneF1 As Long
 
    Set sh = ThisWorkbook.Sheets("Sheet3")
    derniereLigneF1 = 25 'par exemple
    fichier = "fichier1"
    zone = """R1:R & derniereLigneF1 & """
    zone = fichier & "!Sheet3.Range(" & zone & ")"
 
    Debug.Print zone
    myFormula = "=VLOOKUP([A3]," & zone & ",1,0)"
    Debug.Print myFormula
    sh.[A10].Formula = myFormula
End Sub

Bonjour à tous,

Perso pour rendre dynamique je déclarerais mais variables en Public en dehors de la sub

exemple :
Code:

1 2 3 4 5 6 7 8 9 Public derniereLigneF1 as Long Public derniereLigneF2 as Long Sub Ation_1() Windows("F1.xlsx").Activate derniereLigneF1 = Cells(Rows.Count, 1).End(xlUp).Row Windows("F2.xlsx").Activate derniereLigneF2 = Cells(Rows.Count, 1).End(xlUp).Row
Ensuite toujours perso je ne vois pas l'intérêt d'utiliser une formule pour faire une boucle on a tout ce qu'il faut en VBA pour le faire : For ; Do Loop ........
et en plus c'est plus souple

enfin comme je dis c'est mon avis perso

tiens nous au courant pour la suite

A+

04/05/2022, 11h53
Pierre Fauconnier
Salut.

Les formules interclasseurs sont à bannir (c'est un des 7 péchés capitaux d'Excel). Il serait à mon avis plus intéressant de rapatrier les données de la source dans le classeur de travail puis d'utiliser les fonctions en local. en 2013, on peut rapatrier ces données par Power Query, paramétré éventuellement via Excel, ou par VBA.

Reconstruire des formules interclasseur par VBA est souvent le signe d'une mauvaise conception de la solution, et il est alors plus profitable de remettre l'ouvrage sur le métier. L'utilisation de tableaux structurés permettrait sans conteste d'améliorer la conception et la solution, qu'elle soit Excel/Power Query ou VBA.

A contrario d'Igloobel, je déconseille vivement les variables publiques, qui ne sont utiles que dans très peu de cas très spécifiques.

@Belga16
Citation:
Envoyé par Belga16

Code:

1 2 derniereLigneF1 = 25 'par exemple
La question du demandeur est JUSTEMENT: Comment rendre le 25 dynamique... Merci d'essayer de comprendre la demande histoire d'éviter d'y répondre de manière erronée.
05/05/2022, 15h57
Igloobel

Citation:

Envoyé par Pierre Fauconnier

... A contrario d'Igloobel, je déconseille vivement les variables publiques, qui ne sont utiles que dans très peu de cas très spécifiques.

Ah bon et pourquoi ce n'est pas bon ???

dans la plupart des codes que j'utilise je mets très souvent "Public" dans la déclaration de mes variables (quasiment tout le temps d'ailleurs). J'utilise un module que je nomme "Declaration" et je les mets tous dedans.

Je deteste les déclarations à la volée, Mais cela viens de ma formation. Je viens du monde du MainFrame avec des programmes COBOL.

Et dans le COBOL on a une zone (la WORKING STORAGE SECTION) avant le programme où on déclare toutes les variables. Du coup on a un programme bien structuré et stable. Cela gère les Banques et les assurances depuis 50 ans et il y a très peu de déchets.

Donc j'ai gardé cette façon d'opérer.

Cependant si je ne fait pas bien je reste ouvert à toutes les suggestions surtout si on m'explique pourquoi

Je suis à ton écoute Pierre ....