Atteindre la dernière cellule d’une feuille avec un lien hypertexte

Cette astuce a un but plus pédagogique que pratique.
Il est en effet évident que la solution en VBA est plus propre et simple.
L'autre solution qui consiste à utiliser la combinaison CTRL + Flèche bas est aussi plus simple (attention cependant aux arrêt intempestifs sur les cellules vides).

Vue d'ensemble des formules :

Recherche de la dernière cellule vide

Tout d'abord, on se souviendra des deux formules suivantes qui permettent d'atteindre la dernière cellule contenant :
- Soit des nombres : =EQUIV(E1+307;Feuil1!$A:$A)
- Soit du texte : =EQUIV("*";Feuil1!$A:$A;-1)

On notera que si la dernière valeur est une erreur, elle n'est pas considérée.

En combinant les deux formules, on a ensuite la dernière valeur de la colonne, à condition que cette colonne contienne à la fois des nombres et du texte :
=MAX(EQUIV("*";Feuil1!$A:$A;-1);EQUIV(E1+307;Feuil1!$A:$A))
Sinon, on obtient l'erreur #N/A

On peut donc gérer l'erreur #N/A de la manière suivante :
=MAX(
SI(ESTNA(EQUIV("*";Feuil1!$A:$A;-1));0;EQUIV("*";Feuil1!$A:$A;-1));
SI(ESTNA(EQUIV(E1+306;Feuil1!$A:$A));0;EQUIV(E1+306;Feuil1!$A:$A)))

Créer la référence en toute lettre

Il s'agit ici de trouver la référence de la cellule sous la forme A1.
Pour cela, on utilise la fonction ADRESSE :
=ADRESSE(MAX(
SI(ESTNA(EQUIV("*";Feuil1!$A:$A;-1));0;EQUIV("*";Feuil1!$A:$A;-1));
SI(ESTNA(EQUIV(E1+306;Feuil1!$A:$A));0;EQUIV(E1+306;Feuil1!$A:$A)));1)
qui renvoit donc la valeur $A$6 dans notre exemple.

Création du lien hypertexte

On peut finalement créer un lien hypertexte pointant sur A6 à l'aide de la formule suivante :
=LIEN_HYPERTEXTE("#Feuil1!$A$6";"Pointe sur A6")

et donc, en reprenant la formule précédente, nous obtenons :
=LIEN_HYPERTEXTE(
"#Feuil1!" &
ADRESSE(MAX(
SI(ESTNA(EQUIV("*";Feuil1!$A:$A;-1));0;EQUIV("*";Feuil1!$A:$A;-1));
SI(ESTNA(EQUIV(E1+306;Feuil1!$A:$A));0;EQUIV(E1+306;Feuil1!$A:$A)));1);
"Dernière ligne")

qui transformera la cellule en lien hypertexte.

Un peu de simplification ...

Là, on peut "simplifier" un peu en utilisant un nom reprenant la position de la dernière cellule.

Pour cela, dans Insertion - Nom - Définir, on va créer le nom derniere_ligne et lui attribuer la formule suivante :
="#Feuil1!" &
ADRESSE(MAX(
SI(ESTNA(EQUIV("*";Feuil1!$A:$A;-1));0;EQUIV("*";Feuil1!$A:$A;-1));
SI(ESTNA(EQUIV(Feuil1!C3+306;Feuil1!$A:$A));0;EQUIV(Feuil1!C3+306;Feuil1!$A:$A)))
;1)

Il ne reste plus qu'à écrire la formule suivante :
=LIEN_HYPERTEXTE(derniere_ligne;"Dernière ligne")

Ces formules sont bien sur un peu trop complexes par rapport au poblème initial, mais elles permettent d'ouvrir un certain nombre de possibilités quand à la création de liens hypertextes dynamiques et de recherches de dernières cellules.

Source d'inspiration - entre autres :
AndrewExcelTip

Gaetan Mourmant
http://www.polykromy.com

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !

Le défi !

Nouvelle rubrique (déjà) pour XLérateur

Il s'agit de trouver des défis sous Excel à résoudre.

Le genre de questions qui vous a pris des jours et des jours et que vous n'avez toujours par résolu...Et pourtant intuitivement, il doit y avoir une solution !!!

On va commencer avec un relativement simple :

En masquant des colonnes (sélectionner plusieurs colonnes, clic droit dessus et choisir masquer), quel est le nom le plus long que l'on puisse écrire avec les colonnes visibles restantes ?!

Mon record : 4 avec BEBE : B-E-BE 😉

Premier défi

A vous de jouer !

Gaetan Mourmant
PS : Cette idée provient d'un autre blog Anglais.

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !

Trous de mémoire…

Bonjour et bonne semaine,

Si comme moi, vous avez une mémoire très très faible (et Google ne fais qu'empirer les choses !), vous aurez besoin de ces quelques formules :

Tout d'abord, quel jour sommes-nous ?

Pour une mise à jour à chaque recalcul : =aujourdhui()
Pour une écriture en "dur" et immuable dans le temps : CTrl + ;

Après, l'autre question classique :

"Mais, combien y-a-t-il de jours au mois de Mai ?"

Soit donc en A1 une date quelqueconque de Mai. Mettons le 1er Mai, nous pouvons alors utiliser la formule suivante :
=jour(date(annee(A1) ;mois(A1)+1;0))

Si on recopie A1 vers le bas en cliquant avec le bouton droit de la souris, on obtient ceci :

Il ne reste plus qu'à recopier la formule B1 pour avoir la correspondance des mois.
On peut aussi remplacer A1 par aujourdhui() pour avoir le nombre de jours du mois courant.

Oui, mais Octobre, c'est bien le 11ème mois de l'année ?!

Si finalement, vous n'êtes pas capable de vous souvenir qu'Octobre est le 10ème mois de l'année (je crois 😉 ), il ne reste plus qu'à transformer le format de la cellule pour afficher le mois en entier.
Par exemple, en choisissant mmmm aaa (Menu - Format - Cellule...) :

et donc finalement, on a un tableau de ce genre :

Mon autre source permanente de questionnement, ce sont les années bisextiles !

D'où
=si(mois(date(annee(A1);2;29)) =2;"Année bisextile";"Année non bisextile")

On résoud le problème en regardant si le 29 février "existe" dans Excel.
D'accord, je triche un peu et je laisse Excel régler le problème, mais ca fonctionne !

Sur ce, j'espère ne rien avoir oublié (!) et vous souhaite un bon lundi.

Gaetan Mourmant
www.polykromy.com

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !

Transformer du code VBA en HTML

Bonjour,

VB HTML Maker

Si vous êtes webmaster d'un site web consacré à Excel ou au VB, il peut parfois être très utile de transformer du code VBA en HTML.

Le problème, c'est qu'en faisant un simple copier - coller, le code ne conserve ni les couleurs, ni les tabulations.

Pour y remédier, nos amis anglophones ont concocté ce petit utilitaire :

VB HTML Maker

D'où le résultat :

Option Explicit

Sub test()
'Ce module vise à démontrer l'utilisation de VB HTML
Dim a As Integer
For a = 1 To 10
    Beep
Next

End Sub

Bon amusement.

Gaetan Mourmant
www.polykromy.com

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !