Discussion :

[oz2007]

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 :

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

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),"""")"

[Marc-L]

Bonjour !

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, …

[jeanmidudu]

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 :

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

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 :

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

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),"""")"

[oz2007]

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 :

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

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),"""")"

[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 :

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

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),"""")"

[oz2007]

Alors la macro

Code :

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

 "=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 JT

Merci

[jeanmidudu]

Pardon, erreur de ma part, j'ai supprimé un peu trop de trucs dans ta formule
cela devrait être mieux :

Code :

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

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.

[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

[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),"""")"

[oz2007]

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

[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 :

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

& 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 JT, 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?

[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 J1TXXX (XXX pour la dernière ligne) du fichier MSG.ASS.Q.FLUX_ASSURE_CONTRAT.
Je ne sais pas si je suis très claire

[jeanmidudu]

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 :

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

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 JT :

Code :

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

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