VBA - Parcourir plusieurs plages d'une boucle

Bonjour à tous,

Je possède un petit code VBA qui me permet d'inscrire le résultat d'une formule dans certaines cellules d'une colonne spécifiée: la J.

Mon code est le suivant:

Sub Calcul_Somme()
Dim k, i As Long
Dim Feuille As Worksheet
Dim Plage2, Cel1 As Range
Dim debut, fin, temps As Date

debut = Time

Set Feuille = ThisWorkbook.Sheets("PL")
k = Feuille.Cells(Rows.Count, 2).End(xlUp).Row
Set Plage2 = Feuille.Range("J6:J" & k)

With Application
    For Each Cel1 In Plage2
        i = Cel1.Row
            If Cel1.Interior.Color = RGB(160, 203, 183) = True And Feuille.Range("A" & i) <> "Add ICP" Then
            Cel1 = .SumIfs(Range("J6:J" & i - 1), Range("Z6:Z" & i - 1), Range("B" & i), Range("D6:D" & i - 1), Range("D" & i), Range("F6:F" & i - 1), "*ICP*") +  .SumIfs(Range("J" & i + 1 & ":J" & k), Range("Z" & i + 1 & ":Z" & k), Range("B" & i), Range("D" & i + 1 & ":D" & k), Range("D" & i), Range("F" & i + 1 & ":F" & k), "*ICP*")
            End If
    Next Cel1
End With

fin = Time
temps = fin - debut
MsgBox temps
End Sub

J'aimerais un peu modifier ce code, et ajouter quelque chose du genre "For each plage2 in ????" afin que ce que je fasse aujourd'hui sur la colonne J, se fasse aussi sur les colonnes K, L, ..., jusqu'à la colonne V.

Quelqu'un pourrait-il m'aider ?

Merci,

SkillzZ

Bonsoir,

un essai, sans avoir tout compris

Sub Calcul_Somme()
Dim k, i As Long, Boucle As Integer
Dim Feuille As Worksheet
Dim Plage2, Cel1 As Range

Set Feuille = ThisWorkbook.Sheets("PL")
k = Feuille.Cells(Rows.Count, 2).End(xlUp).Row

For Boucle = 10 To 22
    Set Plage2 = Feuille.Range(Cells(6, Boucle), Cells(k, Boucle))

    With Application
        For Each Cel1 In Plage2
            i = Cel1.Row
            If Cel1.Interior.Color = RGB(160, 203, 183) = True And Feuille.Range("A" & i) <> "Add ICP" Then
                Cel1 = .SumIfs(Range(Cells(6, Boucle), Cells(i - 1, Boucle)), Range("Z6:Z" & i - 1), Range("B" & i), Range("D6:D" & i - 1), Range("D" & i), Range("F6:F" & i - 1), "*ICP*") + .SumIfs(Range(Cells(i + 1, Boucle), Cells(k, Boucle)), Range("Z" & i + 1 & ":Z" & k), Range("B" & i), Range("D" & i + 1 & ":D" & k), Range("D" & i), Range("F" & i + 1 & ":F" & k), "*ICP*")
            End If
        Next Cel1
    End With
Next Boucle

End Sub

@ bientôt

LouReeD

Salut LouReeD,

Merci beaucoup pour ta réponse ! J'essaye ça de suite mais ça ressemble beaucoup à ce que je souhaite, je pense que tu as bien compris

À bientôt,

SkillzZ

Re-bonjour,

qJe reviens à la charge avec le code que tu m'as fourni LouReeD, qui répond parfaitement à ma première demande. Après utilisation, je me rend compte que son exécution est un peu longue au vu de la taille de mon fichier. Et une solution serait que je "déplace" la condition IF que j'utilise. Je m'explique:

Ici, elle est utilisée sur chaque cellule de chacune des plages. Alors qu'à partir du moment où cette condition est satisfaite sur la première plage, je n'ai à priori plus besoin de la checker pour les plages suivantes. Je donne un exemple concret pour expliquer la chose, ce sera plus simple:

Dans l'état actuel la macro parcourt la colonne J et identifie des cellules qui valide la condition (disons J4, J11 et J19) et inscrit un résultat dans ces cellules. Ensuite elle va parcourir la colonne K, et va faire de même et les cellules qui valident la condition seront toujours sur la même ligne: K4, K11 et K19. Il en sera de même pour les colonnes qui suivent: L4, L11, L19, ....

C'est pourquoi à partir du moment où les cellules qui respectent cette condition ont été identifiées en colonne J, je n'ai plus besoin de vérifier cette condition pour les colonnes suivantes. Mais en revanche ça demande stocker l'information ou de remplir par lignes plutot que par colonne. Je travaille actuellement dessus mais je ne suis pas arrivé à mes fins.

Quelqu'un a-t-il qqchose à me proposer ?

Pour rappel, voici le code:

Sub Calcul_Somme()

'Dim debut, fin, temps As Date
Dim k, i As Long, Boucle As Integer
Dim Feuille As Worksheet
Dim Plage2, Cel1 As Range

'debut = Time

Set Feuille = ThisWorkbook.Sheets("PL")
k = Feuille.Cells(Rows.Count, 2).End(xlUp).Row

For Boucle = 10 To 22
    Set Plage2 = Feuille.Range(Cells(6, Boucle), Cells(k, Boucle))
    With Application
        For Each Cel1 In Plage2
            i = Cel1.Row
            If Cel1.Interior.Color = RGB(160, 203, 183) = True And Feuille.Range("A" & i) <> "Add ICP" Then
                Cel1 = .SumIfs(Range(Cells(6, Boucle), Cells(i - 1, Boucle)), Range("Z6:Z" & i - 1), Range("B" & i), Range("D6:D" & i - 1), Range("D" & i), Range("F6:F" & i - 1), "*ICP*") + .SumIfs(Range(Cells(i + 1, Boucle), Cells(k, Boucle)), Range("Z" & i + 1 & ":Z" & k), Range("B" & i), Range("D" & i + 1 & ":D" & k), Range("D" & i), Range("F" & i + 1 & ":F" & k), "*ICP*")
            End If
        Next Cel1
    End With
Next Boucle

'fin = Time
'temps = fin - debut
'MsgBox temps
End Sub

Merci d'avance,

SkillzZ

Je suis parvenu à faire quelque chose, mais bizarrement (à mon sens), c'est plus long..

Sub Calcul_Somme()

Dim debut, fin, temps As Date
Dim k, i, j As Long, Boucle As Integer
Dim Feuille As Worksheet
Dim Plage1, Plage2, Cel1, Cel2 As Range

debut = Time

Set Feuille = ThisWorkbook.Sheets("PL")
k = Feuille.Cells(Rows.Count, 2).End(xlUp).Row

    With Application
    Set Plage1 = Feuille.Range("J6:J" & k)
        For Each Cel1 In Plage1
            i = Cel1.Row
            If Cel1.Interior.Color = RGB(160, 203, 183) = True And Feuille.Range("A" & i) <> "Add ICP" Then
                Set Plage2 = Feuille.Range(Cells(i, 10), Cells(i, 22))
                For Each Cel2 In Plage2
                    j = Cel2.Column
                    Cel2 = .SumIfs(Range(Cells(6, j), Cells(i - 1, j)), Range("AZ6:AZ" & i - 1), Range("B" & i), Range("D6:D" & i - 1), Range("D" & i), Range("F6:F" & i - 1), "*ICP*") + .SumIfs(Range(Cells(i + 1, j), Cells(k, j)), Range("AZ" & i + 1 & ":AZ" & k), Range("B" & i), Range("D" & i + 1 & ":D" & k), Range("D" & i), Range("F" & i + 1 & ":F" & k), "*ICP*")
                Next Cel2
            End If
        Next Cel1
    End With

fin = Time
temps = fin - debut
MsgBox temps
End Sub

Peut-être que la solution de LouReeD était en fait meilleure tout simplement ! Si je me méprends quelque part, n'hésitez pas à me le dire

SkillzZ

Bonsoir,

la boucle est "lente" car elle fait appel à "Excel" continuellement... Range("A4") par exemple.

si les calculs ne sont pas imbriqués, il faudrait mettre la feuille utilisée dans les différents tests dans un tableau VBA à deux dimensions du style :

Dim Tablo() As Variant
Set Tablo = ActiveWorkBook.ActiveSheet.Range("A1:V12000").Value

Ensuite vote boucle de "J" à "V" serait une boucle sur le valeurs de ce Tablo allant de Tablo(1,10) à Tablo(12000,22)

Et il vous faut transformer vos calcul en remplaçant les Range par leur équivalent dans le Tablo.

Et il vous faut un deuxième Tablo du style :

Dim Tablo_Résultat() As Variant

pour récupérer les résultats des calculs

celui-ci peut également faire la même taille que l'autre, ou bien ne faire que la taille de la zone de modification c'est à dire de J à V sur 12000 lignes

ReDim Tablo_Résultat(12000,13)

Une fois la boucle bouclée, il suffit de retranscrire le tableau dans la plage voulue par :

ActiveWorkBook.ActiveSheet.Range("J6").Resize(12000, Ubound(Tablo_Résultat,2)).Value = Application.Transpose(Tablo_Résultat)

Enfin c'est l'idée à quelques retouches près, mais le fait que tout se passe en "mémoire" sous VBA sans accès perpétuels sur la feuille, vous gagnerez un temps fou !

Mais pour coder ceci il faut comprendre d'où l'on part, par où on passe et surtout pour aller où, et là je vous avoue être un peu perdu !

@ bientôt

LouReeD

Bonjour à tous, bonjour LouReeD,

Merci beaucoup pour ta réponse (je me permets de te tutoyer, n'hésite pas à en faire autant). Je ne maîtrise malheureusement pas les tableaux du tout, même si j'ai plusieurs fois essayé de m'y mettre à l'aide de la doc trouvée en ligne. Je vais essayer d'être plus clair vis à vis de toutes les interrogations que tu as, et je te transmets donc le fichier concerné pour t'expliquer comment les choses fonctionnent.

J'ai une liste de comptes en colonne B, à chaque ligne correspond un compte. Chaque ligne, donc chaque compte, a une cellule jaune ou verte en colonne J. Verte, signifie que c'est une cellule "totalisatrice", c'est-à-dire qu'elle fait la somme d'autres cellules. Ce sont ces cellules qui sont affectées par ma macro, car c'est dans celles-ci que cette dernière vient inscrire le résultat d'un SOMME.SI.ENS fonction de plusieurs critères. Donc toutes les cellules vertes de la colonne J sont destinées à être remplies par ma macro, à l'exception de celles qui sur leur ligne en colonne A, "Add ICP".

À partir de là, je pense que tu comprends mieux le pourquoi de cette macro, elle parcourt l'ensemble de la colonne J et vérifie à chaque fois deux conditions:

If Cel1.Interior.Color = RGB(160, 203, 183) = True And Feuille.Range("A" & i) <> "Add ICP" Then

Si les conditions sont respectées, alors la macro remplit la cellule avec le résultat d'un SOMME.SI.ENS. Puis ensuite la macro fait la même chose en colonne K, puis en colonne L, etc...

Ce qui pourrait me faire gagner du temps je pense, est de réaliser le IF uniquement sur les cellules de la colonne J. Car à partir du moment où les deux conditions sont respectées en colonne J, elles le sont sur toutes les colonnes qui suivent jusqu'à la colonne V. Jusqu'ici ce IF est répété sur l'ensemble des cellules de chaque colonne, alors qu'à priori ce n'est pas nécessaire.

Ai-je été plus clair ? Je te remercie pour ton aide en tout cas. Et pour revenir aux tableaux, j'espère parvenir à les maîtriser un jour car ce n'est pas la première fois que je me rends compte de la puissance de cet outil.

Bonne journée,

SkillzZ