Boucle ligne et colonne

**Loupion** · 18/11/2014, 09h59

Bonjour,

J ai un soucis avec une boucle que je voudrais realiser sur une plage de donnees (plusieurs lignes et colonnes) pour y injecter le resultat d une operation.

Je code initial (sans la boucle fonctionne), en revanche lorsque je passe a la seconde etape (creation de la boucle pour faire le calcul sur toutes les cellules sur lesquelles je suis interesse), j ai un message d erreur disant que la boucle n est pas valide

Ci joint le lien pour le fichier

http://cjoint.com/?3Kske6679F8

Le message d erreur est "invalid next control variable reference" et je n arrive pas a trouver sur le forum la solution

Merci pour votre aide

**Daniel.C** · 18/11/2014, 11h02

Bonjour,

Sauf erreur, "n" n'est pas renseigné et il n'y a pas de "Next j" correspondant à :

Code :

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

For j = c To n

**Loupion** · 18/11/2014, 11h09

bonjour Daniel

En fait

Code :

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

For j = c To n

"c" et "n" correspondent aux colonnes sur lesquelles je veux que l operation se realise

**Daniel.C** · 18/11/2014, 11h27

"c" et "n" correspondent aux colonnes sur lesquelles je veux que l operation se realise

J'ai bien compris, mais tu ne mets aucune valeur dans "n". Je devrai trouver quelque part :

Code :

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

1
2
c = 5
n = 10

ou une valeur quelconque, or nulle part tu ne renseignes ces variables.

**Loupion** · 18/11/2014, 11h37

Merci de vous pencher sur mon probleme Daniel.

Je ne suis pas certain d avoir bien compris.

Dois-je declarer la variable " j " comme ci dessous?

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
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
Sub Test2()
 
Dim i As Variant
Dim j As Variant
 
Dim BU As Range, Month As Range, Scenario As Range, Brand As Range, Axe As Range, Category As Range, Scenario2 As Range, Amount As Range
Dim BUdata As Range, Monthdata As Range, Scenariodata As Range, Branddata As Range, Axedata As Range, Categorydata As Range, Scenario2data As Range
Dim Result As Double
 
 
For i = 8 To 21
For j = 3 To 14
 
 
Set Month = ActiveWorkbook.Sheets("Extract").Range("B:B")
Set BU = ActiveWorkbook.Sheets("Extract").Range("D:D")
Set Scenario = ActiveWorkbook.Sheets("Extract").Range("E:E")
Set Brand = ActiveWorkbook.Sheets("Extract").Range("H:H")
Set Axe = ActiveWorkbook.Sheets("Extract").Range("I:I")
Set Category = ActiveWorkbook.Sheets("Extract").Range("A:A")
Set Scenario2 = ActiveWorkbook.Sheets("Extract").Range("E:E")
Set Amount = ActiveWorkbook.Sheets("Extract").Range("K:K")
 
 
Set Monthdata = ActiveWorkbook.Sheets("Analysis").Range("5:j")       'Ligne 5, colonne variable'
Set BUdata = ActiveWorkbook.Sheets("Analysis").Range("A1")          'fixe'
Set Scenariodata = ActiveWorkbook.Sheets("Analysis").Range("6:j")   'Ligne 6, colonne variable'
Set Branddata = ActiveWorkbook.Sheets("Analysis").Range("i:A")       'Ligne variable, colonne A'
Set Axedata = ActiveWorkbook.Sheets("Analysis").Range("i:B")          'Ligne variable, colonne B'
Set Categorydata = ActiveWorkbook.Sheets("Analysis").Range("A2")    'fixe'
Set Scenario2data = ActiveWorkbook.Sheets("Analysis").Range("A4")   'fixe'
 
Result = WorksheetFunction.SumIfs(Amount, Month, Monthdata, BU, BUdata, Scenario, Scenariodata, Brand, Branddata, Axe, Axedata, Category, Categorydata) + WorksheetFunction.SumIfs(Amount, Month, Monthdata, BU, BUdata, Scenario, Scenario2data, Brand, Branddata, Axe, Axedata, Category, Categorydata)
 
Worksheets("Analysis").Range(i, j) = Result
 
 
 
Next i
Next j
 
 
 
End Sub

**vinc_bilb** · 18/11/2014, 11h38

Bonjour,

Je confirme les 2 points .... variables de boucles non définies et next manquant .... (+ type variant mal adapté ....)
De plus, je n'ai pas vraiment compris votre feuille 'analysis (comment sont calculées les données), mais ça ressemble fort à ce que pourrait faire en automatique un TCD .... Voir capture ci-jointe Nom : Capture.JPG
Affichages : 223
Taille : 138,8 Ko

Nom : Capture.JPG
Affichages : 223
Taille : 138,8 Ko

Bonne journée

**Loupion** · 18/11/2014, 11h41

Bonjour Vinc_bilb

le TCD ne me permet pas de selectionner differents scenario pour les mois de l annee

Explication.

nous sommes en Juin, je veux avoir les donnees relles de Janvier a Juin et de Juillet a Decembre les donnees du budget.

Le TCD ne me permet pas cela a ma connaissance.

Pour ce qui est de figer les mois, je suis au courant de cette possibilite et option dans excel, tout comme les slicers qui sont plus agreable, mais dans ce cas je dois passer par un code VBA pour remplacer mes somme.si.ens car ils allourdissent considerablement le fichier et ne le rende pas utilisable.

**Daniel.C** · 18/11/2014, 11h52

Envoyé par Loupion

Merci de vous pencher sur mon probleme Daniel.

Je ne suis pas certain d avoir bien compris.

Dois-je declarer la variable " j " comme ci dessous?

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
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Sub Test2()
 
Dim i As Variant
Dim j As Variant
 
Dim BU As Range, Month As Range, Scenario As Range, Brand As Range, Axe As Range, Category As Range, Scenario2 As Range, Amount As Range
Dim BUdata As Range, Monthdata As Range, Scenariodata As Range, Branddata As Range, Axedata As Range, Categorydata As Range, Scenario2data As Range
Dim Result As Double
 
For i = 8 To 21
For j = 3 To 14
 
Set Month = ActiveWorkbook.Sheets("Extract").Range("B:B")
Set BU = ActiveWorkbook.Sheets("Extract").Range("D:D")
Set Scenario = ActiveWorkbook.Sheets("Extract").Range("E:E")
Set Brand = ActiveWorkbook.Sheets("Extract").Range("H:H")
Set Axe = ActiveWorkbook.Sheets("Extract").Range("I:I")
Set Category = ActiveWorkbook.Sheets("Extract").Range("A:A")
Set Scenario2 = ActiveWorkbook.Sheets("Extract").Range("E:E")
Set Amount = ActiveWorkbook.Sheets("Extract").Range("K:K")
 
 
Set Monthdata = ActiveWorkbook.Sheets("Analysis").Range("5:j")       'Ligne 5, colonne variable'
Set BUdata = ActiveWorkbook.Sheets("Analysis").Range("A1")          'fixe'
Set Scenariodata = ActiveWorkbook.Sheets("Analysis").Range("6:j")   'Ligne 6, colonne variable'
Set Branddata = ActiveWorkbook.Sheets("Analysis").Range("i:A")       'Ligne variable, colonne A'
Set Axedata = ActiveWorkbook.Sheets("Analysis").Range("i:B")          'Ligne variable, colonne B'
Set Categorydata = ActiveWorkbook.Sheets("Analysis").Range("A2")    'fixe'
Set Scenario2data = ActiveWorkbook.Sheets("Analysis").Range("A4")   'fixe'
 
Result = WorksheetFunction.SumIfs(Amount, Month, Monthdata, BU, BUdata, Scenario, Scenariodata, Brand, Branddata, Axe, Axedata, Category, Categorydata) + WorksheetFunction.SumIfs(Amount, Month, Monthdata, BU, BUdata, Scenario, Scenario2data, Brand, Branddata, Axe, Axedata, Category, Categorydata)
 
Worksheets("Analysis").Range(i, j) = Result
 
Next i
Next j
 
End Sub

A toi de voir si tu obtiens ce que tu souhaites. Je n'ai pas cherché à savoir ce que fait ton code. Tu peux aussi sortir de la boucle:

Code :

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

1
2
3
4
5
6
7
8
 Set Month = ActiveWorkbook.Sheets("Extract").Range("B:B")
 Set BU = ActiveWorkbook.Sheets("Extract").Range("D:D")
 Set Scenario = ActiveWorkbook.Sheets("Extract").Range("E:E")
 Set Brand = ActiveWorkbook.Sheets("Extract").Range("H:H")
 Set Axe = ActiveWorkbook.Sheets("Extract").Range("I:I")
 Set Category = ActiveWorkbook.Sheets("Extract").Range("A:A")
 Set Scenario2 = ActiveWorkbook.Sheets("Extract").Range("E:E")
 Set Amount = ActiveWorkbook.Sheets("Extract").Range("K:K")

et déplacer ces lignes au dessus de :

Code :

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

For i = 8 To 21

Dois-je declarer la variable " j " comme ci dessous?

Ce n'est pas faux. En théorie, il est préférable de la déclarer comme "Integer" puisqu'il s'agit d'un nombre entier inférieur à 32768 (et supérieur à -32768). Pour les types de variables, regarde la page :

http://silkyroad.developpez.com/VBA/LesVariables/

**HopPopPop** · 18/11/2014, 12h18

Bonjour à tous,

Par rapport à ton post.
En premier, il faut inverser les lignes :

Code :

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

1
2
Next i
Next j

Puis, il faut remplacer :

Code :

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

Set Scenariodata = ActiveWorkbook.Sheets("Analysis").Range("6:j")   'Ligne 6, colonne variable'

par

Code :

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

Set Scenariodata = ActiveWorkbook.Sheets("Analysis").Cells(6, j)  'Ligne 6, colonne variable'

et ainsi de suite.

**Loupion** · 18/11/2014, 14h07

Hello HopPopPop

Merci pour ton aide

J ai effectue les modifications.

Pour l instant ca fonctionne.

Merci pour ton aide

**HopPopPop** · 18/11/2014, 14h20

L'instruction Range("6:j") provoquera une erreur de compilation, c'est pourquoi j'avais proposé Cells(6,j).

Car j étant une variable, elle ne doit pas se trouver entre les guillemets ; pour qu'elle le soit, il faudrait écrire alors Range(j & ":6"). Attention, la variable ne doit pas être une valeur, mais une lettre. Ici, j étant la colonne ; il faut à mon sens utiliser Cells.

**Loupion** · 18/11/2014, 14h29

HopPoppop,

Nikel cela fonctionne et je comprend les changements.

En revanche le code est assez long a s executer.

Y a t il un moyen de l optimiser?

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
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
Sub Test2()
 
Dim i As Integer
Dim j As Integer
 
Dim BU As Range, Month As Range, Scenario As Range, Brand As Range, Axe As Range, Category As Range, Scenario2 As Range, Amount As Range
Dim BUdata As Range, Monthdata As Range, Scenariodata As Range, Branddata As Range, Axedata As Range, Categorydata As Range, Scenario2data As Range
Dim Result As Double
 
For j = 3 To 14
For i = 8 To 21
 
 
Set Month = ActiveWorkbook.Sheets("Extract").Range("B:B")
Set BU = ActiveWorkbook.Sheets("Extract").Range("D:D")
Set Scenario = ActiveWorkbook.Sheets("Extract").Range("E:E")
Set Brand = ActiveWorkbook.Sheets("Extract").Range("H:H")
Set Axe = ActiveWorkbook.Sheets("Extract").Range("I:I")
Set Category = ActiveWorkbook.Sheets("Extract").Range("A:A")
Set Scenario2 = ActiveWorkbook.Sheets("Extract").Range("E:E")
Set Amount = ActiveWorkbook.Sheets("Extract").Range("K:K")
 
 
Set Monthdata = ActiveWorkbook.Sheets("Analysis").Cells(5, j)      'Ligne 5, colonne variable'
Set BUdata = ActiveWorkbook.Sheets("Analysis").Range("A1")          'fixe'
Set Scenariodata = ActiveWorkbook.Sheets("Analysis").Cells(6, j)  'Ligne 6, colonne variable'
Set Branddata = ActiveWorkbook.Sheets("Analysis").Cells(i, 1)      'Ligne variable, colonne A'
Set Axedata = ActiveWorkbook.Sheets("Analysis").Cells(i, 2)         'Ligne variable, colonne B'
Set Categorydata = ActiveWorkbook.Sheets("Analysis").Range("A2")    'fixe'
Set Scenario2data = ActiveWorkbook.Sheets("Analysis").Range("A4")   'fixe'
 
Result = WorksheetFunction.SumIfs(Amount, Month, Monthdata, BU, BUdata, Scenario, Scenariodata, Brand, Branddata, Axe, Axedata, Category, Categorydata) + WorksheetFunction.SumIfs(Amount, Month, Monthdata, BU, BUdata, Scenario, Scenario2data, Brand, Branddata, Axe, Axedata, Category, Categorydata)
 
Worksheets("Analysis").Cells(i, j) = Result
 
 
 
Next i
Next j
 
MsgBox ("Update done")
 
 
End Sub

**HopPopPop** · 18/11/2014, 15h31

A essayer :
A mettre sur une ligne avant les boucles For :

Code :

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

Application.ScreenUpdating = False

A mettre sur une ligne après ces boucles, après le dernier Next :

Code :

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

Application.ScreenUpdating = True

**Daniel.C** · 18/11/2014, 15h51

Bis : et sort cette séquence de la double boucle, il suffit de l'exécuter une fois :

Code :

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

1
2
3
4
5
6
7
8
Set Month = ActiveWorkbook.Sheets("Extract").Range("B:B")
Set BU = ActiveWorkbook.Sheets("Extract").Range("D:D")
Set Scenario = ActiveWorkbook.Sheets("Extract").Range("E:E")
Set Brand = ActiveWorkbook.Sheets("Extract").Range("H:H")
Set Axe = ActiveWorkbook.Sheets("Extract").Range("I:I")
Set Category = ActiveWorkbook.Sheets("Extract").Range("A:A")
Set Scenario2 = ActiveWorkbook.Sheets("Extract").Range("E:E")
Set Amount = ActiveWorkbook.Sheets("Extract").Range("K:K")

Essaie comme ça :

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
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
Sub Test3()
 
Dim i As Integer
Dim j As Integer
 
Dim BU As Range, Month As Range, Scenario As Range, Brand As Range, Axe As Range, Category As Range, Scenario2 As Range, Amount As Range
Dim BUdata As Range, Monthdata As Range, Scenariodata As Range, Branddata As Range, Axedata As Range, Categorydata As Range, Scenario2data As Range
Dim Result As Double
 
Application.ScreenUpdating = False
With ActiveWorkbook.Sheets("Extract")
    Set Month = .Range("B:B")
    Set BU = .Range("D:D")
    Set Scenario = .Range("E:E")
    Set Brand = .Range("H:H")
    Set Axe = .Range("I:I")
    Set Category = .Range("A:A")
    Set Scenario2 = .Range("E:E")
    Set Amount = .Range("K:K")
End With
Set Categorydata = ActiveWorkbook.Sheets("Analysis").Range("A2")    'fixe'
Set Scenario2data = ActiveWorkbook.Sheets("Analysis").Range("A4")   'fixe'
Set BUdata = ActiveWorkbook.Sheets("Analysis").Range("A1")          'fixe'
 
For j = 3 To 14
For i = 8 To 21
 
With ActiveWorkbook.Sheets("Analysis")
    Set Monthdata = .Cells(5, j)      'Ligne 5, colonne variable'
    Set Scenariodata = .Cells(6, j)  'Ligne 6, colonne variable'
    Set Branddata = .Cells(i, 1)      'Ligne variable, colonne A'
    Set Axedata = .Cells(i, 2)         'Ligne variable, colonne B'
    Result = WorksheetFunction.SumIfs(Amount, Month, Monthdata, BU, BUdata, Scenario, Scenariodata, Brand, Branddata, Axe, Axedata, Category, Categorydata) + WorksheetFunction.SumIfs(Amount, Month, Monthdata, BU, BUdata, Scenario, Scenario2data, Brand, Branddata, Axe, Axedata, Category, Categorydata)
    .Cells(i, j) = Result
End With
 
Next i
Next j
 
Application.ScreenUpdating = True
MsgBox ("Update done")
 
 
End Sub

**Loupion** · 18/11/2014, 16h15

Daniel,

Le code que vous me proposez est assez interesse et plus organise que le mien. En revanche, il n est pas plus rapide a executer.
Je ne sais pas si reduire le range de chacune des colonnes de la plage de donnees permettrait de gagner en efficacite ou si il faut que je travaille sur la formule sumifs qui est lourde a executer

**Daniel.C** · 18/11/2014, 16h25

Est-ce que tu peux mettre ton classeur en PJ ?

**Loupion** · 18/11/2014, 16h34

Ci dessous le lien

Mais il s agit d un fichier de test.

En realite le fichier contient 52 lignes a mettre a jour dans la feuille analysis et bien plus de donnees dans la feuille extraction, c est pourquoi je souhaite l optimiser car suir le fichier de test cela prend 10 secondes mais plus de 2 minutes sur le fichier reeel

http://cjoint.com/?3KsqMOPeXAo

**Daniel.C** · 18/11/2014, 17h16

Est-ce que les noms de produits sont les mêmes pour les différentes marques ?

**Loupion** · 18/11/2014, 17h26

oui se sont les memes

je me demande si je ne devrais pas essayer de passer par un concatenate et un simple sumif mais je dois travailler sur le code et ne suis pas certain de parvenir a creer un code imbriqué

**Daniel.C** · 18/11/2014, 18h16

Essaie la macro suivante et regarde si le résultat est correct (elle plante 1 fois, il faut cliquer sur "Continuer"). Si le résultat est bon, je chercherai la cause de l'erreur.

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
19
20
21
22
23
24
25
Sub Test()
     Dim Tabl As Variant, Mois As Variant, Brand As Variant, Prod As Variant
     Dim I As Long, Result As Variant, Ligne As Long
     Dim Lig As Integer, Col As Integer
     With Sheets("Extract")
        Ligne = .Cells(.Rows.Count, 1).End(xlUp).Row
        Tabl = .Range("A11", .Cells(Ligne, 11))
     End With
     With Sheets("Analysis")
        .[C7:N21].ClearContents
        Result = .[C7:N21]
        Mois = .[C5:N5]
        Brand = Application.Transpose(.[A7:A21])
        Prod = Application.Transpose(.[B7:B21])
        For I = 1 To UBound(Tabl, 1)
            Lig = 1
            Do Until Tabl(I, 8) = Brand(Lig) And Tabl(I, 9) = Prod(Lig)
                Lig = Lig + 1
            Loop
            Col = Application.Match(Tabl(I, 2), Mois, 0)
            Result(Lig, Col) = Result(Lig, Col) + Tabl(I, 11)
        Next I
        .[C7:N21] = Result
     End With
End Sub

Boucle ligne et colonne [XL-2010]

Macros et VBA Excel

Discussions similaires

Partager

Partager