Débutant // Problème code VBA

**SAKAROV** · 08/03/2010, 00h00

Bonjour,

Je souhaiterai faire un petit code VBA me permettant ceci :

(considérons qu'en ligne 1 j'ai mes en-têtes)

si la valeur dans la cellule A3 est identique à la cellule A2, alors vois la cellule A4, si celle ci est identique à la cellule A3, alors va a la cellule 5, etc etc, jusqu'à ce que "NON", la valeur dans la cellule A32 (par exemple) est différente de la cellule A31 --> ALORS dans ce cas :

Code :

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

=SOMMEPROD(J2:J31;E2:E31)/SOMMEPROD(K2:K31;E2:E31)

C'est à dire qu'avant on a donc établi une zone, un "tableau", qui est dans notre exemple A2:K31 (par exemple) et que donc on prend dans ce tableau les colonne J,E et K pour appliquer la formule ci-dessus, et mettre le resultat dans la cellule L2 (à droite de la derniere colonne, en premiere ligne).

Ensuite, mettre cette cellule en format Pourcentage.

Ensuite, dire au code de revenir à la cellule ou la valeur avait été indiquée comme "différente" ---> donc, dans notre exemple, la cellule A32, et là, recommencer la manoeuvre (boucler, donc) jusqu'à ce qu'une cellule soit vide.

c'est à dire, à la fin de mon tableau général, qui fait 30 000 lignes ^^

je précise que la "longueur" des tableaux (dans notre exemple, le premier serait de A2 à K31 (A2:K31) ) est toujours (souvent) différente. Autrement dis, dans la deuxième "boucle" de recherche de valeur dans la colonne A, celle ci peut s'avérer "fausse" (le chiffre est différent) au bout de 15 lignes, ou de 300 lignes.

A noter qu'il y en a 450 ("tableaux", diras-t-on) sur un total de 30.000 lignes...

A noter aussi que j'ai coloré chacun de ces 450 tableaux afin de les différencier (c'est plus lisible) donc il est possible de les reconnaitre non pas que par la valeur dans la colonne A (qui se répète tout le long du tableau) mais aussi par la couleur, qui change.

Enfin, c'est le même principe quand même quoi.

Bref. Je sais que ça va sûrement vous paraitre facile, enfin je pense.. Mais pour moi là, je galèèèère et ce depuis 3 jours à m'arracher les cheveux et à lire des trucs sur les forums...

Quelqu'un à une idée ?

Merci à vous

**Galatane** · 08/03/2010, 08h13

Bonjour

Tout d'abord je ne pense pas me tromper en disant que les test sur les couleur de cellule ne sont pas possible sur excel 2003.

J'ai 2 questions :
- Dans quelle cellule faut il afficher le resultat?
- Faut il toujours se referencer aux lignes E,J et K pour la formule? (ou faut il prendre 4, 9 et 10 lignes plus bas?)

**SAKAROV** · 08/03/2010, 10h03

Dans la colonne L, juste à droite du "tableau". Pour le tableau 1 qui se fini en ligne 31 et commence en ligne 2, ce serait en L2. Et comme le Tableau 1 fini en ligne 31, le tableau 2 commence en ligne 32, donc automatiquement le resultat du tableau 2 sera en cellule L32.

Il faut donc effectuer la formule suivante :

Code :

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

=SOMMEPROD(J2:J31;E2:E31)/SOMMEPROD(K2:K31;E2:E31)

En fonction du tableau dans lequel on se trouve ; la formule ci-dessus est pour le tableau 1 ; pour le tableau 2 qui comme donc en ligne 32, et qui disons finit en ligne 100, ca donnera :

Code :

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

=SOMMEPROD(J32:J100;E32:E100)/SOMMEPROD(K32:K100;E32:E100)

**kahoerre** · 08/03/2010, 13h39

Envoyé par Galatane

Bonjour

Tout d'abord je ne pense pas me tromper en disant que les test sur les couleur de cellule ne sont pas possible sur excel 2003.

C'est possible !
Avec un If Range("C" & ind).Interior.ColorIndex = "Le numéro de la couleur(exemple: 3 pour rouge)"

Liste code couleurs.

**SAKAROV** · 08/03/2010, 14h15

Auriez-vous une idée de boucle pour ça ??

Merci

PS: merci pour la couleur. Faut soit passer par la couleur soit par la valeur contenue en colonne A (celle-ci se répète tout le long, et lorsqu'elle change, c'est la qu'on stoppe le tableau).

Passer par la valeur me parait plus simple.

Quelqu'un aurait une idée je vous prie ?

Je récapitule ma demande brièvement :

Faire un code qui, tout d'abord, incrémente chaque cellule contenue en colonne A tant que la valeur est la même. Une fois une valeur différente, on reconnait ainsi jusqu'où va le premier tableau. Ainsi, appliquer la formule précisée en haut (en prenant donc le contenu des colonnes SITUEES DANS LE TABLEAU défini juste auparavant) ; une fois le calcul fait, le résultat va dans la colonne L dans la première ligne du tableau ; PUIS, faire une BOUCLE pour que l'opération se répète JUSQU'A ce que une cellule vide soit rencontrée en colonne A.

Si je suis pas clair, hésitez pas à poster un com, je surveille toute les demi heures !! Vu que c'est assez urgent, (très urgent en vérité), et que je galère sévère... j'ai des pistes, mais j'suis trop débutant...

**Galatane** · 08/03/2010, 14h50

J'ai bien une idée, mais je n'arrive pas a trouver le bon code. Je debute aussi le vb en fait.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Dim lignea, ligneb as integer
lignea = 2
ligneb = 0
 
while fin = False
 
    ligneb = Nb.si ( "A:A"; "A" & lignea) + ligneb + 2
    Range("L" & ligneb) = sommeprod(Range("J" & lignea & ":J" & ligneb) ; Range("E" & lignea & ":E" & ligneb)) / sommeprod(Range("K" & lignea &":K" & ligneb) ;Range( "E" & lignea & ":E" & ligneb ))
    If Range("A" & ligneb + 1 ) = "" then Fin = true
    lignea = ligneb
wend

Le problème étant de réussir à ecrire en VB :
-le NB.si (Dcount, mais je n'ai pas réussi a le paramétrer correctement)
-le sommeprod (Pour le moment, je n'ai pas encore eu le temps de chercher)

**SAKAROV** · 08/03/2010, 15h39

MERCI C EST UN DEBUT.

PEUT ETRE QUELQU'UN POURRAIT COMPLETER ?

Pour ce qui est du SOMMEPROD, la fonction je l'ai déjà écrite en haut, y'a pas à la changer.faut juste changer les "CHIFFRES" à l'intérieur correspondants aux lignes du tableau concerné.

Code :

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

=SOMMEPROD(J2:J31;E2:E31)/SOMMEPROD(K2:K31;E2:E31)

celle-ci serait pour un tableau A2:K31

Code :

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

=SOMMEPROD(J32:J318;E32:E318)/SOMMEPROD(K32:K318;E32:E318)

celle-la pour un tableau de A32:K318

etc..

la taille des tableaux change tout le temps, le souci est là !! du coup, faut utiliser un repère (donc, le contenu de la cellule A) pour "délimiter" le tableau.

Ensuite, une fois le tableau repéré, bah suffit de mettre le SOMMEPROD qui va avec quoi :p

Tu vois ?

**Qwazerty** · 08/03/2010, 17h18

Envoyé par kahoerre

C'est possible !
Avec un If Range("C" & ind).Interior.ColorIndex = "Le numéro de la couleur(exemple: 3 pour rouge)"

Salut
C'est vrai sauf si les couleurs sont issues d'une Mise En Forme Conditionnelle.

A++
Qwaz

**kahoerre** · 08/03/2010, 17h01

Petite astuce pour avoir le code de tes formules compilé 'par Excel' ;

Tu fais Outils\Macro\Nouvelle Macro , la tu rentres dans la céllule de ton choix ta formule, tu arrête l'enregistrement en retournant dans le même menu.

Et tu peux ensuite voir les formules de tes cellules qui sont en anglais,
dans le code de ta macro.

Si je trouve du temps je me pencherai sur ton problème.

Cordialement,
- Kahoèrre

**Galatane** · 08/03/2010, 17h28

Autre solution (en fait, celle que je t'avais proposé au debut, mais correctement ecrite et corrigé)

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
Option Explicit
 
 
Sub sommeprod_tableau()
 
Dim debut As Integer
Dim fin As Integer
Dim continuer As Boolean
 
debut = 2
fin = 0
 
While continuer = False 'debut de la boucle, continue tant que "continuer = Faux"
 
    fin = Application.WorksheetFunction.CountIf(Range("A:A"), Range("A" & debut)) + fin
        'recherche de la fin du tableau avec la fonction NBSI, en se referencant à la 1 ere cellule du tableau en cours.
 
    Range("L" & debut) = Application.WorksheetFunction.SumProduct(Range("J" & debut & ":J" & fin + 1), Range("E" & debut & ":E" & fin + 1)) / Application.WorksheetFunction.SumProduct(Range("K" & debut & ":K" & fin + 1), Range("E" & debut & ":E" & fin + 1))
        ' calcule du sommeprod
 
    If Range("A" & fin + 2) = "" Then continuer = True
        'condition de fin : Si la cellule suivante au dernier tableau analysé est vide, alors continuer = Vrai
 
    debut = fin + 2
        ' nouveau parametres, pour le tableau suivant
 
Wend
 
End Sub

**Qwazerty** · 08/03/2010, 17h35

Salut
Par contre ton code oblige a ne jamais avoir 2 tableaux repérés avec la meme valeur si en colonne A tu te retrouves avec

Code :

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

ton code comptabilisera 8 cellules contenant "2" et le 2ème tableau contiendra donc 8 cellules au lieu de 3 nécessaires, ce qui faussera toute la suite de la procédure mais peut être que les codes en A ne se répètent jamais.

A++
Qwaz

**Galatane** · 08/03/2010, 17h37

Oui, mais n'ayant pas plus de précision, j'ai fais un truc simple. A voir donc si on peut retrouver plusieurs fois la même entrée.

**SAKAROV** · 09/03/2010, 11h39

Messieurs.

J'ai mis 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
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
Sub Sakarov()
 
Dim TheCel As Range
 
Dim FirstCel As Range
 
Dim TheSh As Worksheet
 
Dim LFirstCel As Long, LLastCel As Long
 
Set TheSh = Sheets("Feuil1")
 
With TheSh 
 
    Set FirstCel = .Range("A2")
 
    Do
 
        For Each TheCel In .Range(FirstCel.Offset(1, 0), .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0))
 
            If FirstCel.Value <> TheCel.Value Then
 
                LFirstCel = FirstCel.Row
 
                LLastCel = TheCel.Offset(-1, 0).Row
 
                FirstCel.Offset(0, 11).Value = WorksheetFunction.SumProduct(.Range(.Cells(LFirstCel, "J"), .Cells(LLastCel, "J")), .Range(.Cells(LFirstCel, "E"), .Cells(LLastCel, "E"))) / WorksheetFunction.SumProduct(.Range(.Cells(LFirstCel, "K"), .Cells(LLastCel, "K")), .Range(.Cells(LFirstCel, "E"), .Cells(LLastCel, "E")))
 
                FirstCel.Offset(0, 12).Formula = "=SumProduct(" & .Range(.Cells(LFirstCel, "J"), .Cells(LLastCel, "J")).Address & "," & .Range(.Cells(LFirstCel, "E"), .Cells(LLastCel, "E")).Address & ")/ SumProduct(" & .Range(.Cells(LFirstCel, "K"), .Cells(LLastCel, "K")).Address & "," & .Range(.Cells(LFirstCel, "E"), .Cells(LLastCel, "E")).Address & ")"
 
                Exit For
 
            End If
 
        Next TheCel
 
                Set FirstCel = TheCel
 
    Loop Until FirstCel.Value = ""
 
End With
 
End Sub

Ca me dit ça :

Erreur d'execution '6' Dépassement de capacité

Quand je clic sur "débogage", ça me surligne ça :

Code :

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

FirstCel.Offset(0, 11).Value = WorksheetFunction.SumProduct(.Range(.Cells(LFirstCel, "J"), .Cells(LLastCel, "J")), .Range(.Cells(LFirstCel, "E"), .Cells(LLastCel, "E"))) / WorksheetFunction.SumProduct(.Range(.Cells(LFirstCel, "K"), .Cells(LLastCel, "K")), .Range(.Cells(LFirstCel, "E"), .Cells(LLastCel, "E")))

.... ????

**Galatane** · 09/03/2010, 11h42

Sur ce code, je peux pas t'aider plus. Je ne le comprend pas plus que toi.

As tu essayer le mien pour tester?

**SAKAROV** · 09/03/2010, 11h42

Et idem pour celui de galatane

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
Option Explicit
 
Sub sommeprod_tableau()
 
Dim debut As Integer
Dim fin As Integer
Dim continuer As Boolean
 
debut = 2
fin = 0
 
While continuer = False 'debut de la boucle, continue tant que "continuer = Faux"
 
    fin = Application.WorksheetFunction.CountIf(Range("A:A"), Range("A" & debut)) + fin
        'recherche de la fin du tableau avec la fonction NBSI, en se referencant à la 1 ere cellule du tableau en cours.
 
    Range("L" & debut) = Application.WorksheetFunction.SumProduct(Range("J" & debut & ":J" & fin + 1), Range("E" & debut & ":E" & fin + 1)) / Application.WorksheetFunction.SumProduct(Range("K" & debut & ":K" & fin + 1), Range("E" & debut & ":E" & fin + 1))
        ' calcule du sommeprod
 
    If Range("A" & fin + 2) = "" Then continuer = True
        'condition de fin : Si la cellule suivante au dernier tableau analysé est vide, alors continuer = Vrai
 
    debut = fin + 2
        ' nouveau parametres, pour le tableau suivant
 
Wend
 
End Sub

--------------------------------------------------------------------------------
Dernière modification par Galatane

: dépassement de capacité ...

**Galatane** · 09/03/2010, 11h47

Peux tu mettre un point d'arret en debut de code et avancer pas a pas (avec F8) pour me dire ouca bug? Car chez moi, le code passe bien

Débutant // Problème code VBA

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager