> Tous les forums > Forum Bureautique
 VBA Fonction dernière ligneSujet résolu
Ajouter un message à la discussion
Page : [1] 
Page 1 sur 1
matbarns
  Posté le 01/08/2019 @ 22:18 
Aller en bas de la page 
Petit astucien

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

Publicité
Debrief
 Posté le 02/08/2019 à 07:06 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

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.



Modifié par Debrief le 02/08/2019 07:38
matbarns
 Posté le 02/08/2019 à 15:33 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

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

Bon we

Debrief
 Posté le 03/08/2019 à 11:55 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

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.

Debrief
 Posté le 04/08/2019 à 21:18 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

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.

Debrief
 Posté le 15/08/2019 à 21:15 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

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



Modifié par Debrief le 16/08/2019 21:33
Page : [1] 
Page 1 sur 1

Vous devez être connecté pour poster des messages. Cliquez ici pour vous identifier.

Vous n'avez pas de compte ? Créez-en un gratuitement !


Sujets relatifs
Changement de couleur d'une ligne en fonction du texte d'une cellule
fonction LIGNE provoque une erreur dans SOMMEPROD
Aller à la dernière ligne
La dernière ligne s'imprime à moitié (Résolu)
Renvoi à la dernière cellule non vide d'une ligne
Image conditionée par cellule fonction ligne
Recherche de la dernière valeur d'une ligne
copier avant derniere ligne en vba
Rechercher dernière cellule non vide d'une ligne
Sélection de la 2ème à la dernière ligne[RESOLU]
Plus de sujets relatifs à VBA Fonction dernière ligne
 > Tous les forums > Forum Bureautique