[VBA-E] Amelioration dun code

Version imprimable

13/07/2006, 11h09
Elstak

[VBA-E] Amelioration dun code

Coucou,

voilà j'ai une feuille excel avec dans une colonne un nom et dans l'autre, sur la meme ligne, une valeur.

je dois faire la somme lorsque le nom est le meme.

Exemple

A 10
B 12
C 3
B 9
C 14

je dois avoir dans ma feuille finale :

A 10
B 21
C 17

or un nom peut apparaitre à n'importe quelle ligne.

pour le moment mon code regarde la premiere ligne de ma feuille source et si le nom n'est pas dans ma feuille cible alors il ajout une ligne (dans la feuille cible) avec ce nom et la valeur. Si le nom est deja present il ajoute la valeur à la valeur deja présente.

Le probleme est que comme il y a environ 80 noms différents, lorqu'il prend un nom de la feuille source il peut faire jusque 80 comparaisons... comme j'ai plus de 40000 lignes.... ca prend du temps (4 minutes environ) et le souci est que j'ai à répéter ca une dizaine de fois (par semaine).

Je voulais donc savoir s'il existait une autre methode plus rapide que la mienne :)
13/07/2006, 11h16
illight

Pourquoi ne pas faire une formule ? je crois que le "NB.SI" fait ce que tu demande :)

EDIT : ah nan ça te dit juste le nombre de cellules qui contiennent la meme valeur..mais tu dois pouvoir le concaténer avec une somme ou un truc comme ça..Vais chercher :)
13/07/2006, 11h21
ouskel'n'or

Citation:

Si le nom est deja present il ajoute la valeur à la valeur deja présente.

C'est qui "il" ? :mouarf:
Peux-tu nous montrer le code de tes boucles (car je pense que tu as deux boucles imbriquées...) ;)
13/07/2006, 11h21
laloune

Citation:

je dois faire la somme lorsque le nom est le meme.

illight a raison, même si je pencherais plutot pour un somme.si:

somme.si(plage_qui_contient_les_critères;critère;plage_qui_contient_les_nombres_a_sommer)

Bon dev...
laloune

Ok illight mais mon probleme n'est pas d'avoir un resultat mais d'ameliorer le temps d'execution.

Donc le NB.si peut convenir (j'irai verifier :P ) mais je ne sais pas si ca sera plus rapide.. enfin je testerai pour voir

sinon voici mon code actuel :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
For i = 2 To NombreLignes
    j = 3
        Do While Ceclasseur.Worksheets(Nomfeuille).Cells(j, NombreColonnes) <> ""
            'Si la sclass a deja été rencontrée
            If Source.Worksheets("data").Cells(i, 4) = Ceclasseur.Worksheets(Nomfeuille).Cells(j, NombreColonnes) Then
                'on ajoute la valeur
                Ceclasseur.Worksheets(Nomfeuille).Cells(j, NombreColonnes + 1) _
                = Ceclasseur.Worksheets(Nomfeuille).Cells(j, NombreColonnes + 1) _
                + Source.Worksheets("data").Cells(i, 5)
                GoTo 1:
            Else
                j = j + 1
            End If
        Loop
        'si on arrive ici c'est que le sclass n'a pas été trouvé
        Ceclasseur.Worksheets(Nomfeuille).Cells(j, NombreColonnes) = Source.Worksheets("data").Cells(i, 4)
        Ceclasseur.Worksheets(Nomfeuille).Cells(j, NombreColonnes + 1) = Source.Worksheets("data").Cells(i, 5)
1:
    Next

nombredeligne est le nombre de lignes qu'il y a à comparer dans la feuille source.

nombrecolonnes est calculé juste pour savoir la place où vont etre copiées les valeurs (dans la feuille de Ceclasseur).

13/07/2006, 11h25
illight

je confirme que le NB.SOMME marche très bien :) suffit que dans ta macro tu fasse une formule après tu la tire :)
Dans ton autre feuille, tu peux mettre tous les noms, les trier puis supprimer les doublons, ensuite tu met ta formule et tu la tire (tout ça tu peux le faire en macro ;) )

Et je pense qu'une formule sera toujours plus rapide qu'une macro avec des boucles :)
13/07/2006, 11h26
Elstak

Perso, d'apres mes souvenir des formules, j'utiliserai plutot une recherche verticale non?

si il trouve pas j'ajoute le nom en dessous sinon j'ajoute la valeur au niveau de la ligne qu'il me trouvera. enfin je ne suis toujours pas convaincu que ce sois plus rapide :? je pense qu'il faudrai modifier mon code mais je sais pas trop comment j'ai deja lu que travailler avec des "array" rendait plus rapide mais bon jvois pas trop si jpeux m'en servir :?
13/07/2006, 11h32
Elstak

mon code??

il est au dessus ouvre les yeux :mouarf:
13/07/2006, 11h34
illight

Ousk il l'a mis le code Elstak 8O

Pour la formule c'est ça..
Donc d'abord ce que j'ai fait c'est que j'ai mis tes valeur de "nom" dans la feuille1 dans la première colonne, et les valeurs dans la 2e colonne, et ensuite dans la feuille 2 j'ai mis les 3 noms avec la formule donc adéquate en face :) donc voici la formule :

Code:

=SOMME.SI(Feuil1!$A$1:$B$5;A1;Feuil1!$B:$B)
13/07/2006, 11h47
Elstak

jcomprend pas trop ton somme.si

d'ailleur je trouve rien à propos de ca dans l'aide excel 2003 !! 8O

il faut savoir à l'avance les noms qu'il y aura? car moi je les ignore au debut... ca change d'une semaine à l'autre :?
13/07/2006, 11h50
ouskel'n'or

Ok pour le code, mon post a dû le croiser ou j'ai pas vu :(
Elstak, pourquoi ne pas utiliser Find ? Inconvénient : si le nom peut se rencontrer plusieurs fois...
Tu dis

Mais l'idée de la formule, si ça correspond à ce que tu veux, est bonne. Une formule donne un résultat instantané si tu peux la placer dans ta feuille dans laquelle tu souhaite placer le résultat. Mais pour ce que j'ai compris, la donnée "résultante" doit se trouver à la place de la donnée cherchée... Et là, ça marche pas. Faudrait ajouter une colonne ;)
Tu dis

somme.si est une formule Excel, ce n'est pas une fonction VBA. Tu as la réponse dans mon post précédent ;)
13/07/2006, 11h53
illight

C'est une fonction d'Excel, elle y est pas dans Excel 2003 8O ?

Je te joins le fichier exemple que j'ai fait :)

Par contre je part dans une demi heure, donc si jamais t'a un soucis et que je suis plus la :mrgreen: , je ne pourrait te répondre que lundi, mais je pense que ouskel et les autres pourrons aussi t'aider :)
13/07/2006, 11h56
Elstak

Citation:

Ok pour le code, mon post a dû le croiser ou j'ai pas vu

tututu pas d'excuse... jlai posté 7 minutes avant donc on s'est pas croisé :P

Citation:

Elstak, pourquoi ne pas utiliser Find ? Inconvénient : si le nom peut se rencontrer plusieurs fois...

jamais utilisé le find mais là c'est sur le nom se rencontre plusieur fois ! donc c'est foutu pour ca...

Citation:

Mais pour ce que j'ai compris, la donnée "résultante" doit se trouver à la place de la donnée cherchée... Et là, ça marche pas. Faudrait ajouter une colonne

Euh.. je saisi pas tout là... :roll:

Citation:

somme.si est une formule Excel

Oui j'ai bien cherché dans l'aide excel mais il y a aucun resultat 8O

j'ai tapé somme.si et somme si .... rien :roll:
13/07/2006, 11h57
Elstak

Ok merci illight !

moi je pars dans 2min30 :mouarf:

mais pas de souci ca presse pas, le code fonctionne c'est juste pour gagner du temps ;)
13/07/2006, 11h57
illight

Citation:

Envoyé par Elstak

j'ai tapé somme.si et somme si .... rien :roll:

elle est dans la catégorie "maths et trigo" ;)
13/07/2006, 12h01
Elstak

jlai trouvée !!

dans formules/formules courantes/formules mathematiques/additionner des nombre

ca coule de source :mouarf:

allez je regarde ca cet aprem salut !
13/07/2006, 12h02
ouskel'n'or

Pfff ! Sait même pas chercher dans l'aide

Citation:

Envoyé par A Somme.si, l'aide Excel

Additionne des cellules spécifiées si elles répondent à un critère donné.

Syntaxe

SOMME.SI(plage;critère;somme_plage)

plage représente la plage de cellules sur lesquelles baser la fonction.
critère représente le critère, sous forme de nombre, d'expression ou de texte, définissant les cellules à additionner. Par exemple, l'argument critère peut être exprimé sous une des formes suivantes : « 32 », « >32 », « pommes ».
somme_plage représente les cellules à additionner. Les cellules comprises dans l'argument somme_plage sont additionnées si et seulement si les cellules correspondantes situées dans l'argument plage répondent au critère. Si l'argument somme_plage est omis, ce sont les cellules de l'argument plage qui sont additionnées.

Exemple

Supposons que la plage A1:A4 contienne la valeur immobilière de quatre maisons, soit respectivement, 100 000 F, 200 000 F, 300 000 F et 400 000 F. La plage B1:B4 contient les commissions sur ventes suivantes correspondant à ces valeurs immobilières : 7 000 F, 14 000 F, 21 000 F et 28 000 F.
SOMME.SI(A1:A4;">160000";B1:B4) égale 63 000 F

Mais pour ça, il faut aller dans Recherche, taper Somme.si et sélectionner Somme.si :mouarf2:
13/07/2006, 12h05
ouskel'n'or

Bon, tu dis si ça te convient, sinon, on verra avec Find et FindNext. Faut bien que tu apprennes ;)
13/07/2006, 13h19
Elstak

oui j'ai bien lu l'aide.

mais je ne connais pas les noms moi !

il faudrai avant que je puisse trouver les différents noms...

jveux bien voir find et findnext :D

jvais deja chercher un peu pour voir ce que c'est.... :P
13/07/2006, 13h44
Elstak

Je pense avoir trouvé une piste

avec un SORT, je trie la colonne des noms de ma feuille source mais apres... je sais pas trop encore faudrait que j'initialise le premier nom puis que je fasse un while (je ne suis pas à la derniere ligne de mon fichier source) si le nom est le meme alors j'ajoute le nombre a la colonne a coté (sur ma feuille destination), sinon je descennd d'une ligne (tjr sur la destination) et ainsi de suite..

je sais pas si je suis clair mais c'est assez clair dans ma tete :D

Tiens, comme je sais que tu as des problèmes de compréhension, je t'ai mâcher le travail :mouarf:

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
33
34
35
36
37
Sub RechercherEnBoucle()
    'Tu crées le tableau des valeurs que tu vas chercher
Dim LeTableauDesDonnéesAchercher()
    'Pour chaque valeur à tester, tu incrémentes un compteur
'compteur = compteur + 1
    'Tu redimensionnes le tableau
'Redim Preserve LeTableauDesDonnéesAchercher(compteur)
    'et tu lui affectes la valeur
LeTableauDesDonnéesAchercher(Compteur) = LaValeur
    'Quand tu as toutes tes valeurs tu détermines la plage de la recherche
'No1èreLigne = ...
'NoDernièreLigne = ... 'je pense que tu as la syntaxe pour la retrouver
'NoColonne = ...
    For i = 1 To Compteur
        plage = Range(Cells(No1èreLigne, NoColonne), Cells(NoDernièreLigne, NoColonne)).Address
        'Au départ, la plage couvre toutes les données de la colonne, ensuite, elle s'étendra
        'de la ligne de la dernière occurence trouvée à NoDernièreLigne
        With Worksheets(LaFeuille).Range(plage)
            'Je cherche la première occurence AVANT de rentrer dans la boucle de recherche des données suivantes
            Set C = .Find(LeTableauDesDonnéesAchercher(i), LookIn:=xlValues) 'là tu peux préciser valeur entière ou non
            If Not C Is Nothing Then 'Si j'ai trouvé une donnée, j'entre dans la boucle
                Do
                    Adresse = C.Address
                    'là, fais tes petites affaires avant de chercher l'occurence suivante (addition, pas addition...
                    NLig = C.Row + 1    'On note le No de ligne de la donnée trouvée
                                        '... pour déterminer la plage de recherche suivante
                    plage = Range(Cells(NLig, NoColonne), Cells(NoDernièreLigne, NoColonne)).Address
                    Set C = .FindNext(C)
                Loop While Not C Is Nothing And C.Address <> Adresse
                'je sors de la boucle si je ne trouve plus rien ou...
                '... si l'occurence trouvée est sur la dernière ligne de la plage...
                '... (la plage ne couvrant alors qu'une ligne)
            End If
        End With
        Set C = Nothing
    Next i
End Sub

Pour ce qui est de la ligne

Citation:

'là, fais tes petites affaires avant de chercher l'occurence suivante (addition, pas addition...

je te laisse faire ce que tu as à faire ;)

PS - Je n'ai pas testé la procédure telle quelle mais si tu as un pb... tu connais l'adresse...

13/07/2006, 14h28
ouskel'n'or

Désolé mais il semblerait que j'ai laissé une erreur :oops:
Tu ne dois pas faire

Citation:

NLig = C.Row + 1 'On note le No de ligne de la donnée trouvée

mais

Code:

NLig = C.Row 'On note le No de ligne de la donnée trouvée

En effet, comme on utilise findnext, il cherche la donnée en partant de la ligne suivante... Mais comme je t'ai dit, je n'ai pas testé... Mais comme je te connais, tu vas faire ça pour moi ;)
13/07/2006, 14h37
Elstak

tinquietes pas je regarderais mais là je pars sur autre chose donc t'etonne pas de ne pas avoir de reponse tout de suite :P
17/07/2006, 08h00
illight

Citation:

Envoyé par Elstak

Je pense avoir trouvé une piste

avec un SORT, je trie la colonne des noms de ma feuille source mais apres... je sais pas trop encore faudrait que j'initialise le premier nom puis que je fasse un while (je ne suis pas à la derniere ligne de mon fichier source) si le nom est le meme alors j'ajoute le nombre a la colonne a coté (sur ma feuille destination), sinon je descennd d'une ligne (tjr sur la destination) et ainsi de suite..

je sais pas si je suis clair mais c'est assez clair dans ma tete :D

Rien compris :mrgreen:
17/07/2006, 08h33
Elstak

haha ! :lol:

je fait un sort donc tous les noms sont rangés. A partir du moment où je tombe sur un autre nom je sais que je reverai plus celui d'avant donc j'ajoute le nouveau nom dans mon fichier destination avec sa valeur puis tant que je trouve le meme nom j'ajoute...

ca evite de rechercher à chaque fois à quellle ligne je dois ajouter au niveau de mon fichier destination.

exemple :

A 12
B 13
A 13

va devenir

A 12
A 13
B 13

au lieu de faire (dans le fichier destination)

A 12 (on copie)
on prend la ligne suivante du fichier source (B 13)
on compare avec le A, on voit que ce n'est pas le meme donc on ajoute une ligne et on met B 13 (on gagne cette comparaison avec un "sort") et crois moi quand t'a 50 lignes à comparer à chaque fois que tu veux ajouter une valeur bah tu gagne du temps à ne pas les faire ! (50 lignes maximum, ca depend à quelle ligne se trouve le nom dans ton fichier destination apres...)

bref je passe de 3min20 à 12 secondes (pas mal hein? :mouarf: )

donc mon ptit dodo ne m'en veux pas si je prend pas ta solution mais j'ai compris pour le "find" et findnext" avec ton code ;)
17/07/2006, 09h28
ouskel'n'or

N'empêche, si tu as un gros fichier, "find" et "findnext" sont plus rapides qu'escalader chaque ligne l'une après l'autre... ;)
17/07/2006, 09h59
Elstak

oui jveux bien te croire mais là j'ai plus besoin d'escalader avec le "sort"...

tout est rangé, j'ai juste à parcourir chaque ligne du fichier source et je sais deja où copier les valeurs.... :D
06/06/2007, 13h05
it-worker

Bonjour,

Je lisais tranquillement quand il m'est venu une idée ... Est-ce que autofilter ne serait intéressant à utilisé ici? Pour chaque nom de la colonne A, tu fais un autofilter et ensuite tu peux récupérer le total aisément.

non?
06/06/2007, 13h51
ouskel'n'or

Si ! Mais l'est pas sûr du tout qu'Elstak y connaisse =Somme(gnagnagna) :mouarf:

Salut Elstak, tu es dans ton école ? Je n'avais pas tilté. Toujours dans VBA ?
A+