Vérification d'une série continue de cellules

[fouggy]

Slt à tous,

Qui cherche trouve dit-on... Alors on va dire que par rapport à ma problématique c'est à moitié vrai et à moitié faux, lol.

Ma problématique était la suivante :

Je dispose d'un nombre incalculable d'onglets (proche de 200 000 !) dispatchés dans plusieurs classeurs. Ces onglets ont été nommés automatiquement "ongletxxxxx" et se suivent tous.

Or je me suis aperçu que certains onglets manquaient d'où la nécessité d'un contrôle des onglets manquants dans une série donnée contenu dans chaque classeur. Après une longue recherche sur le net j'ai enfin trouvé une macro qui permettait de lister l'ensemble des onglets d'un classeur. C'est la partie vraie de ma recherche, lol.

Je me suis dit ensuite qu'il suffisait de trouver une macro qui me signale les numéros de série manquants dans une liste continue et contenue maintenant dans la colonne A, et là... rien de chez rien... C'est la partie fausse de ma recherche, lol.

Du coup, le truc basique qui servirait aussi probablement à d'autres personnes serait une macro qui analyse la série contenue dans colonne A et signale l'élément manquant de la série, en la créant, par exemple, dans la colonne B.

Pour exemple les caractères contenus, par cellule, la série de la colonne A :
onglet53720
onglet53721
onglet53723
onglet53724
onglet53729...

Et résultat attendu dans colonne B (le ou les éléments manquant d'une série logique) :
onglet53722
onglet53725
onglet53726
onglet53727
onglet53728...

L'option directe consisterait bien entendu à dire sous forme de macro :
Contrôle la série d'onglets de 53720 à 85210 du classeur ouvert et liste-moi les onglets manquants mais cela n'y est pas sur le net, dans aucune réponse de forum, à moins que cela m'ai échappé, mais j'en doute et serait grandement étonné qu'on puisse me répondre par un lien existant déjà.

Voila donc...

Merci d'avance pour vos réponses.

 

[JCGL]

Re : Vérification d'une série continue de cellules

Bonjour à tous,

Peux-tu essayer avec ceci :

Sub Test()
    Dim L%, j&, k&, DerL%
     DerL = Range("A65536").End(xlUp).Row
     Columns(2).ClearContents
    j = 1
    For L = 2 To DerL
        For k = Right(Cells(L - 1, 1), 5) + 1 To Right(Cells(L, 1), 5) - 1
            Cells(j, 2) = k
            j = j + 1
        Next
    Next
End Sub

A+ à tous

 

[Robert]

Re : Vérification d'une série continue de cellules

Bonsoir Fouggy, JCGL, bonsoir le forum,

Grillé par JCGL ! J'envoie quand même... Ce code te renverra dans la colonne A du premier onglet du classeur, la liste des onglets manquants :

Sub Macro1()
Dim NO As Long 'déclare la variabole NO (Numéro de l'Onglet)
Dim I As Integer 'déclare la variable I (Incrément)
Dim DEST As Range 'déclare la variable DEST

NO = CLng(Mid(Sheets(1).Name, 6)) 'définit le numéro d'onglet NO du premier onglet du classeur
For I = 1 To Sheets.Count 'boucle sur tous les onglets du classeur
    If CLng(Mid(Sheets(I).Name, 6)) = NO Then 'condition : si le numéro de l'onglet correSspont à la variable NO
        NO = NO + 1 'incrément NO
    Else 'sinon
        'définit la cellule de destination DEST (A1, si A1 est vide, sinon la première cellule vide de la colonne 1 (=A) du premier onglet)
        Set DEST = IIf(Sheets(1).Range("A1").Value = "", Sheets(1).Range("A1"), Sheets(1).Cells(Application.Rows.Count, 1).End(xlUp).Offset(1, 0))
        DEST.Value = NO 'renvoie le numéro manquant dans DEST
        NO = NO + 1 'incrément NO
        I = I - 1 'décrément I
    End If 'fin de la condition
Next I 'prochain onglet de la boucle
End Sub

 

[fhoest]

Re : Vérification d'une série continue de cellules

Bonjour à tous,(moi aussi grillé lol!!)
étant donné que tu as la première partie je t'ai fais la deuxième.
Voici un fichier exemple:
A+

 

[Robert]

Re : Vérification d'une série continue de cellules

Bonsoir le fil, bonsoir le forum,

Fhoest, impossible de télécharger ton fichier !

[Édition]
Arf ! le temps d'écrire mon message et ça marche... Désolé donc. Ça marche

 

[JCGL]

Re : Vérification d'une série continue de cellules

Bonjour à tous,
Salut Robert,

Si toutes les cellules de la liste des onglets en A commence par Onglet et quelque soit le nombre qui suit :

Sub Test()
    Dim L%, j&, k&, DerL%
     DerL = Range("A65536").End(xlUp).Row
     Columns(2).ClearContents
    j = 1
    For L = 2 To DerL
        For k = WorksheetFunction.Substitute(Cells(L - 1, 1), "onglet", "") + 1 To WorksheetFunction.Substitute(Cells(L, 1), "onglet", "") - 1
            Cells(j, 2) = "onglet" & k
            j = j + 1
        Next
    Next
End Sub

A+ à tous

 

[fhoest]

Re : Vérification d'une série continue de cellules

Bonsoir Robert,JCGL
c'est parce que je m'étais trompé de fichier entre temps.
j'ai de gros problème pour écrire sur XLD mon curseur scintille tous le temps et je dois laisser entre chaque lettres une demi seconde parfois même je dois retaper plusieurs fois sur mes touches, je doute que cela vienne de mon pc car j'ai le problème uniquement sur XLD.(suis je le seul ???)
A+

 

[Robert]

Re : Vérification d'une série continue de cellules

Bonsoir le fil, bonsoir le forum,

j'ai le problème uniquement sur XLD.(suis je le seul ???)

@Fhoest, Pour ma part, je ne rencontre pas ce genre de problème...

 

[fhoest]

Re : Vérification d'une série continue de cellules

Re,
je viens de poster ici:
https://www.excel-downloads.com/threads/probleme-avec-ie11-sous-windows-8-1.220029/
A bientôt.

 

[fouggy]

Re : Vérification d'une série continue de cellules

Slt le Forum et un grand merci à JCGL, Robert et Fhoest.

J'ai pris le temps de tester vos 3 macros, contenues dans le fichier joint contenant une des séries que j'ai à traiter.

Celle de JCGL est au Top car répondant très exactement à ma demande.
Celle de Fhoest est pas mal sauf qu'elle saute un élément de la série continue et en l'occurrence le 1er (Voir fichier). Par ailleurs la liste en colonne B des éléments manquants renvoie uniquement la partie chiffrée du nom de l'onglet manquant ce qui ne gêne en rien et peut même constituer une différence intéressante à exploiter.
Concernant celle de Robert, impossible de me prononcer car le message de débogage me renvoi à la ligne suivante :
NO = CLng(Mid(Sheets(1).Name, 6)) 'définit le numéro d'onglet NO du premier onglet du classeur

Bien que JCGL ait répondu exactement à ma demande, il serait intéressant que Robert et Fhoest puissent apporter un rectificatif à leur macro afin d'avoir 3 approches différentes pour un même résultat.
Cela fait bientôt 1 mois et demi que je bosse le VBA à partir des cours gratuits proposés sur le net et toutes les approches de macros utiles à mon projet m'intéressent même si je ne comprends pas encore tout mais cela va viendre, lol, entre ce que j'acquiers régulièrement et les macros récupérées sur les forums et que je suis en mesure, pour certaines, de modifier pour les adapter à ma demande. Il m'arrive d'adapter 10 macros à la suite pour obtenir le résultat qu'une seule aurait pu fournir mais bon, Rome ne s'est pas fait en un jour, lol. Suis pas encore en mesure de jouer aux poupées russes avec plusieurs commandes à l'intérieur d'une macro. Voilà donc les raisons pour lesquelles j'aimerais connaître les macros rectifiées de Fhoest et Robert.

Par ailleurs et ayant l'impression d'avoir à faire à 3 férus du VBA, j'en profite pour vous demander si vous connaissez quelqu'un qui "vend" de la macro VBA. J'ai en effet 2 méga-macros à sortir dont la complexité de réalisation dépasse très très largement mon niveau actuel, (même en les saucissonnant) et rien sur l'ensemble des forums qui permette de s'approcher de près ou de loin au résultat attendu, d'où la présente demande.
Je me suis bien entendu et naturellement rapproché de "Pros" pour financer l'élaboration de ces 2 macros mais ceux-ci, ceux qui sont capables de sortir tout ce qu'on leur demande, ne développent pas en VBA, privilégiant d'autres langages. Or je tiens à ce que tout soit fait en VBA car indépendamment du résultat je tiens à découvrir les codes utilisés afin de continuer à progresser dans le langage VBA auquel je me suis attelé et accéder, à terme, à l'autonomie. Les performances macros en termes de temps d'exécution ne sont pas une priorité actuelle.

Voilà, vous savez tout ou déjà beaucoup, lol. Merci donc de bien vouloir m'orienter si vous connaissez.

Dans l'attente impatiente de vos réponses je vous renouvelle mes plus grands remerciements pour ce qui a déjà été fait.

Cordialement.

 

[job75]

Re : Vérification d'une série continue de cellules

Bonjour à tous,

Pas tout suivi mais voyez cette solution par formules dans le fichier joint.

Elle utilise la fonction macro Excel 4.0 LIRE.CLASSEUR.

La fonction ALEA() rend la formule du nom "Liste" volatile pour son recalcul.

A+

 

[job75]

Re : Vérification d'une série continue de cellules

Re,

S'il y a plusieurs fichiers à traiter simultanément ce n'est guère plus difficile.

Ouvrez tous les fichiers joints et voyez les formules dans le fichier (2).xlsm.

A+

 

[fouggy]

Re : Vérification d'une série continue de cellules

Slt Job75, heureux de te voir sur ce fil.

J'ai ouvert tes fichiers et malheureusement je n'y comprends rien...

Il y a 2 mois le VBA était du chinois. Et depuis j'apprends le chinois...

Si maintenant tu commences à me parler le Coréen, ça va pas le faire, mdrrrrrrr.

Alors pour moi, le VBA ça se limite à Sub/End Sub dans un module, et le chantier est conséquent. Quant aux formules qui commencent par le signe "=" et qu'il s'agit de maîtriser aussi, ça va pas le faire, pour le moment, lol.

Merci en tous les cas pour ton intervention qui ne manquera pas d'intéresser ceux qui maîtrisent les formules.

Au fait puisque tu es là, j'ai pas osé te dire que je m'étais planté dans ma dernière demande concernant la gestion des dizaines, si tu t'en souviens ?
https://www.excel-downloads.com/threads/copies-et-suppressions-sous-conditions.222764/

En notant "N" en D2 lorsqu'il y avait plus d'un nombre compris entre 10 et 19.9. Cette option n'était pas ma recherche laquelle était bien plus compliqué car il fallait retenir le nombre 10 même s'il y en avait plusieurs à la double condition qu'il soit le plus petit 10 existant et qu'il n'y en ait pas un autre le rendant ex eaquo. Pas simple, lol.
Et bien j'ai saucissonné ta macro pour modifier seulement les "Etapes 1 et 2".
En clair j'ai gardé le reste de ta macro et il m'a fallu 8 macros différentes mais hyper simples à créer.
J'utilise donc 10 macros pour atteindre mon objectif : Le début et la fin de ta macro et les 8 miennes que j'utilise les unes après les autres, n'étant pas encore en mesure de jouer aux poupées russes avec l'ensembles des codes utilisés.

C'est lourdingue, je sais, mais c'est quand-même pas mal pour un débutant qui ne recherche pas encore la vitesse d'exécution mais d'aboutir en essayant de comprendre et de devenir autonome, lol.

Voilà, après si cela t'intéresse, par unique curiosité, je peux te faire passer ce que j'ai fais pour aboutir.
Il serait d'ailleurs intéressant de voir comment tu emboîterais tout cela avec mes macros simples à moi et voir, mais alors uniquement après que tu ais construis ta propre macro avec mes données modifiées, la différence entre les 2 macros définitives afin que je puisse comparer les 2 cheminements.

Qu'en penses-tu ?

En tous les cas c'est des choses que j'aurais été incapable de faire il y a encore 15 jours.

Un grand merci à toi encore et à bientôt.

 

[fhoest]

Re : Vérification d'une série continue de cellules

Bonjour à tous

Celle de Fhoest est pas mal sauf qu'elle saute un élément de la série continue et en l'occurrence le 1er

la solution que j'ai donné était dans un fichier qui contient des titres de champs,
c'est pourquoi dans la macro de mon fichier exemple je commence à la ligne 2 de la colonne A.
voici la rectification en rapport avec le fichier Retour Macro.

Sub Fhoest()
Dim a As Range
Dim conversion As Double
For Each a In Range("A1:A" & [A:A].Find("*", , , , 1, 2).Row)
On Error Resume Next
conversion = CDbl(Right(a.Value, InStr(1, a.Value, "t", 1) - 1))
conversion_bis = CDbl(Right(a.Offset(1, 0).Value, InStr(1, a.Value, "t", 1) - 1))
If CDbl(conversion_bis) - CDbl(conversion) <> 1 Then
For I = 1 To CDbl(conversion_bis) - CDbl(conversion) - 1
L = Range("B999999").End(xlUp).Row + 1
Range("B" & L).Value = "onglet" & CDbl(conversion) + I
Next
End If
On Error GoTo 0
Next
End Sub

A bientôt.