Macro excel recherche V et ligne variable

Version imprimable

Bonjour,

Voila j'ai écris le code suivant qui fonctionne bien si je connais le nombre de ligne exact de mon fichier. Néanmoins, comment faire pour dire à ma macro que le nombre de ligne est variable. Est ce que End(xlUp).Row peut convenir dans ce cas?
Merci d'avance
Code:

1 2 3 4 5 6 7 8 Application.Dialogs.Item(xlDialogOpen).Show Range("A1:AH" & Range("A65536").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Select Windows("Signature SEPA macro.xlsm").Activate Sheets("Feuil1").Select Range("J3").Select ActiveCell.FormulaR1C1 = _ "=IFERROR(VLOOKUP(RC[-7],[MSG.ASS.Q.FLUX_ASSURE_CONTRAT.xlsx]MSG.ASS.Q.FLUX_ASSURE_CONTRAT!R1C10:R153060C124,115,FALSE),"""")"

17/01/2018, 15h03
Marc-L

Bonjour !

Citation:

Envoyé par oz2007

Est ce que End(xlUp).Row peut convenir dans ce cas?

Oui bien sûr, selon l'adéquation avec la conception de la feuille de calculs … Voir aussi la propriété CurrentRegion …

___________________________________________________________________________________________________________________________
Je suis Paris, Egypte, Nigeria, New-York, Mogadicio, Barcelone, London, Manchester, Stockholm, Istanbul, Berlin, Nice, Bruxelles, Charlie, …

Oui, .end(xlup).row peut convenir.

comment .end(xlup).row fonctionne :
- .end(xlup) remonte jusqu'à trouver la première cellule non vide
- .row indique la ligne à laquelle on se trouve.

Dans ton code tu utilises Range("A65536") s'il se peut que tu aies plus de 65536 lignes, alors je te conseille de remplacer cette partie par

Code:

Range("A" & rows.count)

Le "rows.count" a pour effet de compter le nombre de lignes dans la feuille (lignes vides ou non vides). Au final tu devrais avoir quelque chose dans ce style :
Code:

1 2 3 4 5 6 7 8 9 Application.Dialogs.Item(xlDialogOpen).Show Range("A1:AH" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Select Windows("Signature SEPA macro.xlsm").Activate Sheets("Feuil1").Select Range("J3").Select ActiveCell.FormulaR1C1 = _ "=IFERROR(VLOOKUP(RC[-7],[MSG.ASS.Q.FLUX_ASSURE_CONTRAT.xlsx]MSG.ASS.Q.FLUX_ASSURE_CONTRAT!R1C10:R153060C124,115,FALSE),"""")"

En faite ce que je n'arrive pas à faire c'est d'intégrer le .end(xlup) dans cette formule là? Où dois-je le mettre?
Code:

1 2 3 Range("J3").Select ActiveCell.FormulaR1C1 = _ "=IFERROR(VLOOKUP(RC[-7],[MSG.ASS.Q.FLUX_ASSURE_CONTRAT.xlsx]MSG.ASS.Q.FLUX_ASSURE_CONTRAT!R1C10:R153060C124,115,FALSE),"""")"

17/01/2018, 15h23
jeanmidudu
Et bien cela dépend de la plage dans laquelle tu effectues ton VLookUp.
A titre d'exemple (à toi d'adapter selon la colonne que tu souhaites) :
Code:

1 2 "=IFERROR(VLOOKUP(RC[-7],[MSG.ASS.Q.FLUX_ASSURE_CONTRAT.xlsx]MSG.ASS.Q.FLUX_ASSURE_CONTRAT!R1C10:R" & range("R" & rows.count).end(xlup).row & "C124,115,FALSE),"""")"
17/01/2018, 16h33
oz2007

Alors la macro

Code:

"=IFERROR(VLOOKUP(RC[-7],[MSG.ASS.Q.FLUX_ASSURE_CONTRAT.xlsx]MSG.ASS.Q.FLUX_ASSURE_CONTRAT!R1C10:R" & Range("R" & Rows.Count).End(xlUp).Row & "C124,115,FALSE),"""")"

se traduit par la formule suivante: =SIERREUR(RECHERCHEV(C2;'S:\Cotisations\VERIF Signature SEPA\[MSG.ASS.Q.FLUX_ASSURE_CONTRAT.xlsx]MSG.ASS.Q.FLUX_ASSURE_CONTRAT'!$J$1:$DT$1;115;FAUX);"")

Il n'a donc pas pris en compte la plage variable (le End(xlUp).Row doit s'appliquer sur la plage J:DT

Merci
17/01/2018, 16h59
jeanmidudu
Pardon, erreur de ma part, j'ai supprimé un peu trop de trucs dans ta formule :aie:
cela devrait être mieux :
Code:

1 2 "=IFERROR(VLOOKUP(RC[-7],[MSG.ASS.Q.FLUX_ASSURE_CONTRAT.xlsx]MSG.ASS.Q.FLUX_ASSURE_CONTRAT!R1C10:R" & cells(rows.count, 124).end(xlup).row & ",115,FALSE),"""")"
Pour faire simple, si tu n'as pas compris :
R1C10 correspond à Row 1 Column 10 (soit ligne 1 colonne 10, donc à la cellule J1)
R153060C124 correspondait à Row 153060 Column 124 (soit la cellule DT153060)
Il faut donc modifier la born supérieure des lignes de ta plage, d'après tes dires, donc il faut remplacer 153060. Pourquoi utiliser cells(rows.count,124)? Pour pouvoir obtenir la dernière ligne non vide de la colonne 124 (les lignes et colonnes sont inversées par rapport à range)
Normalement, cela devrait être un peu mieux.
18/01/2018, 10h40
oz2007

Bonjour,
Merci pour ton aide. Alors je comprend bien la formule par contre cela ne fonctionne toujours pas. La macro se traduit par:

=SIERREUR(RECHERCHEV(C2;'S:\Cotisations\VERIF Signature SEPA\[MSG.ASS.Q.FLUX_ASSURE_CONTRAT.xlsx]MSG.ASS.Q.FLUX_ASSURE_CONTRAT'!$J$1:$1:$1;115;FAUX);"")

Donc il doit avoir une coquille dans formule?
Merci
18/01/2018, 11h00
jeanmidudu

Désolé, encore une erreur de ma part, j'ai oublié de remettre la partie correspondant à la deuxième colonne. Correctif :

"=IFERROR(VLOOKUP(RC[-7],[MSG.ASS.Q.FLUX_ASSURE_CONTRAT.xlsx]MSG.ASS.Q.FLUX_ASSURE_CONTRAT!R1C10:R" & cells(rows.count, 124).end(xlup).row & "C124,115,FALSE),"""")"
18/01/2018, 14h31
oz2007

1 pièce(s) jointe(s)

Bonjour

Je suis désolée mais cela ne fonctionne toujours pas :( cela ne prend pas en compte la dernière ligne
Voici donc mon fichier. la macro indique la formule en colonne J

Merci infiniment
18/01/2018, 14h52
jeanmidudu

La macro ne fonctionne pas car il n'y a aucune valeur colonne DT (colonne 124)
Pour corriger le problème, il faut remplacer le numéro de colonne par celui qui permet d'avoir la colonne dans laquelle la dernière ligne est non vide et dont la colonne est à prendre en compte.
Par exemple, si c'est dans la colonne A (1) qu'il y a la dernière cellule non vide (numéro de ligne le plus élevé) et qu'elle est à considérer, alors dans

Code:

& cells(rows.count, 124).end(xlup).row

il faut remplacer 124 par 1.
Mais c'est à toi de décider quelle colonne prendre selon tes besoins. Comme tu m'avais signalé la plage J:DT, j'avais pris en compte la dernière colonne. Essaye de remplacer par différents numéro de colonnes (non vides) pour voir la différence (évidemment, si tous les colonnes de la dernière ligne sont remplies, il n'y aura aucune différence).

Est-ce assez clair?
18/01/2018, 16h11
oz2007

Ok j'ai bien compris d'où vient mon prob avec votre explication.
Là le End(xlUp).Row s'applique sur le fichier d'origine (testmacro 1801) car c'est bien la ligne 12 qui est la première ligne vide. Or ce que je veux ce qu'il détecte la dernière ligne pour déterminer la plage dans laquelle il doit faire la recherche c'est à dire J1:DTXXX (XXX pour la dernière ligne) du fichier MSG.ASS.Q.FLUX_ASSURE_CONTRAT.
Je ne sais pas si je suis très claire :)

Oui c'est assez clair pour moi. Cependant, je ne peux pas faire grand chose de plus. C'est à toi de déterminer dans quelle colonne, parmi celles qui sont à prendre en compte, celle qui contient la dernière ligne non vide (et ensuite remplacer le numéro de colonne par le bon dans le code que je t'ai donné).

Autre approche qui pourrait peut-être te convenir, dans le cas où la totalité de ta feuille Excel est à prendre en compte :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 Dim derLign As Long, Col As Long derLign = 1 For Col = 1 To Columns.Count If derLign < Cells(Rows.Count, Col).End(xlUp).Row Then derLign = Cells(Rows.Count, Col).End(xlUp).Row End If Next Col ActiveCell.FormulaR1C1 = _ "=IFERROR(VLOOKUP(RC[-7],[MSG.ASS.Q.FLUX_ASSURE_CONTRAT.xlsx]MSG.ASS.Q.FLUX_ASSURE_CONTRAT!R1C10:R" & Cells(Rows.Count, derLign).End(xlUp).Row & "C124,115,FALSE),"""")"
Autre approche dans le cas où tu te limites à la page J:DT :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 Dim derLign As Long, Col As Long derLign = 1 For Col = 10 To 124 If derLign < Cells(Rows.Count, Col).End(xlUp).Row Then derLign = Cells(Rows.Count, Col).End(xlUp).Row End If Next Col ActiveCell.FormulaR1C1 = _ "=IFERROR(VLOOKUP(RC[-7],[MSG.ASS.Q.FLUX_ASSURE_CONTRAT.xlsx]MSG.ASS.Q.FLUX_ASSURE_CONTRAT!R1C10:R" & Cells(Rows.Count, derLign).End(xlUp).Row & "C124,115,FALSE),"""")"
Après, cela devient compliqué de t'aider...