Discussion :

[GéBé2022]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[Belga16]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[Igloobel]

Bonjour à tous,

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

exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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+

[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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[Igloobel]

... 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 ....