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 ?

**Galatane** · 08/03/2010, 15h47

C'est a cela que sert le Nb.si (sur excel).

Ca compte le nombre de cellule répondant au critère :

Code :

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

Nb.si ( "A:A"; "A" & lignea)

Plage : colonne A
Critère : A2 (pour le 1 er cycle)
Et la boucle tourne jusqu'à trouver une cellule vide

**SAKAROV** · 08/03/2010, 16h33

Tout à fait.

Mais du coup, que manque-t-il à ton code ???

**Galatane** · 08/03/2010, 16h36

La bonne façon d'écrire le nb.si en langage vba.

en effet, la formule s'appelle Dcount, mais je n'arrive pas à l'utiliser.

**SAKAROV** · 08/03/2010, 16h41

mais ça me dit "erreur de syntaxe"

là :

Code :

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

ligneb = Nb.si ( "A:A"; "A" & lignea) + ligneb + 2

Ah ok ; excuse moi, j'avais pas vu la réponse.

Bon bon bon ...

**Qwazerty** · 08/03/2010, 16h47

Salut

Un petit truc, lorsque vous voulez utiliser une fonction interne d'Excel dans du code vba, il faut passer par l'objet worksheetfunction suivi du nom de la fonction en anglais

Sinon pour ton probleme essai 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
Sub Sakarov()
Dim TheCel As Range
Dim FirstCel As Range
Dim TheSh As Worksheet
Dim LFirstCel As Long, LLastCel As Long
 
'Initialisation de variables
Set TheSh = Sheets("Feuil1")
 
With TheSh 'permet de ne pas avoir a répeter TheSh a chaque fois, si vba rencontre un . il rajouetra TheSh devant
    'On marque la cellule A2 comme etant la 1ere de la serie
    Set FirstCel = .Range("A2")
 
    Do
        'On boucle de la cellule FirstCel+1ligne jusqu'a la derniere cellule non vide du tableau (+1 ligne qui servira a finir la boucle Do car elle sera vide)
        For Each TheCel In .Range(FirstCel.Offset(1, 0), .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0))
            'On regarde si le contenu des 2 cellules est différent
            If FirstCel.Value <> TheCel.Value Then
                'TheCel represente une cellule contenant une valeur differente
                'On memorise les lignes de la 1ere et derniere ligne
                LFirstCel = FirstCel.Row
                'On utilise donc la ligne superieur (offset(-1,...)) qui correspond a al derniere ligne contenant la mm valeur que FirstCel
                LLastCel = TheCel.Offset(-1, 0).Row
                'Et on met la somme dans la colonne L (offset(-1,11))
                TheCel.Offset(-1, 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")))
                'On quitte cette boucle
                Exit For
            End If
        'Si les 2 cellules sont identiques, on continue la boucle
        Next TheCel
 
        'Ensuite Fisrtcel devent donc La cellule pointé pas TheCel
        Set FirstCel = TheCel
    'On retourne a Do jusqu'a ce que FirstCel soit vide
    Loop Until FirstCel.Value = ""
 
End With
 
End Sub

A++
Qwaz

**Galatane** · 08/03/2010, 16h54

Je l'avais bien vu ce truc la, mais il me manquait une info importante : les fonctions en anglais

**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

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

Je vient de relire les posts précédents.
J'ai mis directement la valeur issue du calcul dans la cellule de la colonne L, je n'ai pas mis la formule dans la cellule.
Si vous voulez placer un formule dans une cellule, il faut utiliser la propriété Formula de la cellule (+nom de fonction en anglais et des "," a la place des ";"), voila le code modifié pour faire cela en colonne M

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
Sub Sakarov()
Dim TheCel As Range
Dim FirstCel As Range
Dim TheSh As Worksheet
Dim LFirstCel As Long, LLastCel As Long
 
'Initialisation de variables
Set TheSh = Sheets("Feuil1")
 
With TheSh 'permet de ne pas avoir a répeter TheSh a chaque fois, si vba rencontre un . il rajouetra TheSh devant
    'On marque la cellule A2 comme etant la 1ere de la serie
    Set FirstCel = .Range("A2")
 
    Do
        'On boucle de la cellule FirstCel+1ligne jusqu'a la derniere cellule non vide du tableau (+1 ligne qui servira a finir la boucle Do car elle sera vide)
        For Each TheCel In .Range(FirstCel.Offset(1, 0), .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0))
            'On regarde si le contenu des 2 cellules est différent
            If FirstCel.Value <> TheCel.Value Then
                'TheCel represente une cellule contenant une valeur differente
                'On memorise les lignes de la 1ere et derniere ligne
                LFirstCel = FirstCel.Row
                'On utilise donc la ligne superieur (offset(-1,...)) qui correspond a al derniere ligne contenant la mm valeur que FirstCel
                LLastCel = TheCel.Offset(-1, 0).Row
                'Et on met la somme dans la colonne L (offset(-1,11))
                TheCel.Offset(-1, 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")))
                'Placer une formule au lieu de lla valeur (en colone M ici (offset(..,12)))
                TheCel.Offset(-1, 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 & ")"
                'On quitte cette boucle
                Exit For
            End If
        'Si les 2 cellules sont identiques, on continue la boucle
        Next TheCel
 
        'Ensuite Fisrtcel devent donc La cellule pointé pas TheCel
        Set FirstCel = TheCel
    'On retourne a Do jusqu'a ce que FirstCel soit vide
    Loop Until FirstCel.Value = ""
 
End With
 
End Sub

Petit truc, si vous voulez faire apparaitre des textes entre guillemet (exemple "a") dans la formule , il faudra les doubler (""a"").

a++
Qwaz

**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

**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** · 08/03/2010, 20h42

Messieurs, bonsoir.

Tout d'abord, je tiens à vous remercier pour vous être penchés sur mon problème.

Je vais essayer ces codes demain matin et vous en dire des nouvelles, vers 10h.

Si il y a un souci, j'écrirai un nouveau post.

Apparemment ça a l'air correct (enfin, le code de Qzerty... je comprend pas tout ! L'autre, +) ; j'essaierai les DEUX CODES dans tous les cas, pour vous dire.

Par contre, deux choses :

La première, qui s'adresse a Qzerty, comment ça la valeur arrive en colonne M ? dans le code d'avant ça allais en L c'étais ce que je voulais... un truc à dû m'échapper.

Je re-précise, que le résultat doit s'afficher dans la première cellule du tableau, en colonne L. C'est-à-dire que si mes cellules identiques en colonne A vont de A378 à A429 et qu'ainsi mon "tableau" sur lequel doit s'effectuer ma fonction SOMMEPROD sera : A378:K429 ET le résultat sera placé en cellule ..... L378.

Pour info, je répète aussi que mes cellules en colonne A se répètent plusieurs fois, par exemple :

A278, A279, A280, A281 ... A307, A308. et à A309 par exemple ça change, et donc mon tableau du dessus commencera en A278 et finira en A308 ce qui veut dire A278:K308.

il est vrai que sans le fichier sous les yeux c'est pas forcément forcément très évident :p

Là je crois pas pouvoir faire plus clair :p

Mais les codes m'ont l'air bien, enfin je pense, je suis loin d'être un confirmé, comme vous aurez pu le constater !

Moi, avec mon niveau, j'aurai pu le faire mais si les "tableaux" faisaient TOUS LA MEME TAILLE ! Là... j'en savais strictement rien.

J'essaie vos codes demain. Je vous dis les réponses vers 10h donc.

Merci à vous

(j'espère seulement avoir été clair dans mon énoncé initial ! )

Débutant // Problème code VBA

Macros et VBA Excel

Discussions similaires

Partager

Partager