XL 2019 vba trouver un nombre manquant dans une suite donnee

maguie · 17 Février 2021

Bonjour,
Je vous explique ma problématique. J'ai une suite de nombre (numéro de facture : 2018001) et je souhaiterais trouver les numéros de factures manquants dans une suite donnée avec un minimum et un maximum sachant que ce sont des grands nombres.
exemple de numéros : [2018001,2018002,20018004, 20180005..., 20180050]. Ma problématique est de trouver par exemple les numéros de facture manquants en précisant que le début est 20180001 et la fin 20180050. Et que la fonction vba doit rechercher ce nombre dans cet intervalle uniquement.
Par avance merci
Cordialement

Phil69970 · 17 Février 2021

Bonjour maguie, le forum

Je te propose ceci:
Je compare le N° avec le n° suivant -1 si les numéro sont égaux il y a pas de trou sinon il manque une facture ....

@Phil69970

JHA · 17 Février 2021

Bonjour à tous,

Un début de piste

JHA

maguie · 17 Février 2021

Bonjour,
Je vous remercie pour vos réponses mais j'aurais souhaité un code en vba sachant que la suite peut être désordonnée. Numéros issus d'un logiciel comptable.

chris · 17 Février 2021

Bonjour à tous

Une solution PowerQuery, intégré à Excel, avec une liste dans le désordre, le choix des bornes et la liste des manquants

maguie · 17 Février 2021

Merci pour la réponse mais pour PowerQuery cela fonctionne comment? Quelle est la procédure pour retourner les manquants?

chris · 17 Février 2021

Re

Ouvre PowerQuery (Données, Obtenir des données, Lancer PowerQuery)

Tu verras les 2 requêtes :

Choix qui récupère simplement la table de choix des bornes
Factures qui récupères la table des factures et cherche les manquants : tu as les étapes à droite avec le détail dans la barre de formule et en cliquant sur le rouage

J'ai utilisé la technique des suites http://www.excel-formations.fr/Trucs_astuces/PQ05.php

Si l'une ou l'autre étape te pose question, reposte

maguie · 17 Février 2021

chris à dit:
Re

Ouvre PowerQuery (Données, Obtenir des données, Lancer PowerQuery)

Tu verras les 2 requêtes :

Choix qui récupère simplement la table de choix des bornes

Factures qui récupères la table des factures et cherche les manquants : tu as les étapes à droite avec le détail dans la barre de formule et en cliquant sur le rouage

J'ai utilisé la technique des suites http://www.excel-formations.fr/Trucs_astuces/PQ05.php

Super. Je vais essayer en soirée car là je pars travailler. Merci à vous tous et très bonne journée.

job75 · 17 Février 2021

Bonjour maguie, Phil69970, JHA, chris, le forum,

Voyez le fichier joint et cette formule matricielle en D3 :

Code:

=SIERREUR(PETITE.VALEUR(SI(NON(NB.SI(B:B;MIN(B:B)-1+LIGNE(INDIRECT("1:"&MAX(B:B)-MIN(B:B)+1))));MIN(B:B)-1+LIGNE(INDIRECT("1:"&MAX(B:B)-MIN(B:B)+1)));LIGNE(E1));"")

Elle fonctionne quelle que soit la manière dont le tableau est trié.

A+

Phil69970 · 17 Février 2021

Bonjour maguie, JHA, chris, job75, le forum

maguie à dit:
j'aurais souhaité un code en vba sachant que la suite peut être désordonnée.

Je te propose ce fichier :

maguie à dit:
Ma problématique est de trouver par exemple les numéros de facture manquants en précisant que le début est 20180001 et la fin 20180050

Tu indiques en E1 et E2 les numéros à rechercher...

@Phil69970

job75 · 17 Février 2021

Voyez le fichier joint et cette macro dans le code de Feuil1 (clic droit sur l'onglet et Visualiser le code) :

VB:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim P As Range, prem&, der&, mini&, maxi&, d As Object, resu(), tablo, i&, n&
Set P = [A1].CurrentRegion.Columns(2) 'à adapter
prem = Val([E2]): der = Val([E3]) 'à adapter
mini = Application.Min(P)
maxi = Application.Max(P)
prem = IIf(prem > mini, prem, mini)
der = IIf(der > maxi, maxi, der)
Set d = CreateObject("Scripting.Dictionary")
tablo = P.Resize(, 2) 'matrice, plus rapide, au moins 2 éléments
'---liste des numéros sans doublons---
For i = 2 To UBound(tablo)
    d(tablo(i, 1)) = ""
Next
'---liste des manquants---
ReDim resu(1 To UBound(tablo), 1 To 1)
For i = prem To der
    If Not d.exists(i) Then n = n + 1: resu(n, 1) = i
Next
'---restitution---
Application.EnableEvents = False 'désactive les évènements
If FilterMode Then ShowAllData 'si la feuille est filtrée
With [E6] '1ère cellule de destination, à adapter
    If n Then .Resize(n) = resu
    .Offset(n).Resize(Rows.Count - n - .Row + 1).ClearContents 'RAZ en dessous
End With
Application.EnableEvents = True 'réactive les évènements
End Sub

Elle se déclenche quand on modifie ou valide une cellule quelconque.

L'exécution est très rapide car on utilise des tableaux VBA et le Dictionary.

A+

maguie · 18 Février 2021

job75 à dit:

Voyez le fichier joint et cette macro dans le code de Feuil1 (clic droit sur l'onglet et Visualiser le code) :

VB:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim P As Range, prem&, der&, mini&, maxi&, d As Object, resu(), tablo, i&, n&
Set P = [A1].CurrentRegion.Columns(2) 'à adapter
prem = Val([E2]): der = Val([E3]) 'à adapter
mini = Application.Min(P)
maxi = Application.Max(P)
prem = IIf(prem > mini, prem, mini)
der = IIf(der > maxi, maxi, der)
Set d = CreateObject("Scripting.Dictionary")
tablo = P.Resize(, 2) 'matrice, plus rapide, au moins 2 éléments
'---liste des numéros sans doublons---
For i = 2 To UBound(tablo)
    d(tablo(i, 1)) = ""
Next
'---liste des manquants---
ReDim resu(1 To UBound(tablo), 1 To 1)
For i = prem To der
    If Not d.exists(i) Then n = n + 1: resu(n, 1) = i
Next
'---restitution---
Application.EnableEvents = False 'désactive les évènements
If FilterMode Then ShowAllData 'si la feuille est filtrée
With [E6] '1ère cellule de destination, à adapter
    If n Then .Resize(n) = resu
    .Offset(n).Resize(Rows.Count - n - .Row + 1).ClearContents 'RAZ en dessous
End With
Application.EnableEvents = True 'réactive les évènements
End Sub

Elle se déclenche quand on modifie ou valide une cellule quelconque.

L'exécution est très rapide car on utilise des tableaux VBA et le Dictionary.

A+

Bonjour.
Super!
Merci à vous tous pour votre contribution vous m'avez merveilleusement aidé.
ça fonctionne à merveille
Très bonne journée à vous tous. Et encore un grand merci pour votre aide.

maguie · 3 Mars 2021

chris à dit:
Bonjour à tous

Une solution PowerQuery, intégré à Excel, avec une liste dans le désordre, le choix des bornes et la liste des manquants

Bonjour
J'ai regardé ce qui se faisait sur power pivot et powerQuerry. Il y a t-il des cours en ligne ou des informations sur le sujet?
Merci beaucoup

patricktoulon · 3 Mars 2021

bonjour
et ben dis donc ça en fait une machine pour si peu

VB:

Sub test()
Dim tablo, tbl(), i&, a&, mini&, maxi&
tablo = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
mini = WorksheetFunction.Large(tablo, UBound(tablo))
maxi = WorksheetFunction.Large(tablo, 1)
For i = mini To maxi
X = Application.IfError(Application.Match(i, tablo, 0), 0)
If X = 0 Then a = a + 1: ReDim Preserve tbl(1 To a): tbl(a) = "Il manque la facture N° " & i
Next
[B2].Resize(UBound(tbl)) = Application.Transpose(tbl)
End Sub

c'est pas compliqué tu a un min et un max ben boucle du min au max et teste avec match et c'est tout

job75 · 3 Mars 2021

Bonjour le forum,

patricktoulon à dit:
et teste avec match et c'est tout

Tu sais certainement que ça prend du temps sur un grand tableau, le Dictionary l'évite.

A+

XL 2019 vba trouver un nombre manquant dans une suite donnee

maguie

XLDnaute Junior

Phil69970

XLDnaute Barbatruc

Pièces jointes

JHA

XLDnaute Barbatruc

Pièces jointes

maguie

XLDnaute Junior

chris

XLDnaute Barbatruc

Pièces jointes

maguie

XLDnaute Junior

chris

XLDnaute Barbatruc

maguie

XLDnaute Junior

job75

XLDnaute Barbatruc

Pièces jointes

Phil69970

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

maguie

XLDnaute Junior

maguie

XLDnaute Junior

patricktoulon

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Discussions similaires