Comment trouver les nombres manquants dans une série Excel ?

Dans ce tutoriel, je vais vous montrer comment créer une macro VBA qui détecte automatiquement les références produits manquantes dans votre inventaire.

Cette solution va vous faire gagner un temps considérable en identifiant instantanément les numéros de référence absents de votre catalogue, même si vos données sont réparties sur plusieurs colonnes, contiennent des doublons ou des cellules vides.

Nous allons construire ensemble un outil professionnel et flexible qui s'adapte à toutes les situations.

Téléchargement

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

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, nous allons utiliser la base de facturation suivante d'un grossiste en vente de vêtements.

Ce tableau contient des numéros factures qui devraient normalement se suivre de manière séquentielle, mais une analyse rapide permet de se rendre compte que certaines ont disparu de notre fichier.

Dans cet exemple, nous constatons plusieurs particularités qui rendent la tâche complexe si nous devions chercher manuellement.

Tout d'abord, nous voyons que les données sont classées par nom de client, et donc les numéros de factures ne sont triées par ordre croissant, ce qui complique la détection visuelle des trous dans la séquence.

Enfin, si nous analysons bien, nous voyons qu’il manque plusieurs numéros dans cette séquence.

Nous allons donc souhaiter créer une petite macro qui va identifier la référence minimale et maximale, puis parcourir tous les nombres intermédiaires pour détecter ceux qui sont absents.

Cette solution nous donnera ces résultats en quelques secondes seulement.

2. Création et structure de la macro VBA

   2.1 Ouverture de l'éditeur VBA

Nous commençons par ouvrir l'éditeur Visual Basic, qui est l’outil qui va nous permettre d'écrire notre code en appuyant simultanément sur les touches [Alt]+[F11].

Si c'est la première fois que vous utilisez cet éditeur, ne vous inquiétez pas, nous allons tout détailler pas à pas.

Une fois l'éditeur ouvert, nous voyons sur la gauche un explorateur de projets qui affiche la structure de notre classeur Excel.

Nous devons maintenant insérer un nouveau module qui va contenir notre code.

Pour cela, nous faisons un clic droit sur le nom de notre projet, nous sélectionnons « Insertion », puis nous cliquons sur « Module ».

Un nouveau module apparaît avec une feuille de code vierge sur laquelle nous allons travailler.

   2.2 Déclaration de la procédure et des variables

Nous allons maintenant créer notre procédure que nous nommons « TrouverReferencesManquantes », en utilisant le mot-clé « Sub », suivi du nom que nous souhaitons attribuer à la macro.

Lorsque nous validons en appuyant sur [Entrée], Excel ajoute automatiquement la ligne « End Sub », qui vient marquer la fin de la macro.

Tout ce que nous allons saisir entre ces deux lignes sera exécuté automatiquement à chaque fois que nous appellerons la macro.

Ensuite, nous allons créer les variables utiles à l’exécution de celle-ci.

Nous saisissons le code suivant pour démarrer notre macro :

Sub TrouverReferencesManquantes()
    Dim plageEntree As Range, plageSortie As Range
    Dim i As Variant
    Dim resultats As String

En développement, une variable permet de stocker des informations, afin d’y accéder par la suite pour effectuer des traitements.

Pour guider la manière dont nous souhaitons que le code soit exécuté, nous allons attribuer un type en utilisant le mot-clé « as ».

Chaque variable a un rôle précis dans notre programme :

• La variable « plageEntree » va stocker la sélection de cellules contenant nos références produits, nous la typons donc en tant que « Range », c’est-à-dire en tant que plage de cellules
• La variable « plageSortie » contiendra l'emplacement où nous voulons afficher nos résultats.
• La variable « i » servira à passer en revue les différents éléments dans les boucles que nous allons mettre en place. Nous la typons donc en tant que Variant, c’est-à-dire que nous ne lui attribuons pas de type particulier.
• Le variable « resultats » va nous permettre d’enregistrer toutes les références manquantes au fur et à mesure que nous les découvrons, en les séparant par des points-virgules.

   2.3 Récupération de la plage d'entrée

Nous allons maintenant enregistrer les cellules sélectionnées par l’utilisateur contenant les références à analyser. Nous ajoutons ce code :

    Set plageEntree = Selection
    If plageEntree.Count <= 1 Then Exit Sub

Cette instruction enregistre la sélection dans la variable « plageEntree » et vérifie si l'utilisateur a bien sélectionné au moins deux cellules avant de lancer la macro.

Si ce n’est pas le cas, l’analyse ne sera pas pertinente, nous quittons donc immédiatement la macro avec « Exit Sub ».

3. Identification des références manquantes

3.1 Identification des valeurs manquantes entre les bornes

Maintenant que nous avons notre plage de données, nous devons identifier quelle est la référence la plus basse et la plus haute pour savoir entre quelles valeurs nous devons chercher, afin de parcourir toutes les valeurs numériques qui se trouvent entre ces bornes, et pour chacun, vérifier s'il existe dans notre plage de données.

Voici le code à ajouter :

Nous ajoutons ces lignes :

    For i =  WorksheetFunction.Min(plageEntree) To WorksheetFunction.Max(plageEntree)         
        If plageEntree.Find(i) Is Nothing  Then
            …
        End If
    Next

Ces deux fonctions Excel intégrées parcourent automatiquement toute notre plage et retournent respectivement la valeur minimale et maximale.

Nous savons donc que nous devons vérifier l'existence de tous les nombres entre ces deux bornes, incluses.

Pour cela, nous intégrons ces valeurs dans une boucle « For », qui va exécuter le code contenu entre « For » et « Next » pour chaque valeur de « i » comprises entre ces bornes.

À chaque itération, nous utilisons la méthode « Find » pour rechercher la valeur actuelle de « i » dans notre plage. *

Si « Find » ne trouve pas la valeur dans les cellules sélectionnées, alors, il nous renverra « Nothing ».

Nous pourrons donc capturer cette valeur pour l’ajouter dans la variable résultat.

        If plageEntree.Find(i) Is Nothing  Then
            resultats = resultats & i  & ";"
        End If

   3.2 Gestion des cas sans résultat

Avant de passer à l'affichage, nous devons vérifier qu'il y a effectivement des références manquantes à afficher. Nous ajoutons cette vérification :

    If resultats = "" Then
        MsgBox "Aucune référence manquante détectée. "
        Exit Sub
    End If

Si notre variable « resultat » reste vide, cela signifie que toutes les références entre le minimum et le maximum sont présentes dans notre inventaire.

Dans ce cas, nous affichons un message informatif à l'utilisateur et nous quittons la macro.

Cette étape est importante car elle évite d'afficher une erreur ou un résultat vide si tout est en ordre.

4. Affichage des résultats

   4.1 Sélection de la zone d'affichage

Nous devons maintenant demander à l'utilisateur où il souhaite voir apparaître la liste des références manquantes. Nous ajoutons donc ce code :

    Set plageSortie = Application.InputBox("Cliquez sur  la cellule de départ pour afficher les résultats", "Emplacement des  résultats", Type:=8)
    If plageSortie Is Nothing Then Exit Sub

Cette instruction utilise la méthode « InputBox » avec le paramètre « Type:=8 », ce qui indique à Excel que nous attendons une sélection de plage de cellules.

L'utilisateur peut alors cliquer avec sa souris sur Excel pour sélectionner la zone contenant ses références.

Maintenant, nous allons réduire la plage sélectionnée uniquement à la première des cellules, au cas où l’utilisateur aurait sélectionnée plusieurs cellules.

    Set plageSortie = plageSortie.Cells(1, 1)

Pour finir, il ne reste plus qu’à afficher les résultat dans la feuille Excel.

Pour cela, nous allons commencer par afficher un message dans la cellule de destination :

    plageSortie = "Voici les valeurs  manquantes :"
    Set plageSortie = plageSortie.Offset(1, 0)

Une fois le message affiché, nous passons à la ligne en utilisant l’instruction Offset qui permet d’affecter à la variable plageSortie la cellule située juste en dessous.

Puis, nous allons mettre en place une nouvelle boucle « For » pour passer en revue tous les résultats, en réutilisant à nouveau la variable « i », et en utilisant la méthode « Split » d’Excel pour scinder la chaine « resultat » en utilisant le point-virgule :

    For i in Split(resultat, ";")
       …
    Next

Enfin, il ne reste plus qu’à afficher le résultat dans la cellule plageSortie et passer à la ligne du dessous :

    For i in Split(resultat, ";")
            plageSortie = i
           Set plageSortie = plageSortie.Offset(1, 0)
    Next

Pour tester la macro, il ne reste plus qu’à revenir sur notre feuille de calcul, sélectionner les numéros de factures à analyser, puis appuyer sur [Alt]+[F8] pour afficher les liste des macros accessibles dans le classeur.

Nous sélectionnons ensuite la macro « TrouverReferencesManquantes », puis nous validons avec « Exécuter » :

La macro va alors s'exécuter et nous retourner la liste des valeurs manquantes.

Les points forts de notre solution sont nombreux :

• Premièrement, elle fonctionne quelle que soit l'organisation de vos données : une seule colonne, plusieurs colonnes, plusieurs lignes, avec des cellules vides entremêlées.
• Deuxièmement, elle gère parfaitement les doublons puisque la méthode « Find » détecte simplement la présence ou l'absence d'une valeur.
• Troisièmement, elle offre une flexibilité totale dans l'affichage des résultats, s'adaptant automatiquement à vos préférences de présentation.

Vous pouvez facilement adapter cette macro à d'autres contextes : numéros de factures séquentiels, codes employés, numéros de série de matériel, références de commandes, ou tout autre système de numérotation continue.

Il suffit de sélectionner la bonne plage de données et la macro fait le reste.