Problème avec une boucle de type Do While =>Loop

**Runsh63** · 22/09/2011, 12h28

Bonjour,

Afin de compiler des données mensuelles dans un onglet global, j'ai monté ce code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Application.ScreenUpdating = False
Dim SN As Byte, SNN As String, FR As Long, LR As Long
 
On Error Resume Next
 
    For SN = 2 To 13
    SNN = Sheets(SN).Name
        With Sheets(SN)
            .Range("A2:" & .Range("A2").SpecialCells(xlCellTypeLastCell).Address).Copy Sheets(1).Range("B65536").End(xlUp).Offset(1, 0)
        End With
        With Sheets(1)
            FR = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            LR = .Cells(.Rows.Count, 2).End(xlUp).Row
            .Range("A" & FR & ":A" & LR).Value = SNN
        End With
    Next
End Sub

Problème, ma dernière instruction avant le Next consiste à récupérer le nom de l'onglet (qui n'est autre que le mois...) pour le coller en colonne A de mon onglet global. Ca me créé un décalage car malgré l'absence d'onglet, la boucle continue pour m'insérer le nom du dernier onglet jusqu'à 13...

J'ai pensé à faire ceci comme ci-dessous pour parer à ce souci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Application.ScreenUpdating = False
Dim SN As Byte, SNN As String, FR As Long, LR As Long
SN = 2
 
    Do While SN <> 0
    SNN = Sheets(SN).Name
        With Sheets(SN)
            .Range("A2:" & .Range("A2").SpecialCells(xlCellTypeLastCell).Address).Copy Sheets(1).Range("B65536").End(xlUp).Offset(1, 0)
        End With
        With Sheets(1)
            FR = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            LR = .Cells(.Rows.Count, 2).End(xlUp).Row
            .Range("A" & FR & ":A" & LR).Value = SNN
        End With
        SN = SN + 1
    Loop
 
End Sub

Bien entendu ça ne marche pas (je ne maîtrise pas encore ce type de boucle)
Ce que je veux dire ici :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Do While SN <> 0

C'est que si il n'y a plus d'onglet, alors on sort de la boucle.
Pourriez-vous m'aider SVP ?

Merci d'avance !

**tototiti2008** · 22/09/2011, 12h39

Bonjour RunSh,

peut-être simplement remplacer

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

For SN = 2 To 13

par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

For SN = 2 To sheets.count

Et je te conseille d'enlever le On error resume next...

**Sclarckone** · 22/09/2011, 13h43

Envoyé par tototiti2008

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

For SN = 2 To sheets.count

Ce n'est qu'un détail mais mieux vaut utiliser:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

WorkSheets

que

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Sheets

Car la collection Sheets contiens tous les onglets du classeur alors que la collection WorkSheets ne contient que les feuilles de calcul.
Tant qu'il n'y a que des feuilles de calcul, ça va mais s'il y a des feuilles graphiques ou autres...

**Runsh63** · 22/09/2011, 13h52

Bonjour tototiti2008, bonjour Sclarckone,

Merci pour vos réponses à tous les deux. Elles vont résoudre mon problème.
Pour ma culture perso, je te remercie Sclarckone pour tes explications sur la différence entre "sheets" et "worksheets", encore quelque chose que je ne connaissais pas et qui va m'être bien utile (même si dans la pratique sur ce fichier là, je n'ai aucun problème car il n'y a que des feuilles de calculs).
Par contre, tototiti2008, peux-tu m'expliquer m'expliquer pourquoi le

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

On Error Resume Next

est à enlever ? Dans ma tête, je pensais que ceci allait justement me permettre de sortir de la boucle dès qu'il ne trouverai plus d'onglet (puisque moins de 13 si tel est le cas).

En tout cas encore merci !!

**Sclarckone** · 22/09/2011, 14h04

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

On Error Resume Next

est "dangereux" parce qu'il ne fait pas de distinction sur le type d'exception qui est levé. Du coup si une exception est levée dans une autre partie du code que celle où tu t'y attends, l'exécution va quand même continuer et tu peux obtenir un comportement imprévu et inexplicable (au moins à première vue).
C'est donc à utiliser avec beaucoup de précautions!

Dans ton cas, c'est même inutile car utiliser:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

WorkSheets.Count

te permet de sortir de la boucle lorsqu'il n'y a plus de feuilles de calcul et sans lever d'exception.

**CodeFacile** · 22/09/2011, 14h07

Bonjour,

j'utilise plutôt des structures de type

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
do
instruction
loop while condition

dans ton cas , la condition

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
Do While SN <> 0

n'est jamais vérifié car SN est initialisé à 2 et tu ne fais que l'incrémenter.

Ta façon de faire pêche à cause des contraintes qu'elle impose : il faut que le fichier de consolidation soit ouvert en premier ou en dernier pour choisir les bornes 1 à 12 ou 2 à 13.
Je te propose une autre structure pour avoir la liste ordonnée
ex :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
 
liste_mois = array ( "janvier" , "fevrier" ,"mars" , "avril" ..'tu mets le reste des mois.... )   
 
for each  mois_ in liste_mois
  for each Feuil in worksheets
if Feuil.name = mois then 
' traitement
end if 
next 
next 
 
 
 
next

**Runsh63** · 22/09/2011, 14h23

J'adore ce forum, on apprend à faire des choses de x manières différentes.
Merci pour l'eau que tu as apporté à mon moulin CodeFacile, je vais le tester et le mettre de côté, il me sera forcément utile car toutes mes macros font + ou - la même chose ^^

**tototiti2008** · 22/09/2011, 14h24

Bonjour Sclarckone, bonjour CodeFacile,
Re,

Dans ton cas, c'est même inutile car utiliser:

Code :
WorkSheets.Count
te permet de sortir de la boucle lorsqu'il n'y a plus de feuilles de calcul et sans lever d'exception.

Parfaitement juste, mais dans ce cas il faudra également changer tous les "Sheets" du code en "Worksheets", sinon on peut avoir des surprises s'il y a une feuille graphique... pas seulement dans la boucle du For

**Godzestla** · 22/09/2011, 14h25

Bonjour,

petite remarque complémentaire sur la gestion en boucle des sheets (ou worksheets) via le code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

For SN = 2 To 13

C'est extrêmement dangereux car en finalité, tu ne connais pas vraiment le numéro de ta feuille, et ce n'est absolument pas parce que tu as 13 feuilles qu'elles portent le numéro de 2 à 13, et donc Sheet2 n'est pas nécessairement la deuxième feuille.

Si par exemple tu as 3 feuilles et rajoute 1 feuille, tu peux courament avoir ta liste Microsoft Excel object dans vba qui est
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet4)
Sheet4(Sheet3)

J'en ai fait les frais et tu peux chercher trèèèèèèèèèèès longtemps avant de trouver.

Mais, c'était juste une remarque.

**Runsh63** · 22/09/2011, 14h30

C'est vrai tototiti2008, il faut être cohérent et bien penser à utiliser Worksheets ou Sheets partout, sinon ça peut vite faire n'importe quoi...

Ta remarque est parfaitement vrai Godzestla, mais dans mon cas je n'ai aucun risque car ma première feuille est celle des données annuelles, et les 12 suivantes les mois de janvier à décembre. A moins que les technocrates décident de changer notre système calendaire, je n'ai pas de risque ^^

**tototiti2008** · 22/09/2011, 14h49

Bonjour Godzestla,
Re,

@Godzestla : il faut bien faire la distinction entre le CodeName d'une feuille (nom dans Visual Basic Editor), le Name (nom de l'onglet) et le numéro.
Ces 3 notions sont parfaitement indépendantes
Comme le code de RunSh se base sur le numéro

For SN = 2 To 13
SNN = Sheets(SN).Name

Ce qu'il faut lui préciser c'est que la feuille des données annuelles (non parcourue par sa boucle) doit absolument rester en première position, si on déplace les onglets de feuilles le code ne fonctionne plus...

**Godzestla** · 22/09/2011, 15h36

@tototiti2008:

Bien d'accord avec toi.

Mais personnellement je bouclerais sur toutes les feuilles en omettant donc celle dont le nom (.name) est celui de l'actuelle feuille 1.

Ainsi, le blindage est....meilleur.

Et puis SN et SNN, c'est pour moi très peu parlant en cas de maintenance peu fréquente et aussi sujet à gros soucis en cas d'erreur de frappe (1 N de plus et on parle d'autrechose). SN_Name serait bien meilleur que SNN.

Mais bon, c'est en forgeant qu'on ....

**Runsh63** · 22/09/2011, 16h34

Je vais suivre vos conseils, il est vrai que c'est peu fiable, et que dans ma tête c'est facile car SN = Sheet Number et SNN = Sheet Number Name...
En tout cas merci pour toutes vos petites combines qui me seront très utiles.
Bon allez, je vous laisse, je vais continuer à forger