Boucle For Next imbriquée

Résolu

robby98800 Messages postés 64 Date d'inscription mardi 29 mai 2012 Statut Membre Dernière intervention 27 juillet 2012 - 27 juin 2012 à 07:20
robby98800 Messages postés 64 Date d'inscription mardi 29 mai 2012 Statut Membre Dernière intervention 27 juillet 2012 - 22 juil. 2012 à 23:58

Bonjour à tous,

j'ai fait une macro qui compare d'abord une cellule d'une colonne à une autre et qui compare ensuite (si la 1ere condition est bonne) une autre cellule d'une autre colonne à 2 autres(la valeur dans la 1ere doit être encadré par les 2 valeurs dans les deux autres). Si toutes les conditions sont remplis on écrit dans une cellule, sinon on incrémentre la boucle.

C'est assez compliqué à s'en faire une image je vous met en pièce jointe un exemple : http://cjoint.com/?0FBhtQ6Ssmj

La macro ressemble à ca :

Sub position()
For i = 2 To Sheets(2).Range("C" & Rows.Count).End(xlUp).Row
    For j = 2 To Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
    
        If Sheets(2).Range("C" & i) = Sheets(3).Range("A" & j) Then
            If Sheets(2).Range("F" & i) >= Sheets(3).Range("B" & j) And Sheets(2).Range("F" & i) <= Sheets(3).Range("C" & j) Then
                Sheets(2).Range("G" & i) = "couche"
            Else: Sheets(2).Range("G" & i) = ""
            End If
    Next j
        
        End If
    Next j

Next i
        
    
End Sub

Le problème est que j'obtiens l'erreur : erreur de compilation Next sans For à la ligne du premier Next j.

Merci de votre aide !

A voir également:

Vba for next
Boucles vba - Meilleures réponses
Vba boucles - Meilleures réponses
Doevents vba - Conseils pratiques -Visual Basic / VB.NET
Byval vba - Conseils pratiques -Visual Basic / VB.NET
Aide vba - Conseils pratiques -Visual Basic / VB.NET
Vba dernière ligne ✓ - Forum Visual Basic 6
Set vba - Forum Visual Basic

86 réponses

Réponse 1 / 86

cs_cheyenne Messages postés 693 Date d'inscription samedi 18 mai 2002 Statut Membre Dernière intervention 17 avril 2017 2
27 juin 2012 à 19:24

Re,

Erreur de ma part :
le deuxième fin_sh2 est à remplacer évidement par fin_sh3.

Utiliser également With

Sub position()
   Dim i       As Long
   Dim j       As Long
   Dim fin_sh2 As Long
   Dim fin_sh3 As Long

   fin_sh2 = Sheets(2).Range("C" & Rows.Count).end(xlUp).Row
   fin_sh3 = Sheets(3).Range("A" & Rows.Count).end(xlUp).Row
   
   With Sheets(2)
      For i = 2 To fin_sh2
         For j = 2 To fin_sh3
            
            If .Cells(i, 3).Value = Sheets(3).Cells(j, 1).Value Then
               
               If .Cells(i, 6).Value >= Sheets(3).Cells(j, 2).Value Then
                  If .Cells(i, 6).Value <= Sheets(3).Cells(j, 3).Value Then
                     .Cells(i, 7).Value = "couche"
                  Else
                     .Cells(i, 7).Value = "debout"
                  End If
               End If
   
            End If

         Next j
      Next i
   End With
End Sub

Cheyenne

Réponse 2 / 86

ucfoutu Messages postés 18038 Date d'inscription lundi 7 décembre 2009 Statut Modérateur Dernière intervention 11 avril 2018 211
2 juil. 2012 à 07:00

Ecoute,
Je ne veux pas reprendre tout (analyse des tenants et aboutissants)

je vais me contenter de repartir du code que tu as montré le mercredi 27 juin 2012 à 09:08:18 en disant qu'il fonctionnait, mais auquel tu reprochais simplement d'être lent. C'est le mécanisme de ton propre code, que je vais appliquer, mais en utilisant deux tableaux dynamiques ===>>
Voilà ce qu'il deviendrait :

derlig2 = Sheets("Feuil2").Range("C" & Rows.Count).End(xlUp).Row
derlig3 = Sheets("Feuil3").Range("A" & Rows.Count).End(xlUp).Row
tablo2 = Sheets("Feuil2").Range("A2:G" & derlig2)
tablo3 = Sheets("Feuil3").Range("A2:C" & derlig2)
For i = 1 To UBound(tablo2)
  For j = 1 To UBound(tablo3)
    If tablo2(i, 3) = tablo3(j, 1) Then
      If tablo2(i, 6) >= tablo3(j, 2) Then
        If tablo2(i, 6) <= tablo3(j, 3) Then
          tablo2(i, 7) = "couche"
        Else
          tablo2(i, 7) = "debout"
        End If
      End If
    End If
  Next
Next
Sheets("Feuil2").Range("A2:G" & derlig2) = tablo2

________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

Réponse 3 / 86

ucfoutu Messages postés 18038 Date d'inscription lundi 7 décembre 2009 Statut Modérateur Dernière intervention 11 avril 2018 211
27 juin 2012 à 08:13

Bonjour,
Avant d'aller où que ce soit et de commencer à examiner plus en détail :
Tu as deux next j qui sautent aux yeux !!!!!

________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

Réponse 4 / 86

robby98800 Messages postés 64 Date d'inscription mardi 29 mai 2012 Statut Membre Dernière intervention 27 juillet 2012
27 juin 2012 à 08:37

Oui je pensais qu'on devait mettre un next à chaque sortie de If mais je viens de m'apercevoir qu'en enlevant le premier le raisonnement reste correct.
Il n'y a plus d'erreur généré cependant je n'ai pas l'impression que la macro fonctionne car aucun de mes cellules n'affiche "couche" alors que les conditions semblent être remplis.

Je vais faire des tests..

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question

Réponse 5 / 86

robby98800 Messages postés 64 Date d'inscription mardi 29 mai 2012 Statut Membre Dernière intervention 27 juillet 2012
27 juin 2012 à 09:08

Je viens de faire une modification

Sub position()
For i = 2 To Sheets(2).Range("C" & Rows.Count).End(xlUp).Row
    For j = 2 To Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
    
        If Sheets(2).Range("C" & i) = Sheets(3).Range("A" & j) Then
            If Sheets(2).Range("F" & i) >= Sheets(3).Range("B" & j) Then
                If Sheets(2).Range("F" & i) <= Sheets(3).Range("C" & j) Then
            Sheets(2).Range("G" & i) = "couche"
                
            Else: Sheets(2).Range("G" & i) = "debout"
                
                End If
            End If

        
        End If
    Next j

Next i
        
    
End Sub

Comme ça, ça fonctionne apparemment c'était le And qui posait un problème (le fait que le couche ne s'affiche pas). Par contre l'exécution est très longue.

Réponse 6 / 86

cs_cheyenne Messages postés 693 Date d'inscription samedi 18 mai 2002 Statut Membre Dernière intervention 17 avril 2017 2
27 juin 2012 à 13:52

Bonjour,

Optimisation :
Remplacer la borne supérieure des boucles par des variables.

fin_sh2 = Sheets(2).Range("C" & Rows.Count).End(xlUp).Row
fin_sh2 = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To fin_sh2
   For j = 2 To fin_sh3

Ce qui évite un calcul à chaque passage.

Puis indiquer ce que tu dois extraire de chaque Range, en l'occurrence Value, cela évitera à Excel d'interpréter ce que tu veux.

If Sheets(2).Range("C" & i).Value = Sheets(3).Range("A" & j).Value Then

Si tu as beaucoup de lignes à explorer utiliser plutôt Cells que Range afin d'éviter les concaténations.

If Sheets(2).Cells(i, 3).Value = Sheets(3).Cells(j ,1).Value Then

Cheyenne

Réponse 7 / 86

cs_cheyenne Messages postés 693 Date d'inscription samedi 18 mai 2002 Statut Membre Dernière intervention 17 avril 2017 2
27 juin 2012 à 13:57

Re,

J'ai oublié de te recommander de mettre le
ScreenUpdating à False en début de procédure et de le rétablir avant de quitter celle-ci.

Cheyenne

Réponse 8 / 86

robby98800 Messages postés 64 Date d'inscription mardi 29 mai 2012 Statut Membre Dernière intervention 27 juillet 2012
28 juin 2012 à 09:02

Merci beaucoup Cheyenne pour ta réponse et tes explications ça marche beaucoup mieux et ça m'a permis d'y voir plus clair en vba !

Juste une question si dans une condition If on ne met pas de Else (car on ne veux rien faire sinon) le code fonctionne t-il toujours ?

Merci,

Robin

Réponse 9 / 86

ucfoutu Messages postés 18038 Date d'inscription lundi 7 décembre 2009 Statut Modérateur Dernière intervention 11 avril 2018 211
28 juin 2012 à 10:16

Ta question trouve sa réponse dans ton aide VBA !
Et tu en as de surcroît un exemple dans le code de cheyenne, non ? (le premier bloc If ne comprend aucune clause Else.

________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

Réponse 10 / 86

cs_cheyenne Messages postés 693 Date d'inscription samedi 18 mai 2002 Statut Membre Dernière intervention 17 avril 2017 2
28 juin 2012 à 12:41

Bonjour robby98800,

La clause Else n'est en aucun cas indispensable.
(voir la réponse de ucfoutu)

Dans l'exemple de code j'ai oublié ce que je t'avais préconisé, HI

Sub position()
   Dim i       As Long
   Dim j       As Long
   Dim fin_sh2 As Long
   Dim fin_sh3 As Long

   fin_sh2 = Sheets(2).Range("C" & Rows.Count).end(xlUp).Row
   fin_sh3 = Sheets(3).Range("A" & Rows.Count).end(xlUp).Row

   Application.ScreenUpdating = False  ' <<<<<

   '  le code...

   Application.ScreenUpdating = True  ' <<<<<
End Sub

Tu devrais avoir ainsi une nette amélioration du temps d'exécution, surtout si tu as beaucoup de lignes à traiter.

Cheyenne

Réponse 11 / 86

ucfoutu Messages postés 18038 Date d'inscription lundi 7 décembre 2009 Statut Modérateur Dernière intervention 11 avril 2018 211
28 juin 2012 à 13:32

Pas de secrets :
La seule manière vraiment efficace d'accélerer considérablement les choses est à mon sens :
- de créer un tableau dynamique
- de traiter les éléments de ce tableau
- de réinjecter le tableau, d'un seul coup.

Gain considérable (traitement des éléments d'un tableau bien plus rapide que celui des éléments d'une plage, puis réinsertion d'un seul coup, encore plus rapide).
Tout le reste ne sera à mes yeux que "diminuer la douleur", sans s'attaquer à "la lourdeur".

________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

Réponse 12 / 86

cs_cheyenne Messages postés 693 Date d'inscription samedi 18 mai 2002 Statut Membre Dernière intervention 17 avril 2017 2
28 juin 2012 à 14:00

Bonjour ucfoutu,

Certes oui !

1) Mais il faut bien auparavant renseigner ce tableau à partir des plages de données...

2) Le traiter en mémoire est effectivement beaucoup plus rapide !

3) Comment le réinjecter d'un seul coup sans passer par une boucle pour inscrire les données traitées ?

Un tout petit exemple serait le bienvenu. Merci.

Bonne journée à toi,

Cheyenne

Réponse 13 / 86

ucfoutu Messages postés 18038 Date d'inscription lundi 7 décembre 2009 Statut Modérateur Dernière intervention 11 avril 2018 211
28 juin 2012 à 15:03

Bonjour, cheyenne,
La réinjection d'un tableau se fait de la manière la plus simple du monde.
Regarde donc :

 Dim tablo(1 To 10, 1 To 3)
' 1) _______ juste pour créer un tableau exemple _____________
For i = 1 To 10
  For j = 1 To 3
   tablo(i, j) = "a" & i & j
  Next
 Next
' 2) _______________ on l'injecte ainsi 
 Range("A1:C10") = tablo

aucun problème !

________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

Réponse 14 / 86

cs_cheyenne Messages postés 693 Date d'inscription samedi 18 mai 2002 Statut Membre Dernière intervention 17 avril 2017 2
28 juin 2012 à 15:20

Re,

Range("A1:C10") = tablo

Ben oui, évidemment, arggghhhh !!!!
que n'y ai je pas pensé plus tôt ?

Il n'empêche qu'au départ il faut bien renseigner le tableau en parcourant les cellules de la plage.
Mais ce "temps perdu" sera largement compensé par le traitement en mémoire et au final par l'injection en bloc.

Merci pour l'exemple.

Cheyenne

Réponse 15 / 86

ucfoutu Messages postés 18038 Date d'inscription lundi 7 décembre 2009 Statut Modérateur Dernière intervention 11 avril 2018 211
28 juin 2012 à 17:07

Il n'empêche qu'au départ il faut bien renseigner le tableau en parcourant les cellules de la plage.

Ben non !
regarde (en imaginant que tu aies des données dans la plage A1:D5

tablo = Range("A1:D5") ' le voilà, ton tableau
' on va le traiter (faire ici semblant en ajoutant "vu ?" à chaque élément
For i = 1 To UBound(tablo, 1)
   For j = 1 To UBound(tablo, 2)
     tablo(i, j) = tablo(i, j) & "vu ?"
   Next
Next
Range("A1:D5").Value = tablo

Je n'ai ici à aucun moment blouclé sur la plage (lent), mais sur le tableau (10 fois plus rapide).
________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

Réponse 16 / 86

cs_cheyenne Messages postés 693 Date d'inscription samedi 18 mai 2002 Statut Membre Dernière intervention 17 avril 2017 2
28 juin 2012 à 17:31

Re,

Ah ben oui, SUPER !
Je ne savais, je le reconnais, que l'on pouvait remplir un tableau de cette manière.

J'ai déclaré tablo en tant que Variant et effectué divers essais avec des cellules "numériques" et "textes". Impeccable.

Je vais de ce pas revoir les codes de certaines de mes applications.

Un grand merci pour l'astuce que je valide virtuellement

Cheyenne

Réponse 17 / 86

robby98800 Messages postés 64 Date d'inscription mardi 29 mai 2012 Statut Membre Dernière intervention 27 juillet 2012
28 juin 2012 à 23:08

Merci aussi ucfoutu, je vais essayer d'adapter ta méthode.

Réponse 18 / 86

robby98800 Messages postés 64 Date d'inscription mardi 29 mai 2012 Statut Membre Dernière intervention 27 juillet 2012
29 juin 2012 à 06:47

Re,

Voici ce que j'ai fait. Je suis passé par deux tableaux, un dynamique et un fixe.

Sub tabex()
derniere_ligne = Sheets(2).Range("C1").End(xlDown).Row
Dim tab_exa()
ReDim tab_exa(derniere_ligne - 2, 2)

Dim tab_exb(215, 2)
    
    For i = 2 To derniere_ligne - 2
        tab_exa(i, 0) = Sheets(2).Range("C" & i + 2)
        tab_exa(i, 1) = Sheets(2).Range("F" & i + 2)
        tab_exa(i, 2) = Sheets(2).Range("G" & i + 2)

    Next i
    
    For j = 2 To UBound(tab_exb, 1)
            tab_exb(j, 0) = Sheets(3).Range("A" & j)
            tab_exb(j, 1) = Sheets(3).Range("B" & j)
            tab_exb(j, 2) = Sheets(3).Range("C" & j)
    
    Next j
    
    For i = 2 To UBound(tab_exa, 1)
        For j = 2 To UBound(tab_exb, 1)
            If tab_exa(i, 0) = tab_exb(j, 0) Then
                If tab_exa(i, 1) >= tab_exb(j, 1) And tab_exa(i, 1) <= tab_exb(j, 2) Then
                    tab_exa(i, 2).Value = "couche"
               
                End If
            End If
    
        Next j
    Next i

Range("G2:G" & [G65536].End(xlUp).Row).Value = tab_exa(i, 2)

End Sub

J'imagine bien que la dernière ligne est fausse mais je ne sais pas comment dire la 3eme colonne du tab_exa est égale à la plage de cellule de la colonne G. En effet la colonne G a le meme nombre de ligne que la colonne A mais le nombre est variable.
De plus je sais très bien que la dernière ligne ne peut pas fonctionner car j'écrit affecte le tab_exa(i, 2) aux cellules de G2 jusqu'à la dernière cellule de G non vide mais elles ne sont pas remplies avant l'affectation donc ça ne peut pas marcher.

C'est d'ailleurs pour cela que j'obtiens l'erreur 9 l'indice n'appartient pas à la sélection à la derniere ligne.

Je trouve cette méthode très puissante et j'aimerais vraiment avoir plus d'infos pour pouvoir bien la maitriser.

Merci,

Robin

Réponse 19 / 86

ucfoutu Messages postés 18038 Date d'inscription lundi 7 décembre 2009 Statut Modérateur Dernière intervention 11 avril 2018 211
29 juin 2012 à 07:21

Bonjour, robby98800,

Lorsque tu fais une boucle For to next (ex : for x = 1 to 2), la variable d'avancement (ici x, donc) avance d'une unité à chaque tour de boucle. A la fin de la boucle, elle dépasse donc sa limité d'une unité.
Fais l'essai :

For x = 1 to 2
 ' on s'en moque
next
msgbox x ' ===>> x vaut alors 2 + 1 = 3 et non 2

et (dans ton exemple, donc) i a dépassé d'une unité la borne supérieure de ton tableau tab_exa ===>> et tu es hors limite.
Utilise donc i-1 et non i dans ta dernière ligne

________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

Réponse 20 / 86

ucfoutu Messages postés 18038 Date d'inscription lundi 7 décembre 2009 Statut Modérateur Dernière intervention 11 avril 2018 211
29 juin 2012 à 07:25

Ceci étant dit, tu as compris une partie seulement de l'utilisation des tableaux, dont je vois que tu les dimensionnes, puis les alimentes en boucle, au lieu de les générer directement (sans aucune boucle ni besoin de les dimensionner)
Relis donc plus attentivement ce que j'en ai dit plus haut

________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

Discussions similaires

boucle for avec un pas de 2

foreach en SQL ?

Comment fonctionne la fonction FormulaR1C1

fonction modulo en VBA

sortir d'une boucle for each

Votre réponse

Discussions similaires