Sujet Précédent Sujet Suivant

Echec suppression ligne sous condition de la premiere cellule

Résolu
didyvine Messages postés 5 Date d'inscription jeudi 9 juillet 2009 Statut Membre Dernière intervention 3 août 2009 - 9 juil. 2009 à 23:08
didyvine Messages postés 5 Date d'inscription jeudi 9 juillet 2009 Statut Membre Dernière intervention 3 août 2009 - 12 juil. 2009 à 14:25
Bonjour à tous,

Premiere macro premieres galeres, je n'en peux plus. C'est pour un
stage super important pour moi et je ne brille pas! Pourriez vous
m'aider?

apres quelques manip, peu efficientes j'ai copié collé dans la colonne
A toutes une série de données, et je souhaite supprimer la ligne
entière qd certaine conditions sont respectées. Extrait:

i = 2

Do Until Feuil2.Cells(i, 1) = ""

If Feuil2.Cells(i, 1) = "SUR / sous pondération" Then Feuil2.Cells(i, 1).EntireRow.Delete

If Feuil2.Cells(i, 1).Value = "ISHARES DJ STOXX 600 BKS DE" Then Feuil2.Cells(i, 1).EntireRow.Delete

If Feuil2.Cells(i, 1).Value = "Publicité & médias" Then Feuil2.Cells(i, 1).EntireRow.Delete

If Feuil2.Cells(i, 1).Value = "ISHARES DJ ST 600 INSURAN DE" Then Feuil2.Cells(i, 1).EntireRow.Delete

If Feuil2.Cells(i, 1).Value = "" Then Feuil2.Cells(i, 1).EntireRow.Delete

i = i + 1

Loop

A vient forcément de ma boucle car la premiere fois que je l active çà
enleve certaines lignes, mais pas toutes puis si je recommence
plusieurs fois toutes finissent par disparaitre, elles sont donc bien
dans ma base de données.

Novice que je suis je me suis demander si ca ne venait de l
accumulation de conditions, car si je les inverse, par exemple
publicité et média en premier if, c'est alors cette valeur qui n'est
pas effacée totalement.

Je ne comprend vraiment pas.

Merci d'avance.

6 réponses

Réponse 1 / 6
pile_poil Messages postés 682 Date d'inscription vendredi 6 avril 2007 Statut Membre Dernière intervention 4 août 2012 6
10 juil. 2009 à 00:11
normal !
quand tu supprime la deuxieme ligne ( c'est un exemple) de ton tableau la ligne qui avait le numéro 3 prend le numero 2
comme toi tu ajoutes 1 à ton compteur tu vas donc tester la ligne 3 qui était, avant la suppression, la ligne 4
deux solutions à cela
1) quand tu efface une ligne tu décrémentes en meme temps de 1 ton compteur
ou
2) tu commences ta macro de suppression de lignes par la fin et tu remontes

en application de la solution 1 ton code deviendrait

Do Until Feuil2.Cells(i, 1) = ""
If Feuil2.Cells(i, 1) = "SUR / sous pondération"  _ 
or Feuil2.Cells(i, 1).Value = "ISHARES DJ STOXX 600 BKS DE"  _
or Feuil2.Cells(i, 1).Value = "Publicité & médias"  _
or Feuil2.Cells(i, 1).Value = "ISHARES DJ ST 600 INSURAN DE"  Then
          Feuil2.Cells(i, 1).EntireRow.Delete
          i = i-1
End if
i = i + 1
Loop

avec la solution 2 il deviendrait

for  i = Range("A65536").End(xlUp).Row to 2 step-1
    If Feuil2.Cells(i, 1) = "SUR / sous pondération" _ 

    or Feuil2.Cells(i, 1).Value = "ISHARES DJ STOXX 600 BKS DE"  _

    or Feuil2.Cells(i, 1).Value = "Publicité & médias" _

    or Feuil2.Cells(i, 1).Value = "ISHARES DJ ST 600 INSURAN DE"  _
    or Feuil2.Cells(i, 1).Value = "" Then Feuil2.Cells(i, 1).EntireRow.Delete
next  i

[reglement.aspx ]si c'est la solution, penser : REPONSE ACCEPTEE
3
Réponse 2 / 6
pile_poil Messages postés 682 Date d'inscription vendredi 6 avril 2007 Statut Membre Dernière intervention 4 août 2012 6
10 juil. 2009 à 01:08
si j'ai bien compris ton probleme
tu fais toute cette procédure pour la feuille 2 puis pour la 3  et ainsi de suite
ecrit tout ce code dans une procédure comme suit

sub traitement( FeuilleActive as Worksheets)
    'ici tu mets ton traitement en remplaçant feuil2 par FeuilleActive partout ou tu l'as actuellement
End sub

ensuite dans l'évenement click de ton bouton tu mets
traitement Sheets("Feuil2")
traitement Sheets("Feuil3")
etc ....

par ailleurs
plutot que
    ActiveWindow.ScrollColumn = 41
    ActiveWindow.ScrollColumn = 40
    ActiveWindow.ScrollColumn = 39
    ActiveWindow.ScrollColumn = 38
    ActiveWindow.ScrollColumn = 37
    ActiveWindow.ScrollColumn = 36
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 30
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 27
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1

pourquoi ne fais tu pas
for i = 41 to 1 step-1
    ActiveWindow.ScrollColumn = i
next i

de meme pour la série suivante tu peux faire
for i = 1to 41

    ActiveWindow.ScrollColumn = i

next i

je n'ai pas tout détaillé car il se fait tard et je vais me mettre au dodo
en espérant que ces quelques lignes t'auront aidé
[reglement.aspx ]si c'est la solution, penser : REPONSE ACCEPTEE
3
Réponse 3 / 6
pile_poil Messages postés 682 Date d'inscription vendredi 6 avril 2007 Statut Membre Dernière intervention 4 août 2012 6
10 juil. 2009 à 01:19
un dernier petit truc
evite les ".select"
ça rend le code imbuvable à la lecture
exemple:

Sheets("FEUIL2").Select
Range("AH2:AV2").Select
Selection.Copy
Range("AH2:AV2").Select 'cette ligne me semble être un loupé dans le copier/coller
Sheets("Feuil3").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

se remplace par :

Sheets("FEUIL2").Range("AH2:AV2").Copy
Sheets("Feuil3").Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

[reglement.aspx ]si c'est la solution, penser : REPONSE ACCEPTEE
3
Réponse 4 / 6
didyvine Messages postés 5 Date d'inscription jeudi 9 juillet 2009 Statut Membre Dernière intervention 3 août 2009
10 juil. 2009 à 00:31
Tu n'imagine pas à quel point tu viens de me rendre service!!!!!!!!
Si tu as du temps j'ai une autre question, mon programme travaille sur deux bases de données qui vont alimenter une dizaine de feuilles. Je donc réecrit le programme une dizaine de fois, lol, en changeant les noms, car impossible de faire marcher une for each... Du coup procédure trop longue, moi qui voulait la faire marcher d'un coup avec un bouton...J'ai du la fragmenter. Ps si vous voyez des sottises dedans ou des complications inutiles, n'hésitez pas, je compte vraiment me servir de ce travail comme support pour la suite de mon apprentissage:

Private Sub sunthese()
'Chaque feuil pour chaque panier
'comme il y a un espace avant la premiere ligen qui m'interesse je l'ai traité à part, le but étant de remplir le premier nom de la base dans chacune des feuil

Feuil2.Cells(2, 1) = Feuil1.Cells(85, 12)
Feuil3.Cells(2, 1) = Feuil1.Cells(85, 12)
Feuil4.Cells(2, 1) = Feuil1.Cells(85, 12)
Feuil5.Cells(2, 1) = Feuil1.Cells(85, 12)
Feuil6.Cells(2, 1) = Feuil1.Cells(85, 12)
Feuil7.Cells(2, 1) = Feuil1.Cells(85, 12)
Feuil8.Cells(2, 1) = Feuil1.Cells(85, 12)
Feuil9.Cells(2, 1) = Feuil1.Cells(85, 12)

'là je fait une boucle pour copier coller dans chacune de mes feuil tous les noms des valeurs
i = 85
While Feuil1.Cells(i, 12) <> ""If Feuil1.Cells(i, 12) <> "SUR / sous pondération" Then i i + 1 Else i i + 6

    Feuil2.Cells(i - 83, 1) = Feuil1.Cells(i, 12)
    Feuil3.Cells(i - 83, 1) = Feuil1.Cells(i, 12)
    Feuil4.Cells(i - 83, 1) = Feuil1.Cells(i, 12)
    Feuil5.Cells(i - 83, 1) = Feuil1.Cells(i, 12)
    Feuil6.Cells(i - 83, 1) = Feuil1.Cells(i, 12)
    Feuil7.Cells(i - 83, 1) = Feuil1.Cells(i, 12)
    Feuil8.Cells(i - 83, 1) = Feuil1.Cells(i, 12)
    Feuil9.Cells(i - 83, 1) = Feuil1.Cells(i, 12)

Wend

'Je trie pour avoir à eviter de supprimer les lignes dont la première est vide
Feuil2.Activate
Range("A2:Q500").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    ActiveWindow.SmallScroll Down:=186
    Range("A218").Select
   
'j'élimine toutes les lignes ne correspondant pas à mes valeurs et qui se sont copiées qd meme

i = 2
Do Until Feuil2.Cells(i, 1) = ""
If Feuil2.Cells(i, 1) = "SUR / sous pondération" Then Feuil2.Cells(i, 1).EntireRow.Delete
i = i + 1
Loop
i = 2
Do Until Feuil2.Cells(i, 1) = ""
If Feuil2.Cells(i, 1).Value = "ISHARES DJ STOXX 600 BKS DE" Then Feuil2.Cells(i, 1).EntireRow.Delete
If Feuil2.Cells(i, 1).Value = "Publicité & médias" Then Feuil2.Cells(i, 1).EntireRow.Delete
If Feuil2.Cells(i, 1).Value = "ISHARES DJ ST 600 INSURAN DE" Then Feuil2.Cells(i, 1).EntireRow.Delete

i = i + 1
Loop

' Ensuite j'associe à chacune des valeurs d'un panier son poids donné dans la feuil un par un fonction Vlookup, à partir de la feuil deux puis A partir de la feuil 10, j'associe à chaque valeur un ligne de données

Feuil2.Activate
Feuil2.Cells(2, 2).Select
Selection = "=VLOOKUP(A2,'Feuil1'!L85:V350,2,false)"
Selection.AutoFill Destination:=Range("B2:B500")

        Feuil2.Cells(2, 3).Select
        Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,4,false)" '4 est le numéro de la colonne copiée sur la feuille de réf
        Selection.AutoFill Destination:=Range("C2:C500")
            Feuil2.Cells(2, 4).Select
            Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,5,false)"
            Selection.AutoFill Destination:=Range("D2:D500")
                Feuil2.Cells(2, 5).Select
                Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,6,false)"
                Selection.AutoFill Destination:=Range("E2:E500")
                    Feuil2.Cells(2, 6).Select
                    Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,7,false)"
                    Selection.AutoFill Destination:=Range("F2:F500")
                        Feuil2.Cells(2, 7).Select
                        Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,8,false)"
                        Selection.AutoFill Destination:=Range("G2:G500")
                            Feuil2.Cells(2, 8).Select
                            Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,9,false)"
                            Selection.AutoFill Destination:=Range("H2:H500")
                                Feuil2.Cells(2, 9).Select
                                Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,10,false)"
                                Selection.AutoFill Destination:=Range("I2:I500")
                                    Feuil2.Cells(2, 10).Select
                                    Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,11,false)"
                                    Selection.AutoFill Destination:=Range("J2:J500")
                                        Feuil2.Cells(2, 11).Select
                                        Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,12,false)"
                                        Selection.AutoFill Destination:=Range("K2:K500")
                                            Feuil2.Cells(2, 12).Select
                                            Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,13,false)"
                                            Selection.AutoFill Destination:=Range("L2:L500")
                                                Feuil2.Cells(2, 13).Select
                                                Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,14,false)"
                                                Selection.AutoFill Destination:=Range("M2:M500")
                                                Feuil2.Cells(2, 14).Select
                                                Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,15,false)"
                                                Selection.AutoFill Destination:=Range("N2:N500")
                                                Feuil2.Cells(2, 15).Select
                                                Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,16,false)"
                                                Selection.AutoFill Destination:=Range("O2:O500")
                                                Feuil2.Cells(2, 16).Select
                                                Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,17,false)"
                                                Selection.AutoFill Destination:=Range("P2:P500")
                                                Feuil2.Cells(2, 17).Select
                                                Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,18,false)"
                                                Selection.AutoFill Destination:=Range("Q2:Q500")
                                               
'J'ai fait un copié collé valeur car je n'arrivais pas à travailler les cellules qui faisaient référence à une formule

Columns("AW:AW").Select
    ActiveWindow.ScrollColumn = 41
    ActiveWindow.ScrollColumn = 40
    ActiveWindow.ScrollColumn = 39
    ActiveWindow.ScrollColumn = 38
    ActiveWindow.ScrollColumn = 37
    ActiveWindow.ScrollColumn = 36
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 30
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 27
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Columns("B:B").Select
    Selection.Copy
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 27
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 30
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 36
    ActiveWindow.ScrollColumn = 37
    ActiveWindow.ScrollColumn = 38
    ActiveWindow.ScrollColumn = 39
    ActiveWindow.ScrollColumn = 40
    ActiveWindow.ScrollColumn = 41
    Columns("AW:AW").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'Ici respectivement pour chacune des colonnes d'une valeur je multiplie le poids par toutes les données complémentaires ajoutées sur la ligne

i = 2
Do Until Feuil2.Cells(i, 1) = ""
    For j = 0 To 14
    Feuil2.Cells(i, 18 + j).Formula = "=RC[-16]*RC[-15]"
    Next
i = i + 1
Loop

'Enfin j'aggrege, en inscrivant dans un tableau de la feuil3 la somme de chacune des colonnes, sachant qu'il faut que ca s'arrete des que la case est vide
i = 2
Do Until Feuil2.Cells(i, 1) = ""
For j = 34 To 48
Feuil2.Cells(2, j).Formula = "=SUM(RC[-16]:R[498]C[-16])"
Next
i = i + 1
Loop

Sheets("FEUIL2").Select
Range("AH2:AV2").Select
Selection.Copy
Range("AH2:AV2").Select
Sheets("Feuil3").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'et ce à faire sur chacune des feuil, snif

End Sub
0

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question
Réponse 5 / 6
didyvine Messages postés 5 Date d'inscription jeudi 9 juillet 2009 Statut Membre Dernière intervention 3 août 2009
12 juil. 2009 à 12:48
Bonjour,
Merci pour ces réponses. J'ai essayer la procédure pour répliquer le travail sur toutes mes feuilles mais en vain. En fait quand j'ajoute votre Sub traitement, la fenetre d'ouverture des macro s'ouvre et la procédure n'est pas reconnue. Le probleme en plus c'est qu'il n'y a que certaine partie du pg qui s applique à toutes les feuilles, le début par exemple n'en fait pas partie. Et il faut que tout s'éxécute en un clique je ne peux pas créer plusieurs procédure sub. L'idéal serait que j'associe un bouton à la procédure, pour que çà facilite le travail aux utilisateurs, mais lorsque je crée un bouton et que e copie colle mon code, rien ne se passe.

Voici mon code définitif:





Private Sub sunthese()

Feuil2.Cells(2, 1) = Feuil1.Cells(85, 12)

Feuil3.Cells(2, 1) = Feuil1.Cells(85, 12)

Feuil4.Cells(2, 1) = Feuil1.Cells(85, 12)

Feuil5.Cells(2, 1) = Feuil1.Cells(85, 12)

Feuil6.Cells(2, 1) = Feuil1.Cells(85, 12)

Feuil7.Cells(2, 1) = Feuil1.Cells(85, 12)

Feuil8.Cells(2, 1) = Feuil1.Cells(85, 12)

Feuil9.Cells(2, 1) = Feuil1.Cells(85, 12)







i = 85

While Feuil1.Cells(i, 12) ""

If Feuil1.Cells(i, 12) "SUR / sous pondération" Then i i + 1 Else i i + 6



Feuil2.Cells(i - 83, 1) = Feuil1.Cells(i, 12)

Feuil3.Cells(i - 83, 1) = Feuil1.Cells(i, 12)

Feuil4.Cells(i - 83, 1) = Feuil1.Cells(i, 12)

Feuil5.Cells(i - 83, 1) = Feuil1.Cells(i, 12)

Feuil6.Cells(i - 83, 1) = Feuil1.Cells(i, 12)

Feuil7.Cells(i - 83, 1) = Feuil1.Cells(i, 12)

Feuil8.Cells(i - 83, 1) = Feuil1.Cells(i, 12)

Feuil9.Cells(i - 83, 1) = Feuil1.Cells(i, 12)



Wend



Sheets("Feuil2").Select

Columns("A:A").Select

Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal



i = 2

Do Until Feuil2.Cells(i, 1) = ""

If Feuil2.Cells(i, 1) = "SUR / sous pondération" _

Or Feuil2.Cells(i, 1).Value = "ISHARES DJ STOXX 600 BKS DE" _

Or Feuil2.Cells(i, 1).Value = "Publicité & médias" _

Or Feuil2.Cells(i, 1).Value = "ISHARES DJ ST 600 INSURAN DE" _

Or Feuil2.Cells(i, 1).Value = "LYXOR ETF DJ STX HLTH CARE" _

Then

Feuil2.Cells(i, 1).EntireRow.Delete

i = i - 1

End If

i = i + 1

Loop





Feuil2.Activate

Feuil2.Cells(2, 2).Select

Selection = "=VLOOKUP(A2,'Feuil1'!$L$85:$V$350,2,false)"

Selection.AutoFill Destination:=Range("B2:B500")



Feuil2.Cells(2, 3).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,4,false)" '4 est le numéro de la colonne copiée sur la feuille de réf

Selection.AutoFill Destination:=Range("C2:C500")

Feuil2.Cells(2, 4).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,5,false)"

Selection.AutoFill Destination:=Range("D2:D500")

Feuil2.Cells(2, 5).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,6,false)"

Selection.AutoFill Destination:=Range("E2:E500")

Feuil2.Cells(2, 6).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,7,false)"

Selection.AutoFill Destination:=Range("F2:F500")

Feuil2.Cells(2, 7).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,8,false)"

Selection.AutoFill Destination:=Range("G2:G500")

Feuil2.Cells(2, 8).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,9,false)"

Selection.AutoFill Destination:=Range("H2:H500")

Feuil2.Cells(2, 9).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,10,false)"

Selection.AutoFill Destination:=Range("I2:I500")

Feuil2.Cells(2, 10).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,11,false)"

Selection.AutoFill Destination:=Range("J2:J500")

Feuil2.Cells(2, 11).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,12,false)"

Selection.AutoFill Destination:=Range("K2:K500")

Feuil2.Cells(2, 12).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,13,false)"

Selection.AutoFill Destination:=Range("L2:L500")

Feuil2.Cells(2, 13).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,14,false)"

Selection.AutoFill Destination:=Range("M2:M500")

Feuil2.Cells(2, 14).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,15,false)"

Selection.AutoFill Destination:=Range("N2:N500")

Feuil2.Cells(2, 15).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,16,false)"

Selection.AutoFill Destination:=Range("O2:O500")

Feuil2.Cells(2, 16).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,17,false)"

Selection.AutoFill Destination:=Range("P2:P500")

Feuil2.Cells(2, 17).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,18,false)"

Selection.AutoFill Destination:=Range("Q2:Q500")

Columns("AW:AW").Select

ActiveWindow.ScrollColumn = 41

ActiveWindow.ScrollColumn = 40

ActiveWindow.ScrollColumn = 39

ActiveWindow.ScrollColumn = 38

ActiveWindow.ScrollColumn = 37

ActiveWindow.ScrollColumn = 36

ActiveWindow.ScrollColumn = 35

ActiveWindow.ScrollColumn = 34

ActiveWindow.ScrollColumn = 33

ActiveWindow.ScrollColumn = 32

ActiveWindow.ScrollColumn = 31

ActiveWindow.ScrollColumn = 30

ActiveWindow.ScrollColumn = 29

ActiveWindow.ScrollColumn = 28

ActiveWindow.ScrollColumn = 27

ActiveWindow.ScrollColumn = 26

ActiveWindow.ScrollColumn = 25

ActiveWindow.ScrollColumn = 24

ActiveWindow.ScrollColumn = 23

ActiveWindow.ScrollColumn = 22

ActiveWindow.ScrollColumn = 21

ActiveWindow.ScrollColumn = 20

ActiveWindow.ScrollColumn = 19

ActiveWindow.ScrollColumn = 18

ActiveWindow.ScrollColumn = 17

ActiveWindow.ScrollColumn = 16

ActiveWindow.ScrollColumn = 15

ActiveWindow.ScrollColumn = 14

ActiveWindow.ScrollColumn = 13

ActiveWindow.ScrollColumn = 12

ActiveWindow.ScrollColumn = 11

ActiveWindow.ScrollColumn = 10

ActiveWindow.ScrollColumn = 9

ActiveWindow.ScrollColumn = 8

ActiveWindow.ScrollColumn = 7

ActiveWindow.ScrollColumn = 6

ActiveWindow.ScrollColumn = 5

ActiveWindow.ScrollColumn = 4

ActiveWindow.ScrollColumn = 3

ActiveWindow.ScrollColumn = 2

ActiveWindow.ScrollColumn = 1

Columns("B:B").Select

Selection.Copy

ActiveWindow.ScrollColumn = 2

ActiveWindow.ScrollColumn = 3

ActiveWindow.ScrollColumn = 4

ActiveWindow.ScrollColumn = 5

ActiveWindow.ScrollColumn = 6

ActiveWindow.ScrollColumn = 7

ActiveWindow.ScrollColumn = 8

ActiveWindow.ScrollColumn = 9

ActiveWindow.ScrollColumn = 10

ActiveWindow.ScrollColumn = 11

ActiveWindow.ScrollColumn = 12

ActiveWindow.ScrollColumn = 13

ActiveWindow.ScrollColumn = 14

ActiveWindow.ScrollColumn = 15

ActiveWindow.ScrollColumn = 16

ActiveWindow.ScrollColumn = 17

ActiveWindow.ScrollColumn = 18

ActiveWindow.ScrollColumn = 19

ActiveWindow.ScrollColumn = 20

ActiveWindow.ScrollColumn = 21

ActiveWindow.ScrollColumn = 22

ActiveWindow.ScrollColumn = 23

ActiveWindow.ScrollColumn = 24

ActiveWindow.ScrollColumn = 25

ActiveWindow.ScrollColumn = 26

ActiveWindow.ScrollColumn = 27

ActiveWindow.ScrollColumn = 28

ActiveWindow.ScrollColumn = 29

ActiveWindow.ScrollColumn = 30

ActiveWindow.ScrollColumn = 31

ActiveWindow.ScrollColumn = 32

ActiveWindow.ScrollColumn = 33

ActiveWindow.ScrollColumn = 34

ActiveWindow.ScrollColumn = 35

ActiveWindow.ScrollColumn = 36

ActiveWindow.ScrollColumn = 37

ActiveWindow.ScrollColumn = 38

ActiveWindow.ScrollColumn = 39

ActiveWindow.ScrollColumn = 40

ActiveWindow.ScrollColumn = 41

Columns("AW:AW").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False



i = 2

Do Until Feuil2.Cells(i, 1) = ""

If Feuil2.Cells(i, 49) = "" _

Or Feuil2.Cells(i, 49).Value = 0 _

Then

Feuil2.Cells(i, 1).EntireRow.Delete

i = i - 1

End If

i = i + 1

Loop





i = 2

Do Until Feuil2.Cells(i, 1) = ""



For j = 3 To 17

Feuil2.Cells(i, j + 15) = Feuil2.Cells(i, 2) * Feuil2.Cells(i, j)

Next

i = i + 1

Loop

'on aggrège enfin le risque dans un tableau synthès





i = 2

Do Until Feuil2.Cells(i, 1) = ""

For j = 34 To 48

Feuil2.Cells(2, j).Formula = "=SUM(RC[-16]:R[498]C[-16])"

Next

i = i + 1

Loop























'FEUILLE 3



Sheets("Feuil3").Select

Columns("A:A").Select

Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal





i = 2

Do Until Feuil3.Cells(i, 1) = ""

If Feuil3.Cells(i, 1) = "SUR / sous pondération" _

Or Feuil3.Cells(i, 1).Value = "ISHARES DJ STOXX 600 BKS DE" _

Or Feuil3.Cells(i, 1).Value = "Publicité & médias" _

Or Feuil3.Cells(i, 1).Value = "ISHARES DJ ST 600 INSURAN DE" _

Or Feuil3.Cells(i, 1).Value = "LYXOR ETF DJ STX HLTH CARE" _

Then

Feuil3.Cells(i, 1).EntireRow.Delete

i = i - 1

End If

i = i + 1

Loop





Feuil3.Activate

Feuil3.Cells(2, 2).Select

Selection = "=VLOOKUP(A2,'Feuil1'!$L$85:$V$350,3,false)"

Selection.AutoFill Destination:=Range("B2:B500")



Feuil3.Cells(2, 3).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,4,false)" '4 est le numéro de la colonne copiée sur la feuille de réf

Selection.AutoFill Destination:=Range("C2:C500")

Feuil3.Cells(2, 4).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,5,false)"

Selection.AutoFill Destination:=Range("D2:D500")

Feuil3.Cells(2, 5).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,6,false)"

Selection.AutoFill Destination:=Range("E2:E500")

Feuil3.Cells(2, 6).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,7,false)"

Selection.AutoFill Destination:=Range("F2:F500")

Feuil3.Cells(2, 7).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,8,false)"

Selection.AutoFill Destination:=Range("G2:G500")

Feuil3.Cells(2, 8).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,9,false)"

Selection.AutoFill Destination:=Range("H2:H500")

Feuil3.Cells(2, 9).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,10,false)"

Selection.AutoFill Destination:=Range("I2:I500")

Feuil3.Cells(2, 10).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,11,false)"

Selection.AutoFill Destination:=Range("J2:J500")

Feuil3.Cells(2, 11).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,12,false)"

Selection.AutoFill Destination:=Range("K2:K500")

Feuil3.Cells(2, 12).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,13,false)"

Selection.AutoFill Destination:=Range("L2:L500")

Feuil3.Cells(2, 13).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,14,false)"

Selection.AutoFill Destination:=Range("M2:M500")

Feuil3.Cells(2, 14).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,15,false)"

Selection.AutoFill Destination:=Range("N2:N500")

Feuil3.Cells(2, 15).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,16,false)"

Selection.AutoFill Destination:=Range("O2:O500")

Feuil3.Cells(2, 16).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,17,false)"

Selection.AutoFill Destination:=Range("P2:P500")

Feuil3.Cells(2, 17).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,18,false)"

Selection.AutoFill Destination:=Range("Q2:Q500")

Columns("AW:AW").Select

ActiveWindow.ScrollColumn = 41

ActiveWindow.ScrollColumn = 40

ActiveWindow.ScrollColumn = 39

ActiveWindow.ScrollColumn = 38

ActiveWindow.ScrollColumn = 37

ActiveWindow.ScrollColumn = 36

ActiveWindow.ScrollColumn = 35

ActiveWindow.ScrollColumn = 34

ActiveWindow.ScrollColumn = 33

ActiveWindow.ScrollColumn = 32

ActiveWindow.ScrollColumn = 31

ActiveWindow.ScrollColumn = 30

ActiveWindow.ScrollColumn = 29

ActiveWindow.ScrollColumn = 28

ActiveWindow.ScrollColumn = 27

ActiveWindow.ScrollColumn = 26

ActiveWindow.ScrollColumn = 25

ActiveWindow.ScrollColumn = 24

ActiveWindow.ScrollColumn = 23

ActiveWindow.ScrollColumn = 22

ActiveWindow.ScrollColumn = 21

ActiveWindow.ScrollColumn = 20

ActiveWindow.ScrollColumn = 19

ActiveWindow.ScrollColumn = 18

ActiveWindow.ScrollColumn = 17

ActiveWindow.ScrollColumn = 16

ActiveWindow.ScrollColumn = 15

ActiveWindow.ScrollColumn = 14

ActiveWindow.ScrollColumn = 13

ActiveWindow.ScrollColumn = 12

ActiveWindow.ScrollColumn = 11

ActiveWindow.ScrollColumn = 10

ActiveWindow.ScrollColumn = 9

ActiveWindow.ScrollColumn = 8

ActiveWindow.ScrollColumn = 7

ActiveWindow.ScrollColumn = 6

ActiveWindow.ScrollColumn = 5

ActiveWindow.ScrollColumn = 4

ActiveWindow.ScrollColumn = 3

ActiveWindow.ScrollColumn = 2

ActiveWindow.ScrollColumn = 1

Columns("B:B").Select

Selection.Copy

ActiveWindow.ScrollColumn = 2

ActiveWindow.ScrollColumn = 3

ActiveWindow.ScrollColumn = 4

ActiveWindow.ScrollColumn = 5

ActiveWindow.ScrollColumn = 6

ActiveWindow.ScrollColumn = 7

ActiveWindow.ScrollColumn = 8

ActiveWindow.ScrollColumn = 9

ActiveWindow.ScrollColumn = 10

ActiveWindow.ScrollColumn = 11

ActiveWindow.ScrollColumn = 12

ActiveWindow.ScrollColumn = 13

ActiveWindow.ScrollColumn = 14

ActiveWindow.ScrollColumn = 15

ActiveWindow.ScrollColumn = 16

ActiveWindow.ScrollColumn = 17

ActiveWindow.ScrollColumn = 18

ActiveWindow.ScrollColumn = 19

ActiveWindow.ScrollColumn = 20

ActiveWindow.ScrollColumn = 21

ActiveWindow.ScrollColumn = 22

ActiveWindow.ScrollColumn = 23

ActiveWindow.ScrollColumn = 24

ActiveWindow.ScrollColumn = 25

ActiveWindow.ScrollColumn = 26

ActiveWindow.ScrollColumn = 27

ActiveWindow.ScrollColumn = 28

ActiveWindow.ScrollColumn = 29

ActiveWindow.ScrollColumn = 30

ActiveWindow.ScrollColumn = 31

ActiveWindow.ScrollColumn = 32

ActiveWindow.ScrollColumn = 33

ActiveWindow.ScrollColumn = 34

ActiveWindow.ScrollColumn = 35

ActiveWindow.ScrollColumn = 36

ActiveWindow.ScrollColumn = 37

ActiveWindow.ScrollColumn = 38

ActiveWindow.ScrollColumn = 39

ActiveWindow.ScrollColumn = 40

ActiveWindow.ScrollColumn = 41

Columns("AW:AW").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False



i = 2

Do Until Feuil3.Cells(i, 1) = ""

If Feuil3.Cells(i, 49) = "" _

Or Feuil3.Cells(i, 49).Value = 0 _

Then

Feuil3.Cells(i, 1).EntireRow.Delete

i = i - 1

End If

i = i + 1

Loop





i = 2

Do Until Feuil3.Cells(i, 1) = ""



For j = 3 To 17

Feuil3.Cells(i, j + 15) = Feuil3.Cells(i, 2) * Feuil3.Cells(i, j)

Next

i = i + 1

Loop

'on aggrège enfin le risque dans un tableau synthès





i = 2

Do Until Feuil3.Cells(i, 1) = ""

For j = 34 To 48

Feuil3.Cells(2, j).Formula = "=SUM(RC[-16]:R[498]C[-16])"

Next

i = i + 1

Loop



'FEUILLE 4



Feuil4.Activate

Sheets("Feuil4").Select

Columns("A:A").Select

Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal

i = 2

Do Until Feuil4.Cells(i, 1) = ""

If Feuil4.Cells(i, 1) = "SUR / sous pondération" _

Or Feuil4.Cells(i, 1).Value = "ISHARES DJ STOXX 600 BKS DE" _

Or Feuil4.Cells(i, 1).Value = "Publicité & médias" _

Or Feuil4.Cells(i, 1).Value = "ISHARES DJ ST 600 INSURAN DE" _

Or Feuil4.Cells(i, 1).Value = "LYXOR ETF DJ STX HLTH CARE" _

Then

Feuil4.Cells(i, 1).EntireRow.Delete

i = i - 1

End If

i = i + 1

Loop





Feuil4.Activate

Feuil4.Cells(2, 2).Select

Selection = "=VLOOKUP(A2,'Feuil1'!$L$85:$V$350,4,false)"

Selection.AutoFill Destination:=Range("B2:B500")



Feuil4.Cells(2, 3).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,4,false)" '4 est le numéro de la colonne copiée sur la feuille de réf

Selection.AutoFill Destination:=Range("C2:C500")

Feuil4.Cells(2, 4).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,5,false)"

Selection.AutoFill Destination:=Range("D2:D500")

Feuil4.Cells(2, 5).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,6,false)"

Selection.AutoFill Destination:=Range("E2:E500")

Feuil4.Cells(2, 6).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,7,false)"

Selection.AutoFill Destination:=Range("F2:F500")

Feuil4.Cells(2, 7).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,8,false)"

Selection.AutoFill Destination:=Range("G2:G500")

Feuil4.Cells(2, 8).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,9,false)"

Selection.AutoFill Destination:=Range("H2:H500")

Feuil4.Cells(2, 9).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,10,false)"

Selection.AutoFill Destination:=Range("I2:I500")

Feuil4.Cells(2, 10).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,11,false)"

Selection.AutoFill Destination:=Range("J2:J500")

Feuil4.Cells(2, 11).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,12,false)"

Selection.AutoFill Destination:=Range("K2:K500")

Feuil4.Cells(2, 12).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,13,false)"

Selection.AutoFill Destination:=Range("L2:L500")

Feuil4.Cells(2, 13).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,14,false)"

Selection.AutoFill Destination:=Range("M2:M500")

Feuil4.Cells(2, 14).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,15,false)"

Selection.AutoFill Destination:=Range("N2:N500")

Feuil4.Cells(2, 15).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,16,false)"

Selection.AutoFill Destination:=Range("O2:O500")

Feuil4.Cells(2, 16).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,17,false)"

Selection.AutoFill Destination:=Range("P2:P500")

Feuil4.Cells(2, 17).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,18,false)"

Selection.AutoFill Destination:=Range("Q2:Q500")

Columns("AW:AW").Select

ActiveWindow.ScrollColumn = 41

ActiveWindow.ScrollColumn = 40

ActiveWindow.ScrollColumn = 39

ActiveWindow.ScrollColumn = 38

ActiveWindow.ScrollColumn = 37

ActiveWindow.ScrollColumn = 36

ActiveWindow.ScrollColumn = 35

ActiveWindow.ScrollColumn = 34

ActiveWindow.ScrollColumn = 33

ActiveWindow.ScrollColumn = 32

ActiveWindow.ScrollColumn = 31

ActiveWindow.ScrollColumn = 30

ActiveWindow.ScrollColumn = 29

ActiveWindow.ScrollColumn = 28

ActiveWindow.ScrollColumn = 27

ActiveWindow.ScrollColumn = 26

ActiveWindow.ScrollColumn = 25

ActiveWindow.ScrollColumn = 24

ActiveWindow.ScrollColumn = 23

ActiveWindow.ScrollColumn = 22

ActiveWindow.ScrollColumn = 21

ActiveWindow.ScrollColumn = 20

ActiveWindow.ScrollColumn = 19

ActiveWindow.ScrollColumn = 18

ActiveWindow.ScrollColumn = 17

ActiveWindow.ScrollColumn = 16

ActiveWindow.ScrollColumn = 15

ActiveWindow.ScrollColumn = 14

ActiveWindow.ScrollColumn = 13

ActiveWindow.ScrollColumn = 12

ActiveWindow.ScrollColumn = 11

ActiveWindow.ScrollColumn = 10

ActiveWindow.ScrollColumn = 9

ActiveWindow.ScrollColumn = 8

ActiveWindow.ScrollColumn = 7

ActiveWindow.ScrollColumn = 6

ActiveWindow.ScrollColumn = 5

ActiveWindow.ScrollColumn = 4

ActiveWindow.ScrollColumn = 3

ActiveWindow.ScrollColumn = 2

ActiveWindow.ScrollColumn = 1

Columns("B:B").Select

Selection.Copy

ActiveWindow.ScrollColumn = 2

ActiveWindow.ScrollColumn = 3

ActiveWindow.ScrollColumn = 4

ActiveWindow.ScrollColumn = 5

ActiveWindow.ScrollColumn = 6

ActiveWindow.ScrollColumn = 7

ActiveWindow.ScrollColumn = 8

ActiveWindow.ScrollColumn = 9

ActiveWindow.ScrollColumn = 10

ActiveWindow.ScrollColumn = 11

ActiveWindow.ScrollColumn = 12

ActiveWindow.ScrollColumn = 13

ActiveWindow.ScrollColumn = 14

ActiveWindow.ScrollColumn = 15

ActiveWindow.ScrollColumn = 16

ActiveWindow.ScrollColumn = 17

ActiveWindow.ScrollColumn = 18

ActiveWindow.ScrollColumn = 19

ActiveWindow.ScrollColumn = 20

ActiveWindow.ScrollColumn = 21

ActiveWindow.ScrollColumn = 22

ActiveWindow.ScrollColumn = 23

ActiveWindow.ScrollColumn = 24

ActiveWindow.ScrollColumn = 25

ActiveWindow.ScrollColumn = 26

ActiveWindow.ScrollColumn = 27

ActiveWindow.ScrollColumn = 28

ActiveWindow.ScrollColumn = 29

ActiveWindow.ScrollColumn = 30

ActiveWindow.ScrollColumn = 31

ActiveWindow.ScrollColumn = 32

ActiveWindow.ScrollColumn = 33

ActiveWindow.ScrollColumn = 34

ActiveWindow.ScrollColumn = 35

ActiveWindow.ScrollColumn = 36

ActiveWindow.ScrollColumn = 37

ActiveWindow.ScrollColumn = 38

ActiveWindow.ScrollColumn = 39

ActiveWindow.ScrollColumn = 40

ActiveWindow.ScrollColumn = 41

Columns("AW:AW").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False



i = 2

Do Until Feuil4.Cells(i, 1) = ""

If Feuil4.Cells(i, 49) = "" _

Or Feuil4.Cells(i, 49).Value = 0 _

Then

Feuil4.Cells(i, 1).EntireRow.Delete

i = i - 1

End If

i = i + 1

Loop





i = 2

Do Until Feuil4.Cells(i, 1) = ""



For j = 3 To 17

Feuil4.Cells(i, j + 15) = Feuil4.Cells(i, 2) * Feuil4.Cells(i, j)

Next

i = i + 1

Loop

'on aggrège enfin le risque dans un tableau synthès





i = 2

Do Until Feuil4.Cells(i, 1) = ""

For j = 34 To 48

Feuil4.Cells(2, j).Formula = "=SUM(RC[-16]:R[498]C[-16])"

Next

i = i + 1

Loop



'FEUILLE 5



Feuil5.Activate

Sheets("Feuil5").Select

Columns("A:A").Select

Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal

i = 2

Do Until Feuil5.Cells(i, 1) = ""

If Feuil5.Cells(i, 1) = "SUR / sous pondération" _

Or Feuil5.Cells(i, 1).Value = "ISHARES DJ STOXX 600 BKS DE" _

Or Feuil5.Cells(i, 1).Value = "Publicité & médias" _

Or Feuil5.Cells(i, 1).Value = "ISHARES DJ ST 600 INSURAN DE" _

Or Feuil5.Cells(i, 1).Value = "LYXOR ETF DJ STX HLTH CARE" _

Then

Feuil5.Cells(i, 1).EntireRow.Delete

i = i - 1

End If

i = i + 1

Loop





Feuil5.Activate

Feuil5.Cells(2, 2).Select

Selection = "=VLOOKUP(A2,'Feuil1'!$L$85:$V$350,5,false)"

Selection.AutoFill Destination:=Range("B2:B500")



Feuil5.Cells(2, 3).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,4,false)" '4 est le numéro de la colonne copiée sur la feuille de réf

Selection.AutoFill Destination:=Range("C2:C500")

Feuil5.Cells(2, 4).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,5,false)"

Selection.AutoFill Destination:=Range("D2:D500")

Feuil5.Cells(2, 5).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,6,false)"

Selection.AutoFill Destination:=Range("E2:E500")

Feuil5.Cells(2, 6).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,7,false)"

Selection.AutoFill Destination:=Range("F2:F500")

Feuil5.Cells(2, 7).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,8,false)"

Selection.AutoFill Destination:=Range("G2:G500")

Feuil5.Cells(2, 8).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,9,false)"

Selection.AutoFill Destination:=Range("H2:H500")

Feuil5.Cells(2, 9).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,10,false)"

Selection.AutoFill Destination:=Range("I2:I500")

Feuil5.Cells(2, 10).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,11,false)"

Selection.AutoFill Destination:=Range("J2:J500")

Feuil5.Cells(2, 11).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,12,false)"

Selection.AutoFill Destination:=Range("K2:K500")

Feuil5.Cells(2, 12).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,13,false)"

Selection.AutoFill Destination:=Range("L2:L500")

Feuil5.Cells(2, 13).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,14,false)"

Selection.AutoFill Destination:=Range("M2:M500")

Feuil5.Cells(2, 14).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,15,false)"

Selection.AutoFill Destination:=Range("N2:N500")

Feuil5.Cells(2, 15).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,16,false)"

Selection.AutoFill Destination:=Range("O2:O500")

Feuil5.Cells(2, 16).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,17,false)"

Selection.AutoFill Destination:=Range("P2:P500")

Feuil5.Cells(2, 17).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,18,false)"

Selection.AutoFill Destination:=Range("Q2:Q500")

Columns("AW:AW").Select

ActiveWindow.ScrollColumn = 41

ActiveWindow.ScrollColumn = 40

ActiveWindow.ScrollColumn = 39

ActiveWindow.ScrollColumn = 38

ActiveWindow.ScrollColumn = 37

ActiveWindow.ScrollColumn = 36

ActiveWindow.ScrollColumn = 35

ActiveWindow.ScrollColumn = 34

ActiveWindow.ScrollColumn = 33

ActiveWindow.ScrollColumn = 32

ActiveWindow.ScrollColumn = 31

ActiveWindow.ScrollColumn = 30

ActiveWindow.ScrollColumn = 29

ActiveWindow.ScrollColumn = 28

ActiveWindow.ScrollColumn = 27

ActiveWindow.ScrollColumn = 26

ActiveWindow.ScrollColumn = 25

ActiveWindow.ScrollColumn = 24

ActiveWindow.ScrollColumn = 23

ActiveWindow.ScrollColumn = 22

ActiveWindow.ScrollColumn = 21

ActiveWindow.ScrollColumn = 20

ActiveWindow.ScrollColumn = 19

ActiveWindow.ScrollColumn = 18

ActiveWindow.ScrollColumn = 17

ActiveWindow.ScrollColumn = 16

ActiveWindow.ScrollColumn = 15

ActiveWindow.ScrollColumn = 14

ActiveWindow.ScrollColumn = 13

ActiveWindow.ScrollColumn = 12

ActiveWindow.ScrollColumn = 11

ActiveWindow.ScrollColumn = 10

ActiveWindow.ScrollColumn = 9

ActiveWindow.ScrollColumn = 8

ActiveWindow.ScrollColumn = 7

ActiveWindow.ScrollColumn = 6

ActiveWindow.ScrollColumn = 5

ActiveWindow.ScrollColumn = 4

ActiveWindow.ScrollColumn = 3

ActiveWindow.ScrollColumn = 2

ActiveWindow.ScrollColumn = 1

Columns("B:B").Select

Selection.Copy

ActiveWindow.ScrollColumn = 2

ActiveWindow.ScrollColumn = 3

ActiveWindow.ScrollColumn = 4

ActiveWindow.ScrollColumn = 5

ActiveWindow.ScrollColumn = 6

ActiveWindow.ScrollColumn = 7

ActiveWindow.ScrollColumn = 8

ActiveWindow.ScrollColumn = 9

ActiveWindow.ScrollColumn = 10

ActiveWindow.ScrollColumn = 11

ActiveWindow.ScrollColumn = 12

ActiveWindow.ScrollColumn = 13

ActiveWindow.ScrollColumn = 14

ActiveWindow.ScrollColumn = 15

ActiveWindow.ScrollColumn = 16

ActiveWindow.ScrollColumn = 17

ActiveWindow.ScrollColumn = 18

ActiveWindow.ScrollColumn = 19

ActiveWindow.ScrollColumn = 20

ActiveWindow.ScrollColumn = 21

ActiveWindow.ScrollColumn = 22

ActiveWindow.ScrollColumn = 23

ActiveWindow.ScrollColumn = 24

ActiveWindow.ScrollColumn = 25

ActiveWindow.ScrollColumn = 26

ActiveWindow.ScrollColumn = 27

ActiveWindow.ScrollColumn = 28

ActiveWindow.ScrollColumn = 29

ActiveWindow.ScrollColumn = 30

ActiveWindow.ScrollColumn = 31

ActiveWindow.ScrollColumn = 32

ActiveWindow.ScrollColumn = 33

ActiveWindow.ScrollColumn = 34

ActiveWindow.ScrollColumn = 35

ActiveWindow.ScrollColumn = 36

ActiveWindow.ScrollColumn = 37

ActiveWindow.ScrollColumn = 38

ActiveWindow.ScrollColumn = 39

ActiveWindow.ScrollColumn = 40

ActiveWindow.ScrollColumn = 41

Columns("AW:AW").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False



i = 2

Do Until Feuil5.Cells(i, 1) = ""

If Feuil5.Cells(i, 49) = "" _

Or Feuil5.Cells(i, 49).Value = 0 _

Then

Feuil5.Cells(i, 1).EntireRow.Delete

i = i - 1

End If

i = i + 1

Loop





i = 2

Do Until Feuil5.Cells(i, 1) = ""



For j = 3 To 17

Feuil5.Cells(i, j + 15) = Feuil5.Cells(i, 2) * Feuil5.Cells(i, j)

Next

i = i + 1

Loop

'on aggrège enfin le risque dans un tableau synthès





i = 2

Do Until Feuil5.Cells(i, 1) = ""

For j = 34 To 48

Feuil5.Cells(2, j).Formula = "=SUM(RC[-16]:R[498]C[-16])"

Next

i = i + 1

Loop







'FEUILLE 6



Sheets("Feuil6").Select

Columns("A:A").Select

Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal

i = 2

Do Until Feuil6.Cells(i, 1) = ""

If Feuil6.Cells(i, 1) = "SUR / sous pondération" _

Or Feuil6.Cells(i, 1).Value = "ISHARES DJ STOXX 600 BKS DE" _

Or Feuil6.Cells(i, 1).Value = "Publicité & médias" _

Or Feuil6.Cells(i, 1).Value = "ISHARES DJ ST 600 INSURAN DE" _

Or Feuil6.Cells(i, 1).Value = "LYXOR ETF DJ STX HLTH CARE" _

Then

Feuil6.Cells(i, 1).EntireRow.Delete

i = i - 1

End If

i = i + 1

Loop



Feuil6.Activate

Feuil6.Cells(2, 2).Select

Selection = "=VLOOKUP(A2,'Feuil1'!$L$85:$V$350,7,false)"

Selection.AutoFill Destination:=Range("B2:B500")



Feuil6.Cells(2, 3).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,4,false)" '4 est le numéro de la colonne copiée sur la feuille de réf

Selection.AutoFill Destination:=Range("C2:C500")

Feuil6.Cells(2, 4).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,5,false)"

Selection.AutoFill Destination:=Range("D2:D500")

Feuil6.Cells(2, 5).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,6,false)"

Selection.AutoFill Destination:=Range("E2:E500")

Feuil6.Cells(2, 6).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,7,false)"

Selection.AutoFill Destination:=Range("F2:F500")

Feuil6.Cells(2, 7).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,8,false)"

Selection.AutoFill Destination:=Range("G2:G500")

Feuil6.Cells(2, 8).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,9,false)"

Selection.AutoFill Destination:=Range("H2:H500")

Feuil6.Cells(2, 9).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,10,false)"

Selection.AutoFill Destination:=Range("I2:I500")

Feuil6.Cells(2, 10).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,11,false)"

Selection.AutoFill Destination:=Range("J2:J500")

Feuil6.Cells(2, 11).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,12,false)"

Selection.AutoFill Destination:=Range("K2:K500")

Feuil6.Cells(2, 12).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,13,false)"

Selection.AutoFill Destination:=Range("L2:L500")

Feuil6.Cells(2, 13).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,14,false)"

Selection.AutoFill Destination:=Range("M2:M500")

Feuil6.Cells(2, 14).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,15,false)"

Selection.AutoFill Destination:=Range("N2:N500")

Feuil6.Cells(2, 15).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,16,false)"

Selection.AutoFill Destination:=Range("O2:O500")

Feuil6.Cells(2, 16).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,17,false)"

Selection.AutoFill Destination:=Range("P2:P500")

Feuil6.Cells(2, 17).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,18,false)"

Selection.AutoFill Destination:=Range("Q2:Q500")

Columns("AW:AW").Select

ActiveWindow.ScrollColumn = 41

ActiveWindow.ScrollColumn = 40

ActiveWindow.ScrollColumn = 39

ActiveWindow.ScrollColumn = 38

ActiveWindow.ScrollColumn = 37

ActiveWindow.ScrollColumn = 36

ActiveWindow.ScrollColumn = 35

ActiveWindow.ScrollColumn = 34

ActiveWindow.ScrollColumn = 33

ActiveWindow.ScrollColumn = 32

ActiveWindow.ScrollColumn = 31

ActiveWindow.ScrollColumn = 30

ActiveWindow.ScrollColumn = 29

ActiveWindow.ScrollColumn = 28

ActiveWindow.ScrollColumn = 27

ActiveWindow.ScrollColumn = 26

ActiveWindow.ScrollColumn = 25

ActiveWindow.ScrollColumn = 24

ActiveWindow.ScrollColumn = 23

ActiveWindow.ScrollColumn = 22

ActiveWindow.ScrollColumn = 21

ActiveWindow.ScrollColumn = 20

ActiveWindow.ScrollColumn = 19

ActiveWindow.ScrollColumn = 18

ActiveWindow.ScrollColumn = 17

ActiveWindow.ScrollColumn = 16

ActiveWindow.ScrollColumn = 15

ActiveWindow.ScrollColumn = 14

ActiveWindow.ScrollColumn = 13

ActiveWindow.ScrollColumn = 12

ActiveWindow.ScrollColumn = 11

ActiveWindow.ScrollColumn = 10

ActiveWindow.ScrollColumn = 9

ActiveWindow.ScrollColumn = 8

ActiveWindow.ScrollColumn = 7

ActiveWindow.ScrollColumn = 6

ActiveWindow.ScrollColumn = 5

ActiveWindow.ScrollColumn = 4

ActiveWindow.ScrollColumn = 3

ActiveWindow.ScrollColumn = 2

ActiveWindow.ScrollColumn = 1

Columns("B:B").Select

Selection.Copy

ActiveWindow.ScrollColumn = 2

ActiveWindow.ScrollColumn = 3

ActiveWindow.ScrollColumn = 4

ActiveWindow.ScrollColumn = 5

ActiveWindow.ScrollColumn = 6

ActiveWindow.ScrollColumn = 7

ActiveWindow.ScrollColumn = 8

ActiveWindow.ScrollColumn = 9

ActiveWindow.ScrollColumn = 10

ActiveWindow.ScrollColumn = 11

ActiveWindow.ScrollColumn = 12

ActiveWindow.ScrollColumn = 13

ActiveWindow.ScrollColumn = 14

ActiveWindow.ScrollColumn = 15

ActiveWindow.ScrollColumn = 16

ActiveWindow.ScrollColumn = 17

ActiveWindow.ScrollColumn = 18

ActiveWindow.ScrollColumn = 19

ActiveWindow.ScrollColumn = 20

ActiveWindow.ScrollColumn = 21

ActiveWindow.ScrollColumn = 22

ActiveWindow.ScrollColumn = 23

ActiveWindow.ScrollColumn = 24

ActiveWindow.ScrollColumn = 25

ActiveWindow.ScrollColumn = 26

ActiveWindow.ScrollColumn = 27

ActiveWindow.ScrollColumn = 28

ActiveWindow.ScrollColumn = 29

ActiveWindow.ScrollColumn = 30

ActiveWindow.ScrollColumn = 31

ActiveWindow.ScrollColumn = 32

ActiveWindow.ScrollColumn = 33

ActiveWindow.ScrollColumn = 34

ActiveWindow.ScrollColumn = 35

ActiveWindow.ScrollColumn = 36

ActiveWindow.ScrollColumn = 37

ActiveWindow.ScrollColumn = 38

ActiveWindow.ScrollColumn = 39

ActiveWindow.ScrollColumn = 40

ActiveWindow.ScrollColumn = 41

Columns("AW:AW").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False



i = 2

Do Until Feuil6.Cells(i, 1) = ""

If Feuil6.Cells(i, 49) = "" _

Or Feuil6.Cells(i, 49).Value = 0 _

Then

Feuil6.Cells(i, 1).EntireRow.Delete

i = i - 1

End If

i = i + 1

Loop





i = 2

Do Until Feuil6.Cells(i, 1) = ""



For j = 3 To 17

Feuil6.Cells(i, j + 15) = Feuil6.Cells(i, 2) * Feuil6.Cells(i, j)

Next

i = i + 1

Loop

'on aggrège enfin le risque dans un tableau synthès





i = 2

Do Until Feuil6.Cells(i, 1) = ""

For j = 34 To 48

Feuil6.Cells(2, j).Formula = "=SUM(RC[-16]:R[498]C[-16])"

Next

i = i + 1

Loop





'FEUILLE 7

Sheets("Feuil7").Select

Columns("A:A").Select

Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal



i = 2

Do Until Feuil7.Cells(i, 1) = ""

If Feuil7.Cells(i, 1) = "SUR / sous pondération" _

Or Feuil7.Cells(i, 1).Value = "ISHARES DJ STOXX 600 BKS DE" _

Or Feuil7.Cells(i, 1).Value = "Publicité & médias" _

Or Feuil7.Cells(i, 1).Value = "ISHARES DJ ST 600 INSURAN DE" _

Or Feuil7.Cells(i, 1).Value = "LYXOR ETF DJ STX HLTH CARE" _

Then

Feuil7.Cells(i, 1).EntireRow.Delete

i = i - 1

End If

i = i + 1

Loop

Feuil7.Activate

Feuil7.Cells(2, 2).Select

Selection = "=VLOOKUP(A2,'Feuil1'!$L$85:$V$350,9,false)"

Selection.AutoFill Destination:=Range("B2:B500")



Feuil7.Cells(2, 3).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,4,false)" '4 est le numéro de la colonne copiée sur la feuille de réf

Selection.AutoFill Destination:=Range("C2:C500")

Feuil7.Cells(2, 4).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,5,false)"

Selection.AutoFill Destination:=Range("D2:D500")

Feuil7.Cells(2, 5).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,6,false)"

Selection.AutoFill Destination:=Range("E2:E500")

Feuil7.Cells(2, 6).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,7,false)"

Selection.AutoFill Destination:=Range("F2:F500")

Feuil7.Cells(2, 7).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,8,false)"

Selection.AutoFill Destination:=Range("G2:G500")

Feuil7.Cells(2, 8).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,9,false)"

Selection.AutoFill Destination:=Range("H2:H500")

Feuil7.Cells(2, 9).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,10,false)"

Selection.AutoFill Destination:=Range("I2:I500")

Feuil7.Cells(2, 10).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,11,false)"

Selection.AutoFill Destination:=Range("J2:J500")

Feuil7.Cells(2, 11).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,12,false)"

Selection.AutoFill Destination:=Range("K2:K500")

Feuil7.Cells(2, 12).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,13,false)"

Selection.AutoFill Destination:=Range("L2:L500")

Feuil7.Cells(2, 13).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,14,false)"

Selection.AutoFill Destination:=Range("M2:M500")

Feuil7.Cells(2, 14).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,15,false)"

Selection.AutoFill Destination:=Range("N2:N500")

Feuil7.Cells(2, 15).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,16,false)"

Selection.AutoFill Destination:=Range("O2:O500")

Feuil7.Cells(2, 16).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,17,false)"

Selection.AutoFill Destination:=Range("P2:P500")

Feuil7.Cells(2, 17).Select

Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,18,false)"

Selection.AutoFill Destination:=Range("Q2:Q500")

Columns("AW:AW").Select

ActiveWindow.ScrollColumn = 41

ActiveWindow.ScrollColumn = 40

ActiveWindow.ScrollColumn = 39

ActiveWindow.ScrollColumn = 38

ActiveWindow.ScrollColumn = 37

ActiveWindow.ScrollColumn = 36

ActiveWindow.ScrollColumn = 35

ActiveWindow.ScrollColumn = 34

ActiveWindow.ScrollColumn = 33

ActiveWindow.ScrollColumn = 32

ActiveWindow.ScrollColumn = 31

ActiveWindow.ScrollColumn = 30

ActiveWindow.ScrollColumn = 29

ActiveWindow.ScrollColumn = 28

ActiveWindow.ScrollCol
0
Réponse 6 / 6
didyvine Messages postés 5 Date d'inscription jeudi 9 juillet 2009 Statut Membre Dernière intervention 3 août 2009
12 juil. 2009 à 14:25
La suite car trop long:
'FEUILLE 6

Sheets("Feuil6").Select
Columns("A:A").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
i = 2
Do Until Feuil6.Cells(i, 1) = ""
If Feuil6.Cells(i, 1) = "SUR / sous pondération" _
Or Feuil6.Cells(i, 1).Value = "ISHARES DJ STOXX 600 BKS DE" _
Or Feuil6.Cells(i, 1).Value = "Publicité & médias" _
Or Feuil6.Cells(i, 1).Value = "ISHARES DJ ST 600 INSURAN DE" _
Or Feuil6.Cells(i, 1).Value = "LYXOR ETF DJ STX HLTH CARE" _
Then
Feuil6.Cells(i, 1).EntireRow.Delete
i = i - 1
End If
i = i + 1
Loop

Feuil6.Activate
Feuil6.Cells(2, 2).Select
Selection = "=VLOOKUP(A2,'Feuil1'!$L$85:$V$350,7,false)"
Selection.AutoFill Destination:=Range("B2:B500")

Feuil6.Cells(2, 3).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,4,false)" '4 est le numéro de la colonne copiée sur la feuille de réf
Selection.AutoFill Destination:=Range("C2:C500")
Feuil6.Cells(2, 4).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,5,false)"
Selection.AutoFill Destination:=Range("D2:D500")
Feuil6.Cells(2, 5).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,6,false)"
Selection.AutoFill Destination:=Range("E2:E500")
Feuil6.Cells(2, 6).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,7,false)"
Selection.AutoFill Destination:=Range("F2:F500")
Feuil6.Cells(2, 7).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,8,false)"
Selection.AutoFill Destination:=Range("G2:G500")
Feuil6.Cells(2, 8).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,9,false)"
Selection.AutoFill Destination:=Range("H2:H500")
Feuil6.Cells(2, 9).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,10,false)"
Selection.AutoFill Destination:=Range("I2:I500")
Feuil6.Cells(2, 10).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,11,false)"
Selection.AutoFill Destination:=Range("J2:J500")
Feuil6.Cells(2, 11).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,12,false)"
Selection.AutoFill Destination:=Range("K2:K500")
Feuil6.Cells(2, 12).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,13,false)"
Selection.AutoFill Destination:=Range("L2:L500")
Feuil6.Cells(2, 13).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,14,false)"
Selection.AutoFill Destination:=Range("M2:M500")
Feuil6.Cells(2, 14).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,15,false)"
Selection.AutoFill Destination:=Range("N2:N500")
Feuil6.Cells(2, 15).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,16,false)"
Selection.AutoFill Destination:=Range("O2:O500")
Feuil6.Cells(2, 16).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,17,false)"
Selection.AutoFill Destination:=Range("P2:P500")
Feuil6.Cells(2, 17).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,18,false)"
Selection.AutoFill Destination:=Range("Q2:Q500")
Columns("AW:AW").Select
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("B:B").Select
Selection.Copy
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 41
Columns("AW:AW").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

i = 2
Do Until Feuil6.Cells(i, 1) = ""
If Feuil6.Cells(i, 49) = "" _
Or Feuil6.Cells(i, 49).Value = 0 _
Then
Feuil6.Cells(i, 1).EntireRow.Delete
i = i - 1
End If
i = i + 1
Loop


i = 2
Do Until Feuil6.Cells(i, 1) = ""

For j = 3 To 17
Feuil6.Cells(i, j + 15) = Feuil6.Cells(i, 2) * Feuil6.Cells(i, j)
Next
i = i + 1
Loop
'on aggrège enfin le risque dans un tableau synthès


i = 2
Do Until Feuil6.Cells(i, 1) = ""
For j = 34 To 48
Feuil6.Cells(2, j).Formula = "=SUM(RC[-16]:R[498]C[-16])"
Next
i = i + 1
Loop


'FEUILLE 7
Sheets("Feuil7").Select
Columns("A:A").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

i = 2
Do Until Feuil7.Cells(i, 1) = ""
If Feuil7.Cells(i, 1) = "SUR / sous pondération" _
Or Feuil7.Cells(i, 1).Value = "ISHARES DJ STOXX 600 BKS DE" _
Or Feuil7.Cells(i, 1).Value = "Publicité & médias" _
Or Feuil7.Cells(i, 1).Value = "ISHARES DJ ST 600 INSURAN DE" _
Or Feuil7.Cells(i, 1).Value = "LYXOR ETF DJ STX HLTH CARE" _
Then
Feuil7.Cells(i, 1).EntireRow.Delete
i = i - 1
End If
i = i + 1
Loop
Feuil7.Activate
Feuil7.Cells(2, 2).Select
Selection = "=VLOOKUP(A2,'Feuil1'!$L$85:$V$350,9,false)"
Selection.AutoFill Destination:=Range("B2:B500")

Feuil7.Cells(2, 3).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,4,false)" '4 est le numéro de la colonne copiée sur la feuille de réf
Selection.AutoFill Destination:=Range("C2:C500")
Feuil7.Cells(2, 4).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,5,false)"
Selection.AutoFill Destination:=Range("D2:D500")
Feuil7.Cells(2, 5).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,6,false)"
Selection.AutoFill Destination:=Range("E2:E500")
Feuil7.Cells(2, 6).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,7,false)"
Selection.AutoFill Destination:=Range("F2:F500")
Feuil7.Cells(2, 7).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,8,false)"
Selection.AutoFill Destination:=Range("G2:G500")
Feuil7.Cells(2, 8).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,9,false)"
Selection.AutoFill Destination:=Range("H2:H500")
Feuil7.Cells(2, 9).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,10,false)"
Selection.AutoFill Destination:=Range("I2:I500")
Feuil7.Cells(2, 10).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,11,false)"
Selection.AutoFill Destination:=Range("J2:J500")
Feuil7.Cells(2, 11).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,12,false)"
Selection.AutoFill Destination:=Range("K2:K500")
Feuil7.Cells(2, 12).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,13,false)"
Selection.AutoFill Destination:=Range("L2:L500")
Feuil7.Cells(2, 13).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,14,false)"
Selection.AutoFill Destination:=Range("M2:M500")
Feuil7.Cells(2, 14).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,15,false)"
Selection.AutoFill Destination:=Range("N2:N500")
Feuil7.Cells(2, 15).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,16,false)"
Selection.AutoFill Destination:=Range("O2:O500")
Feuil7.Cells(2, 16).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,17,false)"
Selection.AutoFill Destination:=Range("P2:P500")
Feuil7.Cells(2, 17).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,18,false)"
Selection.AutoFill Destination:=Range("Q2:Q500")
Columns("AW:AW").Select
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("B:B").Select
Selection.Copy
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 41
Columns("AW:AW").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

i = 2
Do Until Feuil7.Cells(i, 1) = ""
If Feuil7.Cells(i, 49) = "" _
Or Feuil7.Cells(i, 49).Value = 0 _
Then
Feuil7.Cells(i, 1).EntireRow.Delete
i = i - 1
End If
i = i + 1
Loop


i = 2
Do Until Feuil7.Cells(i, 1) = ""

For j = 3 To 17
Feuil7.Cells(i, j + 15) = Feuil7.Cells(i, 2) * Feuil7.Cells(i, j)
Next
i = i + 1
Loop
'on aggrège enfin le risque dans un tableau synthès


i = 2
Do Until Feuil7.Cells(i, 1) = ""
For j = 34 To 48
Feuil7.Cells(2, j).Formula = "=SUM(RC[-16]:R[498]C[-16])"
Next
i = i + 1
Loop



'feuil8

Sheets("Feuil8").Select
Columns("A:A").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

i = 2
Do Until Feuil8.Cells(i, 1) = ""
If Feuil8.Cells(i, 1) = "SUR / sous pondération" _
Or Feuil8.Cells(i, 1).Value = "ISHARES DJ STOXX 600 BKS DE" _
Or Feuil8.Cells(i, 1).Value = "Publicité & médias" _
Or Feuil8.Cells(i, 1).Value = "ISHARES DJ ST 600 INSURAN DE" _
Or Feuil8.Cells(i, 1).Value = "LYXOR ETF DJ STX HLTH CARE" _
Then
Feuil8.Cells(i, 1).EntireRow.Delete
i = i - 1
End If
i = i + 1
Loop
Feuil8.Activate
Feuil8.Cells(2, 2).Select
Selection = "=VLOOKUP(A2,'Feuil1'!$L$85:$V$350,10,false)"
Selection.AutoFill Destination:=Range("B2:B500")

Feuil8.Cells(2, 3).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,4,false)" '4 est le numéro de la colonne copiée sur la feuille de réf
Selection.AutoFill Destination:=Range("C2:C500")
Feuil8.Cells(2, 4).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,5,false)"
Selection.AutoFill Destination:=Range("D2:D500")
Feuil8.Cells(2, 5).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,6,false)"
Selection.AutoFill Destination:=Range("E2:E500")
Feuil8.Cells(2, 6).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,7,false)"
Selection.AutoFill Destination:=Range("F2:F500")
Feuil8.Cells(2, 7).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,8,false)"
Selection.AutoFill Destination:=Range("G2:G500")
Feuil8.Cells(2, 8).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,9,false)"
Selection.AutoFill Destination:=Range("H2:H500")
Feuil8.Cells(2, 9).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,10,false)"
Selection.AutoFill Destination:=Range("I2:I500")
Feuil8.Cells(2, 10).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,11,false)"
Selection.AutoFill Destination:=Range("J2:J500")
Feuil8.Cells(2, 11).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,12,false)"
Selection.AutoFill Destination:=Range("K2:K500")
Feuil8.Cells(2, 12).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,13,false)"
Selection.AutoFill Destination:=Range("L2:L500")
Feuil8.Cells(2, 13).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,14,false)"
Selection.AutoFill Destination:=Range("M2:M500")
Feuil8.Cells(2, 14).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,15,false)"
Selection.AutoFill Destination:=Range("N2:N500")
Feuil8.Cells(2, 15).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,16,false)"
Selection.AutoFill Destination:=Range("O2:O500")
Feuil8.Cells(2, 16).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,17,false)"
Selection.AutoFill Destination:=Range("P2:P500")
Feuil8.Cells(2, 17).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,18,false)"
Selection.AutoFill Destination:=Range("Q2:Q500")
Columns("AW:AW").Select
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("B:B").Select
Selection.Copy
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 41
Columns("AW:AW").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

i = 2
Do Until Feuil8.Cells(i, 1) = ""
If Feuil8.Cells(i, 49) = "" _
Or Feuil8.Cells(i, 49).Value = 0 _
Then
Feuil8.Cells(i, 1).EntireRow.Delete
i = i - 1
End If
i = i + 1
Loop


i = 2
Do Until Feuil8.Cells(i, 1) = ""

For j = 3 To 17
Feuil8.Cells(i, j + 15) = Feuil8.Cells(i, 2) * Feuil8.Cells(i, j)
Next
i = i + 1
Loop
'on aggrège enfin le risque dans un tableau synthès


i = 2
Do Until Feuil8.Cells(i, 1) = ""
For j = 34 To 48
Feuil8.Cells(2, j).Formula = "=SUM(RC[-16]:R[498]C[-16])"
Next
i = i + 1
Loop


'feuil9


Sheets("Feuil9").Select
Columns("A:A").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
i = 2
Do Until Feuil9.Cells(i, 1) = ""
If Feuil9.Cells(i, 1) = "SUR / sous pondération" _
Or Feuil9.Cells(i, 1).Value = "ISHARES DJ STOXX 600 BKS DE" _
Or Feuil9.Cells(i, 1).Value = "Publicité & médias" _
Or Feuil9.Cells(i, 1).Value = "ISHARES DJ ST 600 INSURAN DE" _
Or Feuil9.Cells(i, 1).Value = "LYXOR ETF DJ STX HLTH CARE" _
Then
Feuil9.Cells(i, 1).EntireRow.Delete
i = i - 1
End If
i = i + 1
Loop
Feuil9.Activate
Feuil9.Cells(2, 2).Select
Selection = "=VLOOKUP(A2,'Feuil1'!$L$85:$V$350,11,false)"
Selection.AutoFill Destination:=Range("B2:B500")

Feuil9.Cells(2, 3).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,4,false)" '4 est le numéro de la colonne copiée sur la feuille de réf
Selection.AutoFill Destination:=Range("C2:C500")
Feuil9.Cells(2, 4).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,5,false)"
Selection.AutoFill Destination:=Range("D2:D500")
Feuil9.Cells(2, 5).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,6,false)"
Selection.AutoFill Destination:=Range("E2:E500")
Feuil9.Cells(2, 6).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,7,false)"
Selection.AutoFill Destination:=Range("F2:F500")
Feuil9.Cells(2, 7).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,8,false)"
Selection.AutoFill Destination:=Range("G2:G500")
Feuil9.Cells(2, 8).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,9,false)"
Selection.AutoFill Destination:=Range("H2:H500")
Feuil9.Cells(2, 9).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,10,false)"
Selection.AutoFill Destination:=Range("I2:I500")
Feuil9.Cells(2, 10).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,11,false)"
Selection.AutoFill Destination:=Range("J2:J500")
Feuil9.Cells(2, 11).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,12,false)"
Selection.AutoFill Destination:=Range("K2:K500")
Feuil9.Cells(2, 12).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,13,false)"
Selection.AutoFill Destination:=Range("L2:L500")
Feuil9.Cells(2, 13).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,14,false)"
Selection.AutoFill Destination:=Range("M2:M500")
Feuil9.Cells(2, 14).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,15,false)"
Selection.AutoFill Destination:=Range("N2:N500")
Feuil9.Cells(2, 15).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,16,false)"
Selection.AutoFill Destination:=Range("O2:O500")
Feuil9.Cells(2, 16).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,17,false)"
Selection.AutoFill Destination:=Range("P2:P500")
Feuil9.Cells(2, 17).Select
Selection = "=VLOOKUP(A2,'feuil10'!$A$2:$R$730,18,false)"
Selection.AutoFill Destination:=Range("Q2:Q500")
Columns("AW:AW").Select
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("B:B").Select
Selection.Copy
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 41
Columns("AW:AW").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

i = 2
Do Until Feuil9.Cells(i, 1) = ""
If Feuil9.Cells(i, 49) = "" _
Or Feuil9.Cells(i, 49).Value = 0 _
Then
Feuil9.Cells(i, 1).EntireRow.Delete
i = i - 1
End If
i = i + 1
Loop


i = 2
Do Until Feuil9.Cells(i, 1) = ""

For j = 3 To 17
Feuil9.Cells(i, j + 15) = Feuil9.Cells(i, 2) * Feuil9.Cells(i, j)
Next
i = i + 1
Loop
'on aggrège enfin le risque dans un tableau synthès


i = 2
Do Until Feuil9.Cells(i, 1) = ""
For j = 34 To 48
Feuil9.Cells(2, j).Formula = "=SUM(RC[-16]:R[498]C[-16])"
Next
i = i + 1
Loop


Sheets("FEUIL2").Range("AH2:AV2").Copy
Sheets("Feuil11").Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("FEUIL3").Range("AH2:AV2").Copy
Sheets("Feuil11").Range("B3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("FEUIL4").Range("AH2:AV2").Copy
Sheets("Feuil11").Range("B4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("FEUIL5").Range("AH2:AV2").Copy
Sheets("Feuil11").Range("B5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("FEUIL6").Range("AH2:AV2").Copy
Sheets("Feuil11").Range("B6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("FEUIL7").Range("AH2:AV2").Copy
Sheets("Feuil11").Range("B7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("FEUIL8").Range("AH2:AV2").Copy
Sheets("Feuil11").Range("B8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("FEUIL9").Range("AH2:AV2").Copy
Sheets("Feuil11").Range("B9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False



End Sub
0

Discussions similaires

Comment jouer contre un ordinateur
cs_boubou45 -
GoldenEye -

3 réponses
Retour à une ligne plus haute en python
Ju -
Whismeril -

7 réponses
Dernière cellule non vide d'une colonne
cs_falafala -
Luigi13013 -

5 réponses
Couleur de fond d'une cellule excel
lorre0041 -
isophie -

7 réponses
Création d'une roue de la chance
cs_qveys -
BBFUNK01 -

5 réponses
Rejoignez-nous