Visual Basic, VB6, VB.NET, VB 2005, VB CodeS-SourceS

CodeS-SourceS est hébergé par Frontier.fr

Ce site au démarrage

begin process at 2012 02 15 11:49:04

Accueil Codes Tutoriels Forum Livres Emploi Services Connexion

Trouver un code source :

dans

[ Dernières recherches ]

Accueil > Forum >

Echec suppression ligne sous condition de la premiere cellule

Poser une question dans le forum ou lancer une discussion

Echec suppression ligne sous condition de la premiere cellule

jeudi 9 juillet 2009 à 23:08:57 | Echec suppression ligne sous condition de la premiere cellule

didyvine

7 msg

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.

vendredi 10 juillet 2009 à 00:11:49 | Re : Echec suppression ligne sous condition de la premiere cellule

pile_poil

672 msg

700 msg CS

Réponse acceptée !
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

si c'est la solution, penser : REPONSE ACCEPTEE

vendredi 10 juillet 2009 à 00:31:45 | Re : Echec suppression ligne sous condition de la premiere cellule

didyvine

7 msg

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

vendredi 10 juillet 2009 à 01:08:30 | Re : Echec suppression ligne sous condition de la premiere cellule

pile_poil

672 msg

700 msg CS

Réponse acceptée !
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é
si c'est la solution, penser : REPONSE ACCEPTEE

vendredi 10 juillet 2009 à 01:19:53 | Re : Echec suppression ligne sous condition de la premiere cellule

pile_poil

672 msg

700 msg CS

Réponse acceptée !
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

si c'est la solution, penser : REPONSE ACCEPTEE

dimanche 12 juillet 2009 à 12:48:21 | Re : Echec suppression ligne sous condition de la premiere cellule

didyvine

7 msg

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

Pour le conseil sur les ActiveWindow.ScrollColumn = 1, çà ne marche pas non plus, il faut dire que j'ai triché avec le code, en enregistrant une macro puis en recopiant le code qui avait été crée... Même chose pour le code qui est sensé trier ma colonne A et qui ne marche qu'1 fois sur deux:

Sheets("Feuil2").Select

    Columns("A:A").Select

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

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

        DataOption1:=xlSortNormal

dimanche 12 juillet 2009 à 14:25:19 | Re : Echec suppression ligne sous condition de la premiere cellule

didyvine

7 msg

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

Cette discussion est classée dans : cells, feuil2, entirerow, if, then

Répondre à ce message

Sujets en rapport avec ce message

Créer un If [ par stephx06 ] Bonjour tout le monde j'ai une petite question. Je dois remettre les mains dans un code que j'ai créer il y a un an... c'était le premier et le dernie [Déplacé VB6 --> VBA] probleme de debogage [ par bary59174 ] bary bonjour a tous, je suis débutant dans ce domaine et j'ai un petit problème de débogage plus exactement de variable. Mais je ne sais pas d'où cel [Incompatibilité de type] If...or...or.... [ par egeiL ] Problème avec les conditions d'un « If » Voici la partie du code qui pose problème If Cells(lignedata, 1) = "M20" Then If Cells(lignedata, 2) = "2 VBA Sudoku [ par johnny78 ] hello toutlemonde bienvenue dans le monde des noobs du VBA ! voila mon soucis: je pensais qu'en utilisant une fonction je pouvais reutiliser le meme m pb variable comparaison nom prénom [ par Mathioustone ] Bonjour à tous, J'ai un soucis de type, d'argument ou d'objet lorsque je souhaite utiliser cette fonction. J'ai deux colonnes H et I qui définissent r valeur comprise entre différentes heures [ par maxxx08 ] Bonjour tout le monde, je travaille sous excel 2003. J'aimerai à l'aide de ma Macro pouvoir comparer le jour de la semaine se trouvant dans la colon AIDE modification code [ par Angecedric ] Bonjour à tous, Depuis plusieur semaine j'ai modifié un programme pour la gestion de salle de réunion sous EXCEL. Après la création d'une salle, l'ut Impossible de trouvé la solution à mon PB, besoin d'expert lol [ par Angecedric ] Bonjour à tous, Depuis plusieur semaine j'ai modifié un programme pour la gestion de salle de réunion sous EXCEL. Après la création d'une salle, l' Erreur récurante de référence d'objet. [ par whombat ] Bonjour à tous, Je cherche depuis plus de 24h mais je sèche. Pour moi tout est bon mais le déboger me dit : 'La référence d'objet n'est pas définie à

Livres en rapport

Tous les livres pour Cells, Feuil2, Entirerow, If, Then

Nos sponsors

Derniers Blogs

GESTION D'EXCEPTION AVEC LES TASKS par richardc

DéMARRONS AVEC LES TASKS par richardc

SLIDE & DéMO TECHDAYS 2012 - FAST & FURIOUS XAML APPS par Vko

XNA IS DEAD! par richardc

TECHDAYS PARIS 2012 : WINDOWS SERVER "8" QUOI DE 9 ! par ROMELARD Fabrice

Forum

RE : [VBA EXCEL] FACEID/ICONE par ucfoutu

FILTRE SUR COLONNE ET COPIE par sellaction

RE : APPLICATION DE GESTION DES VENTES. par vjija147

RE : [VBA EXCEL] FACEID/ICONE par Le Pivert

RE : LISTVIEW TAILLE par ucfoutu

Côté IT

Offres d'emploi

Formations Video

Appels d'offres

Simulateur de gravure flash pour medailles avec inscriptions
Budget : 4 000€
Creation d un bandeau publicitaire
Budget : 200€
Realisation site web - gestionnaire de demande de stages
Budget : 10 000€

Logiciels

DocTranslate (V3.1.0.0)
DOCTRANSLATE (V3.1.0.0)

DocTranslate est un traducteur de document Microsoft Word, PowerPoint et Excel. Il permet d'autom...

Cliquez pour télécharger DocTranslate
Tribler (2012)
TRIBLER (2012)

Tribler est un client pair à pair (P2P/Peer-to-Peer) open source avec la capacité de regarder des...

Cliquez pour télécharger Tribler
OneSwarm (2012)
ONESWARM (2012)

Le peer-to-peer qui protège votre vie privée, c'est OneSwarm. Ce logiciel de peer-to-peer crypté...

Cliquez pour télécharger OneSwarm
PONAMEDIA PREMIUM - HELLLOOO FLASH DEMO (V8.4)
PONAMEDIA PREMIUM - HELLLOOO FLASH DEMO (V8.4)

PONAMEDIA TV DEVIENS HELLLOOO FLASH LA TV SUR VOTRE ORDINATEUR. Toute une plateforme Multi...

Cliquez pour télécharger PONAMEDIA PREMIUM - HELLLOOO FLASH DEMO
Academy System (17.2.1.0)
ACADEMY SYSTEM (17.2.1.0)

Logiciel de gestion des établissements. - élèves/étudiants (inscription, dossier, absence...) -...

Cliquez pour télécharger Academy System