Besoin d aide pour une macro excel / VBA

[Greg]

Bonjour,

je suis +/- debutant en excel VBA et j aimerai faire une petite macro qui m aiderais dans mon travail quotidien.

En gros, pour me permettre d avancer par moi même, j ai juste besoin d 'extraire les infos de la page datas pour obtenir les mêmes rsultats que ceux en rouge dans la page "Results" (resultats que je calculent manuellement pour l instant) du fichier excel que vous pourrez trouver sur à l adresse suivante : http://www.si-la-gi.com/TestMacroGreg.xls :

- Amount (somme des montants par même classe et même value date)
- Class
- Value date

Le probleme est que je ne sais pas vraiment comment faire ca en vba...

Une bonne âme pourrait il m aider sur ce projet ?

Merci d avance,

Gregory

 

[Rénato]

Bonjour GREG,

Dans le cas présent, je ne pense pas que le VBA soit "passage obligé".

Je t'invite à coller dans la cellule E3 de ton onglet "résults" ceci :

=SOMMEPROD((Datas!$G$2:$G$10)*(Datas!$A$2:$A$10=Results!A3)*(Datas!$I$2:$I$10=Results!I3))

puis de recopier vers le bas cette petite formule.


Bien cordialement

Rénato

P.S. : ne pas oublier de faire bruler un cierge à Sainte Monique, grande prétresse du SOMMEPROD.

 

[Gérard DEZAMIS]

Bonsoir Greg et Renato

Je suis resté sur le pas de la porte du Temple "Sommeprod"
Un peu simpliste, je me suis contenté de lier les données, de les classer et aussi de faire joujou avec le dernier hochet que vient de m'offrir Monique cet après-midi (Formule nommée)

Je ne sais pas si cela conviendra totalement mais je me suis bien occupé alors ...
@+
GD

 

[Greg]

Merci a vous deux pour vos reponse !

En ce qui me concerne j ai passé mon apres midi et ma soirée sur mon projet et j ai reussi a obtenir le reultat que je cherchais a l aide d un tableau croisé dynamique puis extraction de ses resultatsen vba dans la fueille results. C est une grosse usine a gaz, mais ca fonctionne...

Gerard, j ai regardé ton fichier. Tres interessant, tu obtiens presque le même resultat que moi sauf qu il manque qqchose de necessaire dans la feuille results :la somme des montants de même classe et de même Value Date.

C est ce que fait en fait la SUPER formule de Renato.

Renato, pourrais tu m expliquer cette formule s il te plait dans le cas présent ?
Comment puis-je l'adapter pour inputer la classe et la value date de chaque total dans la feuille results ?

En tout cas un grand merci a tous les deux.
A+

Greg
ps : vive Sainte-Monique ;-)

 

[Rénato]

Bonjour GREG, Salut Gérard

Cette formule te contraint tout de même à renseigner tes nouvelles lignes dans l'onglet Datas dès qu'une "value date" ou une "class"diffèrent de celles préexistantes.

Pour l'adapter à ton besoin, voici une première solution avec un paramétrage fixe, ainsi ta formule fonctionnera tant que tu n'auras pas dépassé les 5000 lignes (libre à toi d'en rajouter si nécessaire) renseignées dans ton onglet Datas :

=SOMMEPROD((Datas!$G$2:$G$5000)*(Datas!$A$2:$A$5000=Results!A3)*(Datas!$I$2:$I$5000=Results!I3))

On pourrait faire plus élégant avec un petit script qui à chaque ouverture du classeur renomme les plages utilisées.....

Pour ce qui concerne l'explication c'est très simple : pour chaque ligne dans l'onglet Data vérifiant les mêmes "value date " (Datas!$A$2:$A$5000=Results!A3) et les mêmes "class" (Datas!$I$2:$I$5000=Results!I3) les valeurs contenues en colonne G de l'onglet "Datas" (Datas!$G$2:$G$5000) sont additionnées.

Si tu recherches d'autres exemples sur cette fonction, regarde la réalisation de "Super Monique" à ce sujet dans la rubrique "téléchargement" tu vas être soufflé.

Bon Dimanche à tous.

 

[Monique]

Bonjour,

Stop ! J'ai les chevilles qui me font mal.
Une précision, on était 2, Celeda et moi, à faire le classeur "SommeProd"
Seule, je n'y serais pas arrivée. (je n'aurais même pas essayé)
Il n'y a plus qu'une cheville à me faire mal, ça va nettement mieux.

Greg, dans la feuille "Datas", tu as une colonne donnant un n° aux lignes à reporter.
(encore du SommeProd)
Et la feuille "Results" se remplit de façon automatique.
(en feuille Results, tu as 2 méthodes au choix)

 

[Rénato]

Re bonjour,

Toutes mes excuses à ma "toulousaine préférée" sur ce coup là !!!
Chapeau pour ton humilité Monique.

a+

Rénato

 

[@+Thierry]

Bonjour Monique, Rénato, Gérard, Greg, le Forum

Le Sujet m'ayant attiré ici ! finalement je vois que, comme toujours, Monique s'en sort royalement et en plus en toute modestie (comme toujours aussi !)

Ceci dit, je fais ce genre de truc assez souvent pour des besoins du bureau, alors je vous propose une solution 100% VBA qui offre l'avantage, une fois mise en place sur un tableau clairement défini et structuré de ne plus se soucier de rien. La feuille "Results" étant reconstruite systématiquement à la volée à chaque lancement de la procédure, quelque soit le nombre de ligne à scanner dans la feuille Data ainsi que quelque soit les Class, Class CCY, Base CCY...

Je vous laisse toutefois vérifier l'intégrité de ce programme (si quelque chose m'a échappé). Par contre je rassure Greg qui, donc a bien fait de préciser qu'il était novice en VBA, sur ce genre de programme il n'y a aucune maintenance à prévoir pour autant que l'exemple que tu as mis en ligne représente bien la vraie structure de ton WorkBook.

Pour le coté Technique, voici encore une fois en démo l'usage de New Collection qui simplifie grandement la vie du développeur pour générer des Clefs uniques pour reconstruire un Tableau Indéxé Séquentiellement avec les Sommes de sur ces valeurs uniques tout en restant dans des délais raisonnables d'exécution même sur de grandes bases de données. (le plus long étant d'écrire les résulats sur la feuille !! mais faut bien lol)

Bon Dimanche à Tous et Toutes !
@+Thierry

 

[Greg]

Bonjour a tous,

Whaouw, c est dans des moments comme ça qu on se dit qu internet est vraiment utile et qu il y a quand même bcp de gens serviables.
Ca fait plaisir a voir !
Un grand merci pour ces explications Monique, Renato et Gerard.

Thierry, je viens de regarder ton fichier et ton code --> Whaouw, ca force le respect. Je reve de pouvoir un jour ecrire un code comme ca. Etant donné que je ne le comprend pas vraiment, ce serait super si tyu pouvais me l expliquer en detail afin que je sois capable de l adapter, d'en recrer un autre,... ?

Je joins ici ma version a moi. J ai mis des petits ' devant les lignes de code d importation des donnes de tris,... afin que vous n ayez pas de message d erreur.

Comme vous pouvez le voir c est une usine a gaz très mal codée, mais bon ça fonctionne. J ai egalement ajouté une 2eme page results reprenant les deals globaux independant des classes. Par exemple un SELL USD 1000 VS BUY EUR et un BUY EUR 400 VS SELL USD ne font plus qu un seul deal SELL 600 USD VS EUR.

Voili, voilou, encore un grand merci a tous,

Gregory

 

[@+Thierry]

Bonjour Greg, re Monique, Rénato, Gérard

Je veux bien t'expliquer en détail, mais je ne pense pas que tu analyses d'un trait un truc pareil. Non pas que je ne dévoile pas mes secrets (sinon je n'aurais pas fait autant de démos en "full coments détails" ici) mais ce que j'ai peur c'est que ce soit un peu trop pour être assimiller d'un coup par quelqu'un qui ne connait pas les bases du VBA.


Juste pour ta compréhension de quelques trucs :


Construction de ColData Clefs Uniques (Objet New Collection)
ColData.Add TabData(i, 1) & "#" & TabData(i, 3) & "#" & TabData(i, 10) & "#" & CLng(TabData(i, 9)) & "#", _

Ceci signifie que je construis des Clefs Uniques avec la Concaténation de différents Champs de la Feuille "Data" :
Class (col 1) / Base CCY (col 3) / Class CCY (Col 10) / Value Date (Col 9)



Scan Comparitif des Données (Deux Boucles Imbriquées)
For Each Item In ColData
For i = 1 To UBound(TabData)
If Item = TabData(i, 1) & "#" & TabData(i, 3) & "#" & TabData(i, 10) & "#" & CLng(TabData(i, 9)) & "#" Then
Balance = Balance + TabData(i, 7)

Ceci signifie que je fais un comparatif pour chaque Item (Item) dans (ColData) avec chaque Concaténation Similaire de chaque ligne (i) de la Feuille "Data" qui ont été envoyées dans un Tableau Variant (TabData).
Cette Concaténation est la même qu'avant...
Class (col 1) / Base CCY (col 3) / Class CCY (Col 10) / Value Date (Col 9)

Si le résultat est Positif "If Item = Concaténation de TabData sur la Ligne i" alors je récupère la somme des montants de cette manière:
Balance = Balance + Amount (Col 7)



Construction d'une Dynamique Array (TabResult) (Tableau Indéxé Séquentiellement)
ReDim Preserve TabResult(9, X)
TabResult(0, X) = TabData(i, 1)
TabResult(1, X) = TabData(i, 10)
TabResult(2, X) = TabData(i, 3)
TabResult(3, X) = TabData(i, 6)
TabResult(4, X) = Balance
TabResult(5, X) = TabData(i, 10)
TabResult(6, X) = "vs"
TabResult(7, X) = TabData(i, 3)
TabResult(8, X) = TabData(i, 9)
End If
Ceci signifie que tant que l'on est dans la Boucle i (toutes les Lignes de "Datas") et si la condition matche, alors j'incrémente les données dans ce Tableau.



Passage à la seconde Boucle Imbriquée (Boucle For Each))
Next i
X = X + 1
Balance = 0
Next Item

Ceci signifie que l'on a finit le Scan des ligne (i) et donc j'incrémente (X) et je remets la somme à zéro (Balance) pour passer au prochain Item (Item)...

===> Question à ce stade .... (hihihihi) pour voir si tu arraives à suivre !!!!
Que représente un Item ?
A) les Données Concaténées d'une des Lignes de "Datas" (i)
B) la Ligne Suivante des Données Concaténées de Data
D) le prochain résultat du Keno
C) une des Clefs Uniques des enregistrements concaténés de "Datas"

????? Top Chrono










5










4










3










2










1










0.... Fini !!

Si tu a répondu autre chose que D, alors je te conseille de me relire attentivement !!! lol


Car maintenant on passe au Scan du second Item (Item) qui est donc la seconde Clef Unique trouvée dans l'Objet New Collection (ColData) et on va lire les lignes (i) une a une (de nouveau) avec
For i = 1 To UBound(TabData)
et construire une nouvelle ligne dans (TabResult) avec un numéro de Ligne X (incrémenté juste avant... si la condition IF est remplie....

Et ainsi de suite jusqu'à ce qu'il n'y ait plus d'Item... OK ?



Report de donnée en Feuille "VBA-Results" (Boucle For / Next)
With Worksheets("VBA-Results")
For L = LBound(TabResult, 2) To UBound(TabResult, 2)

Ceci signifie que je "lis" ma Dynamic Array en mesurant sa taille avec une Boucle qui va connaitre le plus petit numéro de Ligne (LBound) au plus grand (Ubound)...

Ensuite avec ceci :
.Range("A" & L + 3) = TabResult(0, L)

Je dis simplement que la Cellule "A" & L (L est le numéro de la ligne scannée par ma boucle) + 3 car il y a un décalage entre un Tableau qui démarre à Zéro et la Feuille qui démarre à la Cellule A3.... donc je dis que cette Cellule(Ligne / Colonne) = mon Tableau(Colonne / Ligne) (attention il est inversé)

Et ainsi de suite...

Voilà pour une première approche... c'est assez hardu à comprendre mais j'ai essayé d'être le plus simpliste possible dans mes explication car tu sembles sympathique.

Je vais maintenant regarder ton nouveau fichier mais de là à te dire qu'avec ces expliaction tu seras capable de recréer une procédure identique, tu risque de passer pas mal de nuits blanches, mais si il y a une qualité qui prime en VBA c'est la Patience et la Persévérance...

Bon Courage et Welcome in the VBA's World
@+Thierry

Ah Oui aussi j'oubliais ...

 

[@+Thierry]

Re Bonjour Greg, Monique, Rénato, Gérard, le Forum

Je viens de regarder ton dernier fichier Greg...

Attention, les données Sources & Cibles doivent être sur des Tableaux Structurés

Comme je le précisais dans mon premier Post "pour autant que l'exemple que tu as mis en ligne représente bien la vraie structure de ton WorkBook", tu dois avant toute chose définitir définitivement la structure de tes Tableaux, hors ce que je vois en Feuille "Results" est déjà différent de ton premier fichier (colonnes cachées et emplacement différents).... Donc là il faut quand même faire attention, VBA ne te fera pas de cadeau à ce niveau là...

Bon Courage
@+Thierry

 

[Greg]

Re salut Thierry,

En effet, j'ai un petit peu modifié la structure de ma feuille results pour pouvoir la remplir comme je le souhaitais à partir des resultats des tableaux croisés dynamique.
Le but final etant d'obtenir une page avec les deals par classe et par value date (feuille Results) et les deals globaux (Feuille resultsSummary)
Bref en cliquant sur le bouton "launch" de l'onglt menu, il me sort bien les resultats recherchés même si il est vrai, de manière bien moins elegante que la tienne ou celle de Monique ou Renato ;-)

D'ailleur merci bcp pour tes explications. Je vais analyser ca de plus pret mais c'est vrai que ca depasse de loin mes connaissancent actuelles...

A+

Gregory

 

[FARES]

Re: AIDE pour une macro excel / VBA qui IMPORTE les données d'un fichier texte

MERCI D'AVANCE DE M'AIDER POUR CREE UNE MACRO QUI importe des données d'un fichier texte..

 

[Gérard DEZAMIS]

Re: AIDE pour une macro excel / VBA qui IMPORTE les données d'un fichier texte

Bonsoir Fares

Je ne suis pas assez balèze pour utiliser correctement VBA mais en macro à mettre dans un bouton cela donnerait un truc dans le genre :

Sub import()
Workbooks.OpenText Filename:="C:\Mes Documents\essai.txt", Origin:= xlWindows, StartRow:=1, DataType:=xlFixedWidth
End Sub

Il faudra ensuite voir comment sont ordonnées tes données Texte, separateur, ou espace ou tabulation ou "n'importe quoi"
Un exemple serait le bien venu.
Par la même occasion essaye de jeter un coup d'oeil sur la Charte
* Changement de sujet , squatter le fil des autres, poser une question claire, dire bonjour .... la liste est longue ! Aussi longue que celle de tes données à importer je présume.
@+
GD

 

[Greg]

Re bjr tout le monde,

Je profite de mes quelques jours de congé pour ameliorer mes connaissances en excell et en vba et ce we j'ai décidé de me consacrer a la comprehension de la version de mon projet par Monique.

Dans sa version (le fichier est attaché a son post ici plus haut) est fait sans vba, en utilsant les formules excel et franchement c'est epoustouflant (tout comme la version vba de thierry d'ailleur).

Monique, pourrais tu s'il te plait, quand tu auras un peu de temps "traduire" en francais les formules que tu as integrée dans ta version de mon projet afin de m'aider dans leur compréhension ? J'ai notamment un peu de mal avec les MAX, INDEX et EQUIV.

Voilà, encore un grand merci
A+

Gregory


=SI(NB(A$10:A10)>=MAX(No);"";INDEX(Class;EQUIV(LIGNES(A$11:A11);No;0)))

=SI($A11="";"";INDEX(ClassCcy;EQUIV(LIGNES(B$11:B11);No;0)))

=SI($A11="";"";INDEX(BaseCcy;EQUIV(LIGNES(C$11:C11);No;0)))


=SI($A11="";"";INDEX(Bs;EQUIV(LIGNES(D$1111);No;0)))


=SI($A11="";"";SOMMEPROD((Class=A11)*(Vd=I11)*Amount))

=SI($A11="";"";INDEX(Vd;EQUIV(LIGNES(I$11:I11);No;0)))