Comment obtenir les cellules à l’intersection de deux plages en VBA sur Excel

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

Au cours des différents tutoriels ou cours présents sur le blog, nous avons déjà eu l’occasion d’utiliser à de nombreuses reprises l’instruction Intersect de VBA. Celle-ci permet de récupérer l’éventuelle plage de cellulese qui se trouve à l’intersection d’au moins deux plages de cellules données. Aujourd’hui, je vous propose de revenir sur cette méthode, dont la maîtrise devient rapidement indispensable, surtout lorsque nous commençons à manipuler des évènements de VBA.

 

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

Tutoriel Vidéo

 

 

1. Présentation de la fonction Intersect()

 

La fonction Intersect est une méthode de l’objet principal de VBA, l’objet Application.

Celle-ci permet, comme nous venons de le voir dans l’introduction, de récupérer une plage d’une ou plusieurs cellules qui se trouve à l’intersection des plages passées en argument.

En d’autres termes, avec Intersect, nous allons récupérer un objet Range dans lequel nous retrouverons toutes les cellules communes aux objets Range donnés.

Pour illustrer l’utilisation que nous pouvons avoir d’Intersect, nous allons utiliser un tableau reprenant les ventes réalisées par une entreprise au cours des 12 mois de l’année 2020, réparties par région :

Excel formation - Intersect VBA - 01

Ensuite, nous allons souhaiter extraire les ventes réalisées au cours d’un mois donné, pour un pays donné.

Par exemple, les ventes réalisées en Allemagne, durant le mois d’octobre 2020.

Pour cela, la première chose à faire va consister à relever les coordonnées des plages de cellules correspondantes :

  • Les ventes réalisées en Allemagne se trouvent saisies aux coordonnées C9 à C21,

Excel formation - Intersect VBA - 02

  • Les ventes réalisées au cours du mois d’octobre sont quant à elles saisies au sein des cellules situées aux coordonnées B18 à D18 :

Excel formation - Intersect VBA - 03

Pour connaître la cellule dans laquelle se trouve les ventes correspondantes (soit la cellule C18), nous allons donc pouvoir créer une macro-commande en VBA.

Pour cela, il suffit de lancer Visual Basic Editor qui est l’outil intégré dans les applications de la suite Office et qui permet de créer des macro-commandes en utilisant le langage de programmation VBA.

Pour lancer cette application, nous pouvons par exemple utilisant le raccourci clavier [Alt]+[F11] :

Une fois dans VBE nous allons pouvoir insérer la macro-commande à deux endroits différents :

  • Soit dans le module de feuille spécifique à la feuille de calcul actuelle,
  • Soit dans un module dédié

D’une manière générale, nous préférerons utiliser les modules de feuilles pour utiliser les évènements de feuille (comme nous le verrons un peu plus tard), et saisir les autres commandes dans un module classique, ce qui permettra notamment de pouvoir les appeler depuis n’importe quelle feuille de calcul du classeur et également de pouvoir l’exporter dans un autre classeur simplement.

Nous allons donc utiliser cette seconde option, et pour cela, nous nous rendons dans le menu Insertion > Module :

Excel formation - Intersect VBA - 04

VBE nous insère alors un nouveau module (Module1) dans lequel nous allons pouvoir saisir notre macro :

Excel formation - Intersect VBA - 05

Nous créons ainsi la macro en utilisant le mot-clé « Sub », suivi du nom de la macro (par exemple « ventesAllemagneOctobre ») :

Excel formation - Intersect VBA - 06

Il suffit ensuite de valide la création de la procédure en appuyant sur la touche [Entrée] pour que VBE ajoute automatiquement la ligne « End Sub », ce qui signifie qu’à chaque fois que nous lancerons la macro, toutes les instructions situées entre ces deux lignes seront exécutées.

Pour afficher le montant des ventes, nous insérons donc la ligne suivante :

 

Sub ventesAllemagneOctobre()
    MsgBox Application.Intersect([c9:c21],  [b18:d18])
End Sub

Pour tester la macro, il suffit ensuite d’appuyer sur la touche [F5] du clavier :

Excel formation - Intersect VBA - 07

Note : L’instruction Intersect est implicitement native de l’objet Application. Il n’est donc pas utile d’appeler celui-ci :

 

Sub ventesAllemagneOctobre()
    MsgBox Intersect([c9:c21],  [b18:d18])
End Sub

 

Pour simplifier la saisie de cette instruction, il est possible d’utiliser les noms de plages de cellules automatiques, ce qui nous permettra de les appeler directement par les en-têtes de lignes et de colonnes.

Pour cela, de retour sur la feuille de calcul nous allons procéder de la manière suivante :

  • Tout d’abord, nous sélectionnons toutes les cellules du tableau :

Excel formation - Intersect VBA - 08

  • Puis nous nous rendons dans le menu Formules du ruban, afin de cliquer sur le bouton Depuis sélection dans le groupe Noms définis :

Excel formation - Intersect VBA - 09

  • Excel nous affiche alors une fenêtre afin de spécifier à partir de quels éléments nous souhaitons créer les noms de plages (nous choisissons alors d’utiliser la ligne du haut et la colonne de gauche) :

Excel formation - Intersect VBA - 10

Si en apparence nous ne voyons aucune modification, il suffit de dérouler le menu de nom de cellules (situé à gauche de la barre de saisie des formules) pour y retrouver tous les noms créés automatiquement par Excel :

Excel formation - Intersect VBA - 11

De cette manière nous allons pouvoir modifier l’instruction en appelant les plages directement avec les noms de plage :

 

Sub ventesAllemagneOctobre()
    MsgBox Intersect([Allemagne], [Octobre])
End Sub

Le résultat sera alors strictement identique, mais l’instruction est évidemment plus claire à saisir et à lire :

Excel formation - Intersect VBA - 12

 

2. Colorer les cellules situées à l’intersection

 

Maintenant imaginons que nous souhaitions colorer la couleur de fond des cellules dans lesquelles se trouvent les ventes réalisées au cours du premier trimestre pour la France.

Pour cela, nous appelons l’instruction Union qui permet de fusionner les plages passées en argument afin de créer une plage unifiée avec les trois mois Janvier/Février/Mars.

Puis après avoir utilisé Intersect pour réduire cette plage aux seules réalisées en France, nous allons en modifier la couleur de fond en utilisant la sous-propriété ColorIndex d’Interior qui est elle-même une propriété de l’objet Range généré par l’intruction Intersect :

 

Sub colorerFranceT1()
    Intersect(Union([Janvier],  [Février], [Mars]), [France]).Interior.ColorIndex = 5
End Sub

Ce qui permet de modifier les couleurs de fond des cellules concernées :

Excel formation - Intersect VBA - 13

 

3. Sommer les valeurs situées à l’intersection

 

En partant de la même méthodologie, nous pouvons également imaginer réaliser une somme des cellules situées à l’intersection de deux plages.

Pour cela, il va nous suffire d’utiliser une boucle For que nous avons déjà eu l’occasion de découvrir dans le cadre de la formation dédiée à l’apprentissage du VBA (cliquez-ici pour en savoir davantage).

Nous commençons ici par créer deux variables :

  • Une variable r qui va permettre de passer en revue toutes les cellules contenues à l’intersection des plages étudiées,
  • Une seconde variable total qui va permettre quant à elle de sommer le résultat des cellules

 

 

Sub totalFranceT1()
    Dim r As Range
    Dim total As Long
End Sub

 

Ensuite, nous allons pouvoir mettre en place une boucle pour passer en revue toutes les cellules qui nous intéressent :

 

For Each r In Intersect(Union([Janvier],  [Février], [Mars]), [France])
    Next

Puis nous réalisons la somme des cellules :

 

For Each r In Intersect(Union([Janvier],  [Février], [Mars]), [France])
        total = total + r
    Next

Et enfin nous pouvons afficher un message à l’utilisateur :

 

    MsgBox total 

 

Excel formation - Intersect VBA - 14

 

4. Intersect et la gestion des évènements

 

Comme nous l’avons déjà vu dans le chapitre dédié à la découverte des évènements de la formation dédiée à l’apprentissage de VBA, un évènement permet déclencher une série d’instructions lorsque que quelque chose se passe sur le classeur ou la feuille de calcul.

Pour l’exemple, nous allons par exemple chercher à savoir si la cellule sélectionnée appartient au tableau de données.

Pour cela, revenons dans le projet VBA, puis nous allons entrer dans le module de la feuille de calcul en double-cliquant sur la feuille en question depuis l’explorateur de projet :

Excel formation - Intersect VBA - 15

Pour ajouter automatiquement l’évènement, il suffit de sélectionner Worksheet dans le menu de sélection des objets :

Excel formation - Intersect VBA - 16

Excel ajoute alors une procédure évènementielle qui sera lancée automatiquement à chaque fois qu’une cellule va être sélectionnée :

Excel formation - Intersect VBA - 17

Comme vous pouvez le voir, la ou les cellules sélectionnées vont être retournées avec la variable Target.

Il suffit alors de vérifier si celle-ci appartient à la plage des cellules dans laquelle se trouve le tableau.

Et pour cela nous allons utiliser une particularité de Intersect : si aucune cellule ne se trouve à l’intersection des cellules passées en argument, Intersect retourne comme valeur Nothing :

 

Private Sub Worksheet_SelectionChange(ByVal Target  As Range)   If Intersect(Target, [ventes2020])  Is Nothing Then
        MsgBox "La cellule sélectionnée ne fait pas  partie du tableau"
    Else
        MsgBox "La cellule sélectionnée fait partie du  tableau"
    End If
    
End Sub

Enfin, si nous souhaitons uniquement à savoir si la cellule appartient au tableau, nous allons pouvoir réduire le test en utilisant le mot-clé Not :

 

    If Not Intersect(Target,  [ventes2020]) Is Nothing Then
        MsgBox "La cellule sélectionnée fait partie du  tableau"
    End If

De cette manière, rien ne sera affiché si la cellule sélectionnée se trouve en dehors du tableau.

Dans le cas contraire, Excel va afficher une notification pour prévenir l’utilisateur que la cellule appartient bien au tableau :

Excel formation - Intersect VBA - 18

 

 

 

 



Articles qui pourraient vous intéresser

Comment identifier les cellules identiques en 1 clic sur Excel (couleur selon valeur) ?
Comment créer un tableau de bord qui s’actualise et identifie automatiquement les meilleures performances sur Excel !
L’erreur cachée qui plombe vos tableaux Excel (et comment l’éviter)
Comment entourer automatiquement cellules non conformes Excel sans MF conditionnelle ni formule ?
Comment créer un tableau de bord commercial intelligent avec Excel ?
Comment créer un graphique dessiné à la main avec Excel ?
Comment créer un graphique de distribution en 5 minutes chrono ?
L’astuce Excel pour imprimer le tableau que vous voulez en un clic !
Comment gérer ses comptes sur Excel avec le « Suivi du budget familial »
Comment créer facilement des QR codes sans VBA avec Excel ?
Transformez vos données en graphique tornade interactif facilement sur Excel !
Comment créer un graphique Iceberg dans Excel ?

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2024 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.