[XL-2003] Boucler sur des lignes pour placer des valeurs selon certaines conditions [Résolu]

[antoine2933]

Bonjour à tous,

voilà je n'arrive pas à faire fonctionner cette macro
la seule erreur qu'il me dit c'est que j'ai une erreur 424
A quoi cette erreur correspond?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
 For Each cel In Sheets("Arbitrage_budget").Range("c38:o250")
        With cel
            If (Column.Value(f) = "rejetée") And (Column.Value(g)) = 1 Then
            Column.Value(l) = 2
            ElseIf (Column.Value(f) = "rejetée") And (Column.Value(g)) <> 1 Then
            Column.Value(l) = 3
            End If
                If Column.Value(f) <> "rejetée" Then
                Column(l) = 1
                End If
                    If Column.Value(f) = " " Then
                    Column.Value(l) = " "
                    End If
        End With
    Next cel

est ce que vous auriez une idée pour la faire marcher. j'en ai vraiment besoin

merci

[LouiMz]

Bonjour,

Sur quelle ligne l'erreur se produit-elle?

Et à quoi correspond :

Code :

Columns.Value(f)

Car cela ne fait référence à aucune cellule et donc à aucune valeur.

[antoine2933]

il ne me met pas la ligne de l'erreur c'est comme ci elle était globale

Code :

Columns.Value(f)

correspond a la valeur de la ligne correspondant a la colonne f

[Sclarckone]

=> Comment une ligne peut-elle correspondre à une colonne?!
...montre-nous la déclaration et l'affectation des variables utilisées, ça permettra d'y voir plus clair.

Sinon quand l'erreur se produit appuie sur 'Débogage' pour voir sur quelle ligne elle se produit.

[antoine2933]

je vais reprendre un le problème car je pense mettre mal expliqué

Je cherche à attribuer pour chaque ligne une valeurs situé en colonne L une valeur en fonction de 2 valeurs situé en colonne f et g

si f = rejetée et g = 1 alors valeur dans L doit être égale a 2
si f = rejetée et g <> 1 alors valeur dans L doit étre égale a 3
si f <> rejetée alors valeur dans L doit étre égale a 1
et si f="" alors rien

je pensais que mon code marcherait mais il me met erreur 424 c tout et je peux pas déboguer

[Sclarckone]

Ok, bon en fait tu es mal parti avec ton code...

Pour désigner la valeur de la cellule situé à l'intersection de la ligne i (dans ton programme, i sera une variable que tu incrémentes à chaque tour pour balayer toute ta colonne) et de la colonne F (dont l'index est 6):

Code :

ActiveSheet.Cells(i, 6).Value

Pas besoin de balayer toutes les cellules de ta feuille, contente-toi de la colonne L:

Code :

1
2
3
4
5
ActiveSheet.Range("L1").EntireColumn
 
'ou même (il me semble que ça fonctionne également):
 
ActiveSheet.Range("L:L")

Et si tu veux balayer ta colonne avec une boucle For Each...In..., il est préférable de déclarer explicitement l'élément qui change à chaque tour:

Code :

1
2
3
Dim myCell As Range
 
For Each myCell In ActiveSheet.Range("L1").EntireColumn

[antoine2933]

merci beaucoup pour tes conseils j'en suis arrivé à faire ce bout de code

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Dim myCell As Range
    Dim myCell2 As Range
    Dim i As Integer
    For i = 39 To 1121
        For Each myCell In ActiveSheet.Range("F1").EntireColumn
            If myCell = "rejetée" Then
                For Each myCell2 In ActiveSheet.Range("G1").EntireColumn
                    If myCell2 = 1 Then Range(i, 6).Value = 2
                    Else: Range(i, 6).Value = 3
                    End If
                Next myCell2
            ElseIf myCell <> "rejetée" Then Range(i, 6).Value = 1
                Else: Range(i, 6).Value = xlNone
            End If
       Next myCell
    Next i

autre solution essayé :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
    Dim myCell As Range
    Dim myCell2 As Range
    Dim i As Integer
    For i = 39 To 1121
        For Each myCell In ActiveSheet.Range("F1").EntireColumn
            Select Case myCell.Value
            Case rejetée
                For Each myCell2 In ActiveSheet.Range("G1").EntireColumn
                    Select Case myCell2.Value
                    Case 1
                    Range(i, 6).Value = 2
                    Case 0
                    Range(i, 6).Value = 3
                    End Select
                Next myCell2
            Case acceptée
            Range(i, 6).Value = 1
            Case Else
            Range(i, 6).Value = xlNone
            End Select
        Next myCell
    Next i

le soucis c'est qu'il me dit qu'il y a une incompatibilité de type '13' sur la ligne 6


Je crois que j'ai compris il n'arrive pas à prendre la valeur alphanumérique.
Mais comment faire pour qu'il utilise une variable num et une alpha pour créer une num

[Sclarckone]

Envoyé par antoine2933

sur la ligne 6

Alors j'imagine que c'est avec la première version de ton code

Code :

If myCell = "rejetée"

Lève une exception parce que myCell est un objet de type Range et "rejetée" une variable de type String. Tu essaies donc de comparer 2 choses qui n'ont rien à voir.

Par contre, ceci fonctionnera très bien pour un test:

Code :

If myCell.Text = "rejetée"

Car la propriété Text d'un objet Range renvoie bien un type String (le texte de la cellule).


Sinon pour la suite des opérations l'exemple que je t'avais donné pour désigner une cellule:

Code :

ActiveSheet.Cells(i, 6).Value

n'était qu'un cas général.

Dans ce que tu as écrit, tu fais une énumération de toute les cellules contenues dans ta colonne F. En fait, VBA va énumérer toutes les cellules contenues dans ta colonne (qui est une 'collection' de cellules), et va appliquer toutes les opérations entre For Each..In... et Next à chaque cellule appartenant à la collection.
Donc tu n'as absolument pas besoin de faire une boucle avec un For i=...To...
Cela aurait été utile si tu n'avais pas défini la collection des cellules de ta colonne et que tu les avais donc désigné les unes après les autres par leur numéro de ligne.

Pour faire quelque chose de propre, tu peux procéder comme cela:

Code :

1
2
3
4
5
6
7
8
 
'Au tout début du code déclarer une colonne qui repère le numéro de ta colonne L, dans laquelle tu veux écrire des valeurs si j'ai bien compris:
 
Const colL = Range("L1").Column
 
'Et pour accéder à l'intériereur du For Each myCell à la cellule située dans la colonne L et sur la même ligne que les cellules que tu es en train de tester:
 
ActiveSheet.Cells(myCell.Row, colL).Value = ...    'myCell.Row renvoie le numéro de la ligne sur laquelle se trouve myCell

De plus, dans ce que tu as écrit, pour chaque cellule de la colonne F, tu vas tester toutes les cellules de la colonne G!
Or je pense que tu veux juste tester les valeurs des cellules se trouvant sur une même ligne pour les colonnes F et G.

Donc pas besoin de l'énumération avec myCell2.

Dans l'énumération avec myCell (donc sur la colonne F), tu peux tester les valeurs de la colonne G comme cela:

Code :

mycell.Offset(0, 1).Value

(ou même définir une constante pour désigner la colonne G et accéder à la cellule comme expliqué précédemment).

[antoine2933]

Merci beaucoup pour ces explications, je comprend mon pb. mais par rapport
au code

Code :

ActiveSheet.Cells(i, 6).Value

je dois toujours le mettre dans une boucle comme ceci :

Code :

 For Each myCell In ActiveSheet.Cells(i, 6).Value

ou il me suffit de le déclarer simplement

lorsque je met la ligne de code

Code :

Const colL = Range("L1").Column

une erreur intervient sur le mot column : constante requise je comprend pas
cette ligne de code est censé indiquer l'emplacement de la valeur calculée
non?

mon code final devrait ressembler à ça ??

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
    Dim myCell As Range
    Dim myCell2 As Range
    Dim i As Integer
    Const colL = Range("L1").Column
    For Each myCell In ActiveSheet.Cells(i, 6).Value
            If myCell.Text = "rejetée" Then
                For Each myCell2 In ActiveSheet.Cells(i, 7).Value
                    If myCell2 = 1 Then
                    ActiveSheet.Cells(myCell.Row, colL).Value = 2
                    Else: ActiveSheet.Cells(myCell.Row, colL).Value = 3
                    End If
                Next myCell2
            ElseIf myCell.Text <> "rejetée" Then
            ActiveSheet.Cells(myCell.Row, colL).Value = 1
                Else: ActiveSheet.Cells(myCell.Row, colL).Value = xlNone
            End If
    Next myCell

il me semble que ce code est bon mais il me met que j'ai une erreur de type 1004 sur la ligne

Code :

For Each myCell In ActiveSheet.Cells(i, 6).Value

alors qu’auparavant il n'y en avait pas. loose

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
    Dim myCell As Range 
    Dim myCell2 As Range
    Dim i As Integer
    For Each myCell In ActiveSheet.Cells(i, 6).Value
            If myCell.Text = "rejetée" Then
                For Each myCell2 In ActiveSheet.Cells(i, 7).Value
                    If myCell2 = 1 Then
                    ActiveSheet.Cells(myCell.Row, 12).Value = 2
                    Else: ActiveSheet.Cells(myCell.Row, 12).Value = 3
                    End If
                Next myCell2
            ElseIf myCell.Text <> "rejetée" Then
            ActiveSheet.Cells(myCell.Row, 12).Value = 1
                Else: ActiveSheet.Cells(myCell.Row, 12).Value = xlNone
            End If
    Next myCell

[Sclarckone]

Envoyé par antoine2933

Merci beaucoup pour ces explications, je comprend mon pb. mais par rapport
au code

Code :

ActiveSheet.Cells(i, 6).Value

Absolument pas! Car cela désigne une seule cellule et pas toute la colonne. J'ai comme l'impression que tu n'es pas très familier avec les concepts d'objets en VBA et structures de type boucle.
Si tu as d'autres modules dans ce genre à coder en VBA, tu devrais t'y intéresser...

Envoyé par antoine2933

Code :

Const colL = Range("L1").Column

une erreur intervient sur le mot column : constante requise je comprend pas
cette ligne de code est censé indiquer l'emplacement de la valeur calculée
non?

Ca c'est de ma faute...utilise 12 à la place de Range("L1").Column et ça devrait fonctionner.



Pour ce qui est de l'architecture de ton code, je vais te donner une solution (avec des If imbriqués) et je t'invite à bien comprendre comment elle fonctionne (n'hésite pas à poser des questions):

Code :

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
Const colL = 12
Const colG = 7
Dim myCell As Range
 
For Each myCell In ActiveSheet.Range("F1").EntireColumn
 
   If myCell.Text = "rejetée" Then
 
        If ActiveSheet.Cells(myCell.Row, colG).Value = 1 Then
            ActiveSheet.Cells(myCell.Row, colL).Value = 2
        Else
            ActiveSheet.Cells(myCell.Row, colL).Value = 3
        End If
 
    ElseIf myCell.Text <> "rejetée" Then
 
        ActiveSheet.Cells(myCell.Row, colL).Value = 1
 
    Else
 
        ActiveSheet.Cells(myCell.Row, colL).Value = xlNone
 
    End If
 
Next myCell

[Pierre Fauconnier]

Salut.

Envoyé par antoine2933

je vais reprendre un le problème car je pense mettre mal expliqué

Je cherche à attribuer pour chaque ligne une valeurs situé en colonne L une valeur en fonction de 2 valeurs situé en colonne f et g

si f = rejetée et g = 1 alors valeur dans L doit être égale a 2
si f = rejetée et g <> 1 alors valeur dans L doit étre égale a 3
si f <> rejetée alors valeur dans L doit étre égale a 1
et si f="" alors rien

je pensais que mon code marcherait mais il me met erreur 424 c tout et je peux pas déboguer

En reprenant cette demande, je propose une solution sans boucle (pensez d'abord Excel avant de penser VBA...) en plaçant une formule conditionnelle.

Code :

Range("L1:L10").Formula = "=IF(F1=""rejetée"",IF(G1=1,2,3),IF(F1="""","""",1))"

L'objet Range doit être adapté.

Si l'on veut figer les valeurs, on ajoutera la ligne

Code :

Range("L1:L10").Value = Range("L1:L10").Value

[EDIT] C'est tout. Pas de boucle, pas de conditions "en dur" dans du code spaghetti. Juste deux petites lignes de code. En cas de modification des conditions, il suffira de modifier la formule passée à la propriété Formula [/EDIT]

L'avantage à procéder de cette façon réside dans le fait que si les conditions évoluent, il suffit de modifier la formule sans toucher à la structure du code VBA. En inscrivant les conditions dans le code avec des boucles et des IF, on doit modifier "beaucoup" de code pour le faire correspondre aux nouvelles conditions.

[antoine2933]

Merci pour vous solution mais il semble y avoir quelques problèmes

Sclarckone j'ai lancer ton bout de code mais le résultat n'est pas le bon il m'affiche bien dans la colonne L un résultat mais seulement pour la ligne 1 et il ne correpond pas à un des résultats mis en place

-4142

je continue à lire ton code pour le comprendre et voir la faute .

pourquoi mais tu

Code :

Const colG = 7

et pas

Code :

Const colF = 6

aussi



Pierre

j'ai aussi essayé ton bout de code il me prend bien un résultat pour toutes les lignes mais ne correspond pas au résultat attendu N/A au lieu de 1,2 ou 3

[Pierre Fauconnier]

Envoyé par antoine2933

...
Pierre

j'ai aussi essayé ton bout de code il me prend bien un résultat pour toutes les lignes mais ne correspond pas au résultat attendu N/A au lieu de 1,2 ou 3

Je ne vois pas comment la formule pourrait renvoyer N/A.

Le code ne fait que placer une "bête" formule utilisant SI() en L1:L10. Si tu n'ajoutes pas la deuxième ligne de code, tu verras la fonction dans ta feuille Excel.

Peux-tu copier cette fonction et la mettre ici? Tu peux aussi faire un copier/coller exact de mon code, tel que tu l'as essayé?

Tu dois aussi bien sûr adapter à ta plage de cellules. Mais sois bien convaincu que c'est la façon la plus simple de résoudre ton problème.

[gFZT82]

Bonjour,

Si tu as absolument besoin de passer par VBA, voici un code commenté :

Code :

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
Sub Test()
Dim DerLig As Long
Dim Ws As Worksheet
Dim i As Integer
    Set Ws = ActiveSheet
    With Ws
    'Recherche de la dernière ligne renseignée dans la colonne F
    DerLig = .Range("F" & .Rows.Count).End(xlUp).Row
    For i = 1 To DerLig
        'si F = "rejetée"
        If .Range("F" & i) = "rejetée" Then
            ' si G = 1 alors la valeur de L est égale a 2
            If .Range("G" & i) = 1 Then
                .Range("L" & i) = 2
            'sinon (G <> 1), la valeur de L est égale a 3
            Else
                .Range("L" & i) = 3
            End If
        'sinon (F <> "rejetée")
        Else
            'si F n'est pas vide alors la valeur de L est égale a 1
            If .Range("F" & i) <> "" Then
                .Range("L" & i) = 1
            'sinon L est vide
            Else
                .Range("L" & i) = ""
            End If
        End If
    Next i
    End With
    Set Ws = Nothing
End Sub

Cela dit, la solution préconisée par Pierre Fauconnier reste à privilégier pour les motifs qu’il a évoqués.

Cordialement.

[antoine2933]

Pierre,

voyons si j'ai bien compris je dois placer simplement ces 2 lignes dans le code de la feuille et le résultats pour chaque ligne (1 à 10) doit arriver dans la colonne L c'est ça

Code :

1
2
Range("L1:L10").Formula = "=IF(F1=""rejetée"",IF(G1=1,2,3),IF(F1="""","""",1))"
Range("L1:L10").Value = Range("L1:L10").Value

ou faut'il que je rajoute une boucle

Code :

1
2
for i = 1 to 10 
next i

[Pierre Fauconnier]

Tu dois juste mettre les deux lignes. La seule chose à faire, c'est adapter la plage (Range) qui, dans mon exemple, reprend les cellules L1:L10.

Tu ne dois pas boucler. De plus, tu sors la logique du code VBA, car tu la gères beaucoup plus facilement dans la formule que dans un code VBA itératif.

[antoine2933]

C'est bon j'ai trouver mon bonheur

Vraiment MERCI BEAUCOUP à vous tous je garde vos solutions et vos conseils de côté et je vais bien les regarder afin de mieux comprendre.

Juste une question pour Pierre pourquoi préconise-t-on plus la formule conditionnelle à du codage en VBA

juste pour ça simplicité par rapport à l'autre méthode ou y a-t-il autre chose ?

[Pierre Fauconnier]

Envoyé par antoine2933

...Juste une question pour Pierre pourquoi préconise t'on plus la formule conditionnelle à du codage en VBA ...

La discussion apporte d'elle-même la réponse. Il a fallu une quinzaine de messages pour adapter les boucles, les codes, les noms de variables, etc... alors que deux lignes de VBA suffisent pour obtenir la solution.


Avec une solution qui amène des IF...END IF imbriqués, tu as un code "à rallonge"... et encore, tu as ici un ensemble de conditions simples. Imagine que tu les complexifies un tout petit peu, et tu es parti pour la gloire. Si, demain, tu dois tenir compte des rejetée, acceptée, En attente, tu vas te casser les dents avec ton code.

Avec celui que je propose, tu modifies simplement la formule que tu mets, par code, dans les cellules de L1:L10. De plus, tu peux créer cette formule dans Excel (avec la vérification par Excel de la syntaxe) puis la récupérer facilement via la fenêtre d'exécution en VBA pour l'intégrer à ton code.

Enfin, les boucles, c'est beaucoup plus lent que de placer une formule en Excel puis de convertir en valeurs (pas sur quelques lignes, mais sur des centaines, voire des milliers de lignes, ça compte).

Une dernière raison réside dans le fait que nous sommes a priori pas des programmeurs et que, du plus,nous travaillons avec vbA (pour Application, ici Excel). Alors, autant nous laisser aider par Excel plutôt que de partir dans du code VBA qui nous dépasse vite. [EDIT] Plus il y a de lignes de code, plus il y a de risques de bugs... [/EDIT]

[antoine2933]

Merci beaucoup pour ta réponse Pierre

je comprend ta logique je pense utiliser ce principe maintenant

Très bonne journée