Bonsoir,

nouvelle interrogation, je veux créer une fonction pour rechercher le numéro de la dernière ligne d'une feuille excel. J'ai écrit cela:

Function Derlig(feuille As Worksheet) As Long

Derlig = Sheets("Feuil1").Cells(Rows.Count, 1).End(xlUp).Row

End Function

Quand je passe sur ma feuille excel, en B2, j'écris = Derlig(Feuil1) et j'obtiens "#valeur!" !!!!!!???

je comprends pas pourquoi, quelqu'un a une idée ?

merci

Bonjour,

Une question apparemment simple mais qui en amène pas mal d'autres...

- Dans Derlig(Feuil1), le nom Feuil1 n'est pas reconnu par Excel. Si tu dois passer le nom de la feuille en argument, il faut passer par un String "Feuil1".

- Pourquoi passer en argument le nom de la feuille si tu utilises Sheets("Feuil1").etc.. dans ton code ? (je suppose dû à tes essais).
Si tu veux faire référence à la feuille qui contient l'appel à la fonction tu peux utiliser Application.Caller.Parent.Name dans la fonction!:
Worksheets(Application.Caller.Parent.Name).etc...

- L'expression Cells(Rows.Count, 1).End(xlUp).Row universellement utilisée (ou ses variantes) est dangereuse si on ne sait pas qu'elle ne tient pas compte des lignes masquées. Masque la dernière ligne valorisée et tu verras que ton compte diminue de 1.

- De plus, telle que formulée ici, cette expression recherche la dernière ligne non masquée de la colonne 1. Et tu dis vouloir chercher la dernière ligne de la feuille Excel. Cette dernière ligne de la feuille Excel est:
Worksheets(Application.Caller.Parent.Name).UsedRange.Row + Worksheets(Application.Caller.Parent.Name).UsedRange.Rows.Count - 1
qui donne 1 si aucune ligne ! Ou mieux:
Worksheets(Application.Caller.Parent.Name).UsedRange.Find("*", , , , xlByRows, xlPrevious).Row (erreur si aucune ligne valorisée, voir ci-dessous)

Si tu veux la dernière ligne (masquée ou pas) d'un Range c'est <Range> .Find("*", , , , xlByRows, xlPrevious).Row (erreur si aucune ligne valorisée, voir ci-dessous). Par exemple:
DernièreLigne = 0
On Error Resume Next
DernièreLigne = Worksheets(Application.Caller.Parent.Name).Columns(1).Find("*", , , , xlByRows, xlPrevious).Row
On Error GoTo 0

Personnellement j'utilise ces petites fonctions pour me simplifier la vie: https://www.cjoint.com/c/IHceAVvzY8x

- Il te reste encore un problème qui est l'actualisation de la valeur de ta fonction privée dans la cellule.
Si tu ne prends pas les dispositions utiles, la valeur dans la cellule ne sera pas recalculée lorsque des lignes seront ajoutée.

Pour que la valeur soit actualisée, il faut que tu cites dans les paramètres un Range qu'Excel pourra voir et donc savoir qu'il faut recalculer la fonction si une modification est faite dans ce Range. Que ce paramètre soit utile ou non pour la fonction.

Par exemple, si tu veux afficher le nombre de lignes de la feuille Excel 2007+:
en B2 -> =Derlig($A$1:$XFD$1048576)
en VBA:
Function Derlig(R As Range) As Long 'Paramètre R inutile pour la fonction
Derlig = 0
On Error Resume Next
Derlig = Worksheets(Application.Caller.Parent.Name).UsedRange.Find("*", , , , xlByRows, xlPrevious).Row
On Error GoTo 0
End Function

ou encore:
en B2 -> =Derlig($A$1:$XFD$1048576)
en VBA:
Function Derlig(R As Range) As Long 'Paramètre R utile pour la fonction
Derlig = 0
On Error Resume Next
Derlig = R.Find("*", , , , xlByRows, xlPrevious).Row
On Error GoTo 0
End Function

Cette même dernière fonction pour afficher la dernière ligne de la colonne 1:
en B2 -> =Derlig($A:$A)

Cordialement,
D.

Merci beaucoup pour ton aide, je vais devoir passer un peu de temps pour comprendre chaque étape mais au-moins ça fonctionne !!!

Bon we

Pour compléter sur ce thème de la dernière ligne, il est possible d'utiliser une formule et se passer d'une fonction privée:

Pour la colonne A:
=MAX(SIERREUR(EQUIV(9^9;$A:$A;1);0);SIERREUR(EQUIV("zzz";$A:$A;1);0))

Pour le Range A1:A10:
=MAX(SIERREUR(EQUIV(9^9;$A$1:$A$10;1);0);SIERREUR(EQUIV("zzz";$A$1:$A$10;1);0))

Si texte seulement
==EQUIV("zzz";$A:$A)

Si nombre seulement
=EQUIV(9^9;$B:$B)

D.

Mes (15) petits lapins qui avez téléchargé ces petites fonctions pour me simplifier la vie, je dois vous confesser que j'y ai trouvé un bug Excel !

Ce bug Excel sur un "Range.Find" retourne l'adresse d'une cellule hors du Range sous certaines conditions indéterminées.
Exemple: Range($16:$16).Find("*", , , , xlByColumns, xlPrevious) retourne $E$3 qui contient une valeur certes, mais pas du tout dans le Range cherché.

Du coup, j'ai modifié les fonctions pour se protéger de cette étrance situation: https://www.cjoint.com/c/IHetnQ28Rkx

D.

Mes (7) petits lapins (de moins en moins et je les comprends ) qui avez téléchargé le dernier fichier, je dois avouer qu'il est buggé .

En effet, j'avais supprimé une instruction de la version précédente, ne sachant à quoi elle servait (ah ! les commentaires) et du coup...

C'est encore un effet du "Range.Find xlNext" qui ne trouve pas la 1ère cellule du Range s'il y a d'autres candidats. Il faut tester la 1ère cellule à part !
A noter que cette étrangeté Excel n'est pas symétrique avec le "Range.Find xlPrevious" qui trouve bien la dernière dans une config similaire.

Du coup j'ai corrigé. En ajoutant le paramètre de recherche "LookIn" (xlComments, xlFomulas, xlValues) et je suis encore tombé sur un bug Excel qui sur une recherche "Lookin = xlFomulas" dans un Range qui n'en contient pas, retourne la 1ère cellule non vide.

A force de rajouter tous ces contrôles, ça m'a paru imbuvable. J'ai donc développé de simples routines sans utiliser "Range.Find" (avec option "LookIn")

https://www.cjoint.com/c/IHqtGRzzMeL