VBA remplacer plage statique par dynamique

J'ai besoin de votre aide sur un bout de macro créé par l'enregistreur d'excel.

Dans une cellule du Classeur1, j'ai cette formule qui fait une recherche dans une plage statique du Classeur2. Je voudrais rendre cette plage dynamique car le nombre de lignes dans Classeur2 augmente ou diminue à chaque fois comme c'est une extraction.

Range("C2").Select

ActiveCell.FormulaR1C1 = "RECHERCHEV(B2;'[Classeu2.xlsx]Feuil1'!$B$2:$B$2500;1;faux)"

Ensuite pour recopier vers le bas cette formule, j'ai mis ceci. Est-ce que c'est la bonne méthode pour recopier ?

Selection.AutoFill Destination:=RAnge("C2:C" & Range("B3").End(xlDown).Row)

merci beaucoup pour votre aide

cordialement

Bonjour,

Je ne connais pas ton degré de familiarité avec les macros ...

1. Concernant la plage dynamique, il te faut créer une variable du style : derlig=Range("A65536").End(xlUp).Row

pour ensuite l'intégrer dans ta formule ...

2. Concernant la copie, je trouve plus simple l'instruction qui reprend la plage de départ pour faire : .Copy Destination:=

Merci je vais tester ta propostion mais avant peux-tu me confirmer la syntaxe ?

derlig=Range("A65536").End(xlUp).Row

ActiveCell.FormulaR1C1 = "RECHERCHEV(B2;'[Classeu2.xlsx]Feuil1'!derlig;1;faux)"

C2.Copy Destination:=derlig

encore merci

Re,

On dirait que tu travailles avec deux fichiers ouverts ...

Il faut faire attention à tout l'adressage ...

Sans voir tes fichiers, c'est assez compliqué de deviner le bon code ...!!!

Tu peux tester le code suivant:

der = ActiveSheet.Range("C65536").End(xlUp).Row
derlig = Workbooks("Classeu2.xlsx").Sheets("Feuil1").Range("B65536").End(xlUp).Row
Range("C2").Formula = "RECHERCHEV(B2;'[Classeu2.xlsx]Feuil1'!$B$2:$B$" & derlig & ";1;faux)"
Range("C2").Copy Destination:=Range("C3:C" & der )

Si çà fonctionne du premier coup... ce sera de la chance ...

merci beaucoup, ça va marcher, j'en suis sûre

je teste et te tiens au courant

bonne journée à toi

clacha a écrit :

merci beaucoup, ça va marcher, j'en suis sûre

je teste et te tiens au courant

bonne journée à toi

Je croise les doigts pour toi ...

Au "kazou" ... il te faudra joindre tes fichiers ...

Bon Courage

J'étais trop optimiste, car j'ai eu un message "l'indice n'appartient pas à la sélection".

Je joints un exemple de fichiers pour mieux visualiser où est le pb.

Pour cet exemple, j'ai modifié cette formule de départ que tu m'avais donnée :

Range("C2").Formula = "RECHERCHEV(B2;'[Classeu2.xlsx]Feuil1'!$B$2:$C$" & derlig & ";1;faux)"

merci encore pour ton aide

Je viens de m'apercevoir que dans Classeur1, mes données sont mal positionnées pour la formule. Il faut donc que tu ajoutes une colonne vide en colonne A.

Mais apparemment ce n'est pas ça qui bloque la macro mais plutôt la variable : derlig.

Re,

Tu comprends pourquoi je t'ai dit que de correctement coder toutes les adresses est si important ...

A tester :

Sub Test()
  Workbooks("Classeur1").Activate
  der = ActiveSheet.Range("B65536").End(xlUp).Row
  derlig = Workbooks("Classeur2.xlsx").Sheets("Feuil1").Range("B65536").End(xlUp).Row
  Range("C2").Formula = "RECHERCHEV(B2;'[Classeur2.xlsx]Feuil1'!$B$2:$C$" & derlig & ";2;faux)"
  Range("C2").Copy Destination:=Range("C3:C" & der)
End Sub

La macro s'exécute et la formule est mise dans le classeur de la macro et non dans Classeur1.

Voir copie écran jointe

je me sens perdue

Bonjour,

Une proposition à étudier.

J'ai utilisé les fonctionnalités des données en tableaux (dynamiques).

Cdlt.

Option Explicit
Option Private Module
Public Sub test()
Dim strFormula As String

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    strFormula = "= VLOOKUP([@[Nat.cpt.]],Classeur2.xlsx!Tableau1[#Data],2,FALSE)"
    Workbooks("Classeur1.xlsx").Activate
    Worksheets("Feuil1").Range("B2").FormulaR1C1 = strFormula

    Application.Calculation = xlCalculationAutomatic

End Sub

clacha a écrit :

La macro s'exécute et la formule est mise dans le classeur de la macro et non dans Classeur1.

Voir copie écran jointe

je me sens perdue

Rien de bien grave ... Il te manque simplement le signe = devant RECHERCHE ...

Et ta macro doit être placée dans la Classeur 1 ...

Bon Courage

Bonjour Jean-Eric et merci pour cette proposition pour laquelle :

• j'ai mis Classeur2 en format Tableau
• la macro met bien dans Classeur1 et enn C2 la formule mais elle ne la recopie pas vers le bas
• de plus je n'obtiens pas vraiment de résultat en C2 mais ceci : = VLOOKUP([@[Nat.cpt.]],Classeur2.xlsx!Tableau1[#Data],2,FALSE)

Pour Jean-Eric

ça marche mais il faut mettre Classeur1 et Classeur2 en format Tableau.

Par contre j'ai une question, si je mets ton code dans le corps de ma macro, comme ceci, ça peut fonctionner aussi ?

merci pour ta réponse

Dim strFormula As String

strFormula = "= VLOOKUP([@[Nat.cpt.]],Classeur2.xlsx!Tableau1[#Data],2,FALSE)"

Workbooks("Classeur1.xlsx").Activate

Worksheets("Feuil1").Range("B2").FormulaR1C1 = strFormula

Bonjour,

la macro met bien dans Classeur1 et enn C2 la formule mais elle ne la recopie pas vers le bas

Dans mon exemple c'est B2. Dans ton cas C2 ne fait pas partie du tableau et la recopie ne se fait pas (normal).

[quote] de plus je n'obtiens pas vraiment de résultat en C2 mais ceci : = VLOOKUP([@[Nat.cpt.]],Classeur2.xlsx!Tableau1[#Data],2,FALSE)[/quote]

Ta cellule est formaté en texte. Formate là en standard.

Par contre j'ai une question, si je mets ton code dans le corps de ma macro, comme ceci, ça peut fonctionner aussi ?

A priori, oui. Mais je crains de ne pas comprendre ta question.

Cdlt.

Merci pour toutes ces précisions.

Ma question que tu n'as pas comprise était : la variable strFormula peut-elle être déclarée au milieu d'autres instructions de la macro ? A priori mais je voulais une confirmation.

Sinon, y a-t-il une autre alternative au fait de mettre en format Tableau mes données car ce sont des extractions brutes.

Avec ta proposition, il faut que dans la macro (au début je pense) que je convertisse toutes les données de mes différents fichiers au format tableau. J'ai 8 fichiers au total... avec des formules qui font appel à ces 8 fichiers.

Donc c'est quand même pas simple.

Re,

Je te conseille alors d'utiliser la proposition de James007...

Cdlt.

Bonjour James007

Je me permets de te solliciter encore pour le même pb que celui d'hier.

Comme tu me l'as conseillé, j'ai placé la macro dans Classeur1 et ajouté une instruction de conversion de la colonne C.

Mais la conversion n'a pas l'air de fonctionner.

Est-ce que tu aurais une idée.

Ce serait super sympa si tu pouvais regarder, si tu as 5 mn.

Je joints les deux classeurs.

merci

Bonjour Clacha,

Pas de problème ...!!!

Je suis certain qu'ensemble nous arrivons à surmonter cet obstacle ...

Dès que j'ai un petit moment, je plonge dans ta macro ...

Mais entretemps, je tiens à te féliciter pour ta persévérance ... C'est la meilleure façon d'apprendre ...!!!

Merci beaucoup James pour tes encouragements.

Si tu savais le nb de nuits blanches que j'ai passées sur Excel...

Mais ça vaut le coup !

Encore merci pour ta aide et bon dimanche

Re,

Si cela ne t'embête pas ... commençons par le commencement ...

Je constate que les comptes de la Comptabilité Nationale ainsi que leurs Désignations sont systématiquement répétés à l'identique entre 4 et 7 fois ... du coup, aucune formule de recherche ne va fonctionner ... car, pour faire simple, les formules de recherche ont besoin d'un identifiant unique ...

Pour les 11'683 lignes du Classeur2, il n'y a en fait que 26 Comptes Uniques ... (pour info, ci-joint un fichier avec la liste...)

Du coup, la vraie question ... si on oublie tout cela ... est la suivante :

Quel est ton objectif ?

Avec ce post, mon objectif était d'avoir un petit exemple de macro avec la fonction RECHERCHEV qui va chercher des données dans un autre fichier, sur une plage de données dynamique (c'est le titre de mon post).

Comme tu m'as demandé des fichiers, Je t'ai fourni les fichiers Classeur1 et Classeur2 pour faire pour ce test et n'ai pas pris le temps de retirer les doublons. Mais les doublons n'empêchaient pas la macro de fonctionner hier. C'était parce que la macro était dans un 3ème fichier, comme tu me l'as indiqué dans un des commentaires.

Avec le cas d'hier, mon but était est d'avoir un exemple pour ensuite le transposer sur la production mensuelle d'un reporting de contrats. Ce reporting faisant appel à 8 fichiers et utilisera la fonction recherchev.

Les données de ce reporting des contrats seront presque identiques à celles qui sont dans Classeur2, c'est-à-dire que que ce sont différentes extractions dont : articles, commandes...

Tu comprends mieux maintenant mon besoin et ma demande ?