Optimisation boucle if VBA

Question

J'essaie de construire une macro sur ma feuille active me ramenant les valeurs de mes fonds presente sur une autre feuille en fonction du choix de l'identifiant que j'aurais fait dans ma Liste deroulante. j'ai utiliser la boucle if qui marche mais le problème est que j'ai plus de 190 fonds dans ma Liste déroulante et que ca me conduira à boucler à l'infini. Existe-t-il une methode me permettant d'optimiser mon programme pour contourner ce problème? voici le code que j'ai utilisé: Sub Remplir() ' feuille de calcul ' attribue des valeurs aux champs en fonction du choix du ticker dans la liste deroulante If Cells(1, 2).Value = "TPXH FP Equity" Then Range("B2").Value = Sheets("sheet1").Range("N17").Value Range("B3").Value = Sheets("sheet1").Range("H17").Value Range("D5:D900").Value = Sheets("sheet2").Range("A5:A900").Value Range("E5:E900").Value = Sheets("sheet2").Range("B5:B900").Value Range("F5:F900").Value = Sheets("sheet2").Range("C5:C900").Value Range("I5:I900").Value = Sheets("sheet2").Range("A5:A900").Value Range("J5:J900").Value = Sheets("sheet2").Range("G5:G900").Value Range("K5:K900").Value = Sheets("sheet2").Range("H5:H900").Value Range("P5:P900").Value = Sheets("sheet2").Range("K5:K900").Value Range("Q5:Q900").Value = Sheets("sheet2").Range("L5:L900").Value Range("S5:S900").Value = Sheets("sheet2").Range("N5:N900").Value Range("T5:T900").Value = Sheets("sheet2").Range("O5:O900").Value Range("W5:W900").Value = Sheets("sheet2").Range("R5:R900").Value Range("X5:X900").Value = Sheets("sheet2").Range("S5:S900").Value Range("Y5:Y900").Value = Sheets("sheet2").Range("T5:T900").Value Range("AB5:AB900").Value = Sheets("sheet2").Range("W5:W900").Value Range("AC5:AC900").Value = Sheets("sheet2").Range("X5:X900").Value Range("AD5:AD900").Value = Sheets("sheet2").Range("Y5:Y900").Value Range("AE5:AE900").Value = Sheets("sheet2").Range("Z5:Z900").Value Range("AF5:AF900").Value = Sheets("sheet2").Range("AA5:AA900").Value Else If Cells(1, 2).Value = "EEA FP Equity" Then Range("B2").Value = Sheets("sheet1").Range("N30").Value Range("B3").Value = Sheets("sheet1").Range("H30").Value Range("D5:D900").Value = Sheets("sheet2").Range("AE5:AE900").Value Range("E5:E900").Value = Sheets("sheet2").Range("AF5:AF900").Value Range("F5:F900").Value = Sheets("sheet2").Range("AG5:AG900").Value Range("I5:I900").Value = Sheets("sheet2").Range("AJ5:AJ900").Value Range("J5:J900").Value = Sheets("sheet2").Range("AK5:AK900").Value Range("K5:K900").Value = Sheets("sheet2").Range("AL5:AL900").Value Range("P5:P900").Value = Sheets("sheet2").Range("AO5:AO900").Value Range("Q5:Q900").Value = Sheets("sheet2").Range("AP5:AP900").Value Range("S5:S900").Value = Sheets("sheet2").Range("AR5:AR900").Value Range("T5:T900").Value = Sheets("sheet2").Range("AS5:AS900").Value Range("W5:W900").Value = Sheets("sheet2").Range("AV5:AV900").Value Range("X5:X900").Value = Sheets("sheet2").Range("AW5:AW900").Value Range("Y5:Y900").Value = Sheets("sheet2").Range("T5:T900").Value Range("AB5:AB900").Value = Sheets("sheet2").Range("AX5:AX900").Value Range("AC5:AC900").Value = Sheets("sheet2").Range("BA5:BA900").Value Range("AD5:AD900").Value = Sheets("sheet2").Range("BB5:BB900").Value Range("AE5:AE900").Value = Sheets("sheet2").Range("BC5:BC900").Value Range("AF5:AF900").Value = Sheets("sheet2").Range("BD5:BD900").Value Else MsgBox " il n'y a pas de correspondance" End If End If End sub Je vous remercie de tout coeur pour l'aide que vous m'accorderai. cordialement...

dhany · Accepted Answer

Bonjour Laetitia YAO , le forum, toutes les cellules d'une feuille de calcul sont verrouillées par défaut ; donc déverrouille les cellules où tu dois saisir des données et protège la feuille de calcul : tes formules étant dans les cellules verrouillées, elles ne seront pas modifiables, et protégées contre tout écrasement accidentel. pour déverrouiller une cellule, fais ceci : clic droit dessus, choisis « Format de cellule » ; onglet Protection ; c'est là : pour protéger la feuille, c'est sur l'onglet Révision , groupe Modifications : Protéger la feuille ⚠ c'est facultatif de mettre un mot de passe ; si tu en mets un, attention de ne pas l'oublier ! dhany...

CindyD · Answer

Je pense qu'une boucle IF n'est pas la plus adaptée. Pour vos 2 premières valeurs sur l'onglet 1, vous pouvez utiliser un RECHERCHEV. Pour les valeurs suivantes, vous pouvez d'abord faire une recherche sur la ligne 3 d'où se trouve vos valeurs, puis faire des sélections en vous décalant de cette case-là à droite et en bas (dans ce cas, il est plus facile d'utiliser les sélections Cells(ligne,colonne) ). Cindy...

MFerrand · Answer

If... &#91;ElseIf...&#93; &#91;Else...&#93; End If n'a rien à voir avec une instruction de boucle, il s'agit d'une instruction conditionnelle (dont les éléments mis entre crochets sont des éléments optionnels). Il n'y a aucune boucle dans ton code. Erreur d'aiguillage... Et ton instruction conditionnelle est mal utilisée, tu ignores visiblement l'existence de ElseIf qui te permettrait de l'écrire sans imbrication. NB - Et mets ton code sous balises Code ! A cetteheure, je me refuse de lire un code non placé sous balises, et s'il n'est pas indenté... Cordialement...

MFerrand · Answer

Tu en traites 2 dans ta macro, et tu les indiques en dur ! Il n'y a donc aucune dépendance avec un choix dans une liste.

Si ta macro (la moitié, concernant un ticker), concerne ce qu'il y a lieu de faire suite à un choix dans la liste, Il faut recueillir le ticker à traiter dans une variable, chercher la zone de données correspondante (je n'ai pas encore assez regardé le détail de ton fichier pour ça, mais cela devrait pouvoir se faire facilement), voir aussi comment on peut simplifier ton énumération (là avec des boucles...), et tu auras alors une procédure qui te feras le remplissage selon choix.

Selon ce que j'ai vu, chaque fois que tu remplis, cela se substitue au remplissage précédent ?

Si pas d'autres éléments en prendre en considération, je peux regarder ça après manger... pour l'instant je bascule en cuisine !

Cordialement.

MFerrand · Answer

Re,

Inutile de répondre en citant à chaque fois, ça ne fait qu'allonger inutilement les posts. Quand tu penses utile de citer, tu cites juste ce qui est utile....

J'attendais ton retour, j'aimerais savoir pourquoi dans ta feuille cible tu fais un saut de 5 colonnes, entre K et P, alors qu'il n'y a qu'un saut de 3 dans la feuille source pour le même emplacement, et que partout ailleurs, le nombre de colonnes sautées est le même ?

Et d'autre part à quoi servent les indications parasites dans les intercolonnes de la feuille source ?

Un code à rallonge n'est jamais ce qu'on peut faire de plus efficace.

Il ne t'échappera pas que ton opération couvre au total 27 colonnes, et que affecter les valeurs de ces 27 colonnes en seule fois, sera nettement plus pratique et plus rapide que de faire colonne par colonne !

Cordialement.

MFerrand · Answer

Voilà pour ce qui est du report des valeurs (bien que l'intérêt de l'opération me paraisse limité, car ces données existant, on peut fort bien les utiliser pour en tirer ce que l'on souhaiter sans avoir à les reproduire...) Sub Remplir() Dim tckr$, ln%, k%, wsS As Worksheet Set wsS = Worksheets("Sheet2") With ActiveSheet tckr = .Range("B1") If tckr = "" Then Exit Sub ln = WorksheetFunction.Match(tckr, &#91;Tickers&#93;, 0) On Error Resume Next k = WorksheetFunction.Match(tckr, wsS.Rows(3), 0) If Err.Number &lt;&gt; 0 Then MsgBox "Le ticker " &amp; tckr &amp; " ne figure pas dans la feuille source !", _ vbCritical, "Ticker non trouvé" Exit Sub End If On Error GoTo 0 .Range("B2") = &#91;Tickers&#93;.Cells(ln, 13) .Range("B3") = &#91;Tickers&#93;.Cells(ln, 7) .Range("D5").Resize(896, 27).Value = wsS.Cells(5, k).Resize(896, 27).Value End With End Sub Après avoir supprimé 2 colonnes parasites entre K et P sur Sheet3, et avoir nommé Tickers la plage B5:B277 de Sheet1, sous cette forme : =DECALER(Sheet1!$B$8;;;NBVAL(Sheet1!$B$8:$B$277)) qui élimine les cellules vides incluses dans la plage, en conservant la possibilité d'extension. Nom à mettre en validation de la cellule B1 de Sheet3. Je ne te suivrai pas sur la suite de ton code... D'abord, parce que je considère comme une des plus grandes absurdités de bourrer de formules une feuille en utilisant VBA, il est plus facile et plus rapide d'insérer des formules en manuel, et on ne le fait qu'une fois car elles ne bougeront plus ensuite. Je n'empêche personne de le faire mais je m'abstiens toujours de réaliser quelque chose que je trouve absurde. Si on utilise VBA, l'intérêt est de mettre les résultats en se passant de formules, cela encombre beaucoup moins. Ensuite parce que certaines me poseraient question, ou me paraissent douteuses, mais c'est une autre histoire... Ensuite toujours, parce que si le but est d'opérer une analyse (je n'ai pas regardé assez pour m'en rendre compte), ce qu'il fallait bâtir c'est un programme d'analyse, opérant sur les données récupérées sous forme de tableaux (pour éviter de travailler sur le tableur et gagner ainsi en rapidité), et fournir les résultats souhaités sous la forme souhaitée à la fin, sans opérer de duplication préalable des données. Cordialement...

MFerrand · Answer

Pour ta question portant sur la protection des formules, on protège généralement la feuille de façon à les protéger... Mais je vois que Dhany t'a fourni avec tous les détails nécessaires comment procéder pour ce faire... Je n'y reviens donc pas. Mais dans la mesure où c'est la seule question qu'a soulevé ma réponse, je ne peux que rester sur l'impression que tu ne m'as pas complètement lu, et/ou que tu n'as pas compris un certain nombre de mes propos. Ce que j'ai essayé de faire, c'est te montrer qu'un code de volume réduit, et précis, pouvait opérer ton opération de transfert de données, et comment on y parvenait avec des adaptations minimes (noms de plage, correspondances de colonnes, etc.). Mais l'existence de nombreuses formules sur la feuille laissaient penser que l'objectif était était d'opérer une analyse des données afin d'en faire apparaître certains résultats... Alors, à ce stade, d'une part j'ai fait état de réticences vis à vis de tes formules, que je peux rapidement epliciter sur deux points : elles shuntent généralement les dernières lignes de tes données, ce qui n'est pas compréhensible, celles-ci couvrant une plage fixe de la ligne 5 à la ligne 900 (si le 900 est susceptible de varier, il conviendrait d'ailleurs de prendre les dispositions pour prendre en compte ces variations), et également lorsque je vois un calcul de moyenne visant à éliminer du calcul des éléments qui ne te conviennent pas, je m'étrangle ! La statistique obéit à des règles précises et une moyenne biaisée perd toute signification au regard des règles que tu semblais initialement appliquer (moyenne de la distribution ± 1,96 écart-type, définissant un intervalle de confiance à 95% dans la mesure où la distribution suit une loi Normale...) Ces réticences mises à part, et dans la mesure où tu sais avec assez de précision quelle analyse tu opères sur les données, et quels résultats significatifs tu souhaites obtenir, la solution de traitement qui devrait à mon sens être la plus performante, consisterait à ne pas tranférer les données déjà : on les prélève sous forme de tableau, et on travaille ensuite sur ce tableau, hors Excel. Puis on suit un protocole d'analyse pour procéder aux calculs souhaités et dégager les résultats sous la forme la plus opératoire par rapport à l'utilisation que l'on souhaite en faire, et on produit en fin de compte ces résultats ! Cordialement et bon weekend...

dhany · Answer

Merci pour ton retour ! je te souhaite bonne continuation ! dhany...

MFerrand · Answer

Quand je parle de travailler hors Excel, pour expliciter sommairement, reprends la macro que j'ai fournie. A la dernière ligne on opère l'affectation des valeurs de la plage source à la plage cible (après avoir défini l'emplacement de cette plage source en recherchant la colonne où elle commence). Elle est ainsi définie : wsS.Cells(5, k).Resize(896, 27).Value Si au lieu de l'affecter à une plage de feuille Excel, tu l'affectes à une variable : Tbl = wsS.Cells(5, k).Resize(896, 27).Value L'affectation des valeurs d'une plage à une variable de type Variant produit un tableau à 2 dimensions de base 1. Tu disposes alors d'un tableau de tes données, de 896 lignes et 27 colonnes dont la première ligne est 1 et la première colonne est 1. Tu peux alors travailler sur ce tableau, directement en mémoire, de façon plus rapide qu'en allant chercher chaque fois les données sur feuille, sans écrire de résultats (tu les consignes dans des variables tableau ou non selon le cas). Et tu ne restitues sur feuille à la fin que les résultats dont tu as besoin. Ceci est très sommairement vu, mais te donne le principe. Tes données source étant clairement définies, dès lors que tu sais exactement quels résultats tu veux obtenir à l'arrivée, et comment tu les obtiens par traitement de telle ou telle partie des données, cela te permet de définir tes variables de sortie, et tes variables d'entrée, lesquelles peuvent être le cas échéant plus utilement constituées par plusieurs tableaux au lieu d'un seul englobant toutes les données. Tu appliques alors les calculs prévus sur les variables d'entrée pour remplir les variables de sortie... Cela demande du travail pour tout définir, car il te faut tout prévoir et ne rien laisser au hasard, mais une fois réalisé tu obtiendras tes résultats quasi-instantanément. Cordialement...