Création boucle for pour remplacer une sérier de if

Bonjour bonjour !

j'ai écrit un code permettant de faire une comparaison de contenu de cellule avec un enchaînement de IF - ELSE et je cherche à passer en boucle for pour alléger le code ...

J'ai écrit cet enchaînement :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

Function module(number As Range)   If InStr(1, number, Sheets("Modules").Range("A3").Value, Compare) >= 1 Then module = "type 1" Else If InStr(1, number, Sheets("Modules").Range("A4").Value, Compare) >= 1 Then module = "type 1" Else If InStr(1, number, Sheets("Modules").Range("A5").Value, Compare) >= 1 Then module = "type 1" Else If InStr(1, number, Sheets("Modules").Range("A6").Value, Compare) >= 1 Then module = "type 1" End If End If End If End If

---

j'aimerai passer à un truc du style :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14

Function module( number As Range)   Application.Volatile Dim Active_cell As String     For i = 3 To 6 Active_cell = Sheets("Modules").Cells(A, i).Value If InStr(1, number, Active_cell, Compare) >= 1 Then module = "type 1" Else module = "autre version" End If Next

---

Seulement la fonction me retourne uniquement le message #VALEUR alors que mes If fonctionnent ..

sauriez vous comment faire ?

Merci en avance !! :)

Bonjour,
Tu confonds nom d'un module et paramètre à passer à un module
Si tu as ou une procédure 'Function ou Sub) ayant comme paramètre Number, c'est la valeur à passer à Number que tu dois définir dans ta structure conditionnelle (If...Then...Else ou Select Case)

Je n'ai pas compris la réponse

j'ai l'impression de respecter la forme des instructions que j'avais dans les IF et pourtant le for ne fonctionne pas (j'ai l'impression qu'il n'est même pas démarré)

Bonjour,
Pour t'apporter une réponse pertinente, il faudrait donner un ou deux exemples concrets sans ligne de code. Par exemple si le nom de ma feuille contient telle valeur contenant 4 j'aimerais appeler telle procédure ou renvoyer telle valeur, etc.

Personnellement, je n'utilise jamais le ElseIf qui pour moi est rapidement indigeste. Je privilégie l'usage du Select Case

Je vous ai mis un fichier qui me sert de test pour ma fonction en PJ avec le code (indigeste) en série de IF :)

L'idée est a terme d'afficher tous les types d'ou le besoin de passer en for sinon cela sera carrément pire qu'indigeste ... Pour cela j'aimerai voir comment en gérer un avec une boucle puis je l'étendrai.

Merci en avance

Bonjour,
Désolé, sauf cas exceptionnel je n'ouvre pas les fichiers joints. Cela ne doit pas être très compliqué d'expliquer en deux trois phrases ce que l'on souhaite obtenir.

En attendant, voici le lien vers un tutoriel sur le sujet La gestion des boucles dans Excel

Je comprends mais ce n'est pas si simple a expliquer ...

ma fonction récupère la valeur d'une cellule en argument d'entrée. Cette cellule est la cellule appelée number. Ensuite elle compare cette valeur à une cellule dans la feuille "Modules" en A3. Si le contenu est identique il faut écrire "type1" dans la cellule ou la fonction a été appelée. Si non, il faut comparer à Sheets("Modules").A4 et ainsi de suite jusqu'à A6. Si rien ne matche alors il faut écrire "autre type".

J'espere que c'est compréhensible :)

Bonjour,
D'après ma compréhension, une proposition

Code:

---

1 2 3 4 5 6 7 8 9

Function CompareCells(Number As Range, CellRange As Range) As String Dim c As Range Dim flag As Boolean For Each c In CellRange If Not flag Then flag = Number.Value = c.Value Next CompareCells = IIf(flag, "Type1", "Autre type") Set c = Nothing End Function

---

Dans la cellule d'excel

Code:

---

=CompareCells(A2;Module!A2:C2)

---

Si la valeur contenue en A2 de la feuille où se trouve la fonction est égale à la valeur contenue dans l'une des cellules A2 à C2 de la feuille nommée Module, la fonction renverra Type1 dans le cas contraire Autre type

Attention, dans cet exemple la comparaison est sensible à la casse

Votre code fonctionne mais par contre la fonction IIF renvoie "autre type" et je ne sais pas comment faire pour qu'à la place elle fasse un deuxième test avec une autre CellRange pour identifier les types 2 et ainsi de suite.

Si je recopie le code pour le type 2, la fonction effacera les types 1 lors de son exécution ..

Avez vous des idées ?

Bonjour,

Code:

---

1 2 3 4 5 6

Version type 1 type 2 type 3 type 4 900-20 900-32 900-98 900-56 900-21 900-99 900-22 900-23

---

Ce serait plus simple si tu les gérais en colonne

Code:

---

1 2 3 4 5 6 7 8 9

Number version 900-20 type 1 900-21 type 1 900-22 type 1 900-23 type 1 900-32 type 2 900-98 type 3 900-99 type 3 900-56 type 4

---

et rien ne t'empêcherait de faire un TCD (ton type de tableau d'origine)


Est-ce possible ?

Igloobel : Malheureusement non car le fichier est très très simplifié par rapport au classeur sur lequel la fonction sera vraiment utilisée!

Philippe : J'ai réussi à adapter votre code à mon problème en enregistrant les valeurs dans une variable de sauvegarde que je remets si le type suivant n'est pas trouvé :)

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

Function CompareCells(Number As Range, CellRange1 As Range, CellRange2 As Range, CellRange3 As Range, CellRange4 As Range) As String Dim c As Range Dim PREVIOUS As String Dim flag As Boolean Dim flag2 As Boolean Dim flag3 As Boolean   For Each c In CellRange1 If Not flag Then flag = Number.Value = c.Value Next CompareCells = IIf(flag, "type1", "") Set c = Nothing   For Each c In CellRange2 If Not flag2 Then flag2 = Number.Value = c.Value Next PREVIOUS = CompareCells CompareCells = IIf(flag2, "type2", PREVIOUS) Set c = Nothing   For Each c In CellRange3 If Not flag3 Then flag3 = Number.Value = c.Value Next PREVIOUS = CompareCells CompareCells = IIf(flag3, "type3", PREVIOUS) Set c = Nothing   For Each c In CellRange4 If Not flag4 Then flag4 = Number.Value = c.Value Next PREVIOUS = CompareCells CompareCells = IIf(flag4, "type4", PREVIOUS) Set c = Nothing   End Function

---

Par contre il y a un soucis, votre code cherche à trouver une correspondance exacte ... je cherche a savoir si CellRange se trouve dans une partie de number.

Exemple : CellRange = 900-21 et number = 900-21-23-65-98

900-21 est commun au début des deux donc number est type 1 , etc

Bonjour …

La fonction agit pour les lignes une par une donc la boucle ne conviendra que si on prévoit une sortie anticipée (avant le Next)
Afin d’éviter des mots réservés, avec ton exemple , une proposition entre autres:

Code:

---

1 2 3 4 5 6 7 8

Function M(R As Range) As String Dim C As Range M = "type 1" For Each C In Feuil2.[A3:A6] M = "autre type" If InStr(1, R, C) > 0 Then M = "Type_1": Exit For Next End Function

---

Salut.

Perso, je n'aime pas les Exit. Je préfère utiliser la bonne boucle, selon ce que le code doit réaliser.

Il y a deux sortes de boucles: les boucles For et les boucles DO.

Les boucles For ont été créées pour boucler sur CHAQUE élément, de sorte qu'il ne devrait normalement pas y avoir de sortie via Exit.

Les boucles DO ont été créées justement lorsqu'il ne s'agit pas de passer sur tous les éléments, mais de quitter la boucle dès qu'une condition n'est plus remplie (While) ou dès qu'une condition est remplie (Until). La aussi, il ne devrait pas y avoir d'Exit, mais une expression booléenne qui permet la sortie de boucle.

OrDonc

Ça ne fonctionne pas j'ai le retour VALEUR! De plus, comme je le disais plus haut il faut que la comparaison trouve une partie de CellRange1 dans number pour retourner type1 ou une partie de CellRange2 pour afficher type2, etc ...

L'exemple de philippe que j'ai allongé dans un message précédent fonctionne uniquement si les cellules comparées sont identiques .. je suis près du but il faut juste adapter son code afin de détecter une correspondance telle que dans l'exemple :
CellRange1 = 900-21 et number = 900-21-23-65-98

900-21 est commun au début des deux donc number est type 1

CellRange2 = 900-22 et number = 900-22-26-79-99 donc type2= 2

Perso, je doute qu'une boucle (forcément lente) soit nécessaire. Un EQUIV (MATCH en anglais) pourrait probablement suffire.

Dès lors

• Que cherches-tu réellement à réaliser?
• Comment ton classeur est-il construit?

J'ai une Feuille appelée Modules.

elle contient 4 colonnes représentants des types

type 1 :
900-21
900-22
900-23

type 2 :
900-32

type 3 :
900-98
900-99

type 4 :
900-56


Sur l'autre feuille appelée traitement j'ai une colonne numéro.
Elle contient des numéros de série complets

900-21-45-45-54-54
900-22-76-45-78-13
900-23-47-85-96-12
900-56-36-52-98-41
900-99-85-63-92-37 par exemple.

La fonction doit regarder si 900-21 est compris dans le numéro de série. Si oui elle écrit type1. Si non elle regarde si 900-22 est compris dedans. Si oui alors type 1. Si non, elle regarde si 900-23 est compris dedans. Si oui alors type 1. Si non, elle regarde si 900-32 est compris et écrit type 2 si il l'est sinon elle passe à la suite.

Ceci est un exemple. Ma fonction doit parcourir une 30aine de lignes par type.

Mon classeur est construit comme ceci (PJ)

Au départ, pas besoin de VBA pour cela, mais bien de INDEX/EQUIV.

Dans l'illustration qui suit, j'ai un tableau T_Types (celui de gauche) qui contient les débuts de code et les types associés. Dans le tableau de droite, on recherche le type correspondant au début du code.

Si on doit le faire en VBA, il suffit d'utiliser la formule en VBA.

Pièce jointe 523616

En 2016, ça peut se réaliser avec Power Query sans aucune formule, si l'on accepte de récupérer les lignes de résultat dans un nouveau tableau.

Je vais essayer mais je doute que ça réponde à mon problème en finalité.

Bonjour à tous,

Oui Pierre si c'était organisé en colonne pas de problème mais c'est pas le cas

dans mon Post j'ai présenté le tableau qu'il a dans son classeur en disant qu'il n'était pas en colonne (dommage)


@ Simon5713

pour une meilleure visibilité et parce que beaucoup n'ouvre pas les classeurs fait des captures écrans de tes feuilles

Citation:

---

Envoyé par Philippe Tulliez

[...]
Personnellement, je n'utilise jamais le ElseIf qui pour moi est rapidement indigeste. Je privilégie l'usage du Select Case

---

Si je peux me permettre...

ElseIF et Select Case ne sont pas interchangeables. On utilisera ElseIf lorsque l'on doit tester des conditions sur des expressions différentes, et Select Case lorsque l'on devra tester plusieurs valeurs d'une même expression.

Code:

---

1 2 3 4 5 6 7 8 9 10

If X= ... then ... ... ElseIF Y = ... then ... ... Else ' aucune des deux conditions remplies ... ... End If

---

Code:

---

1 2 3 4 5 6 7 8 9 10 11

Select Case Expression Case Valeur1 ' sous-entendu Expression = Valeur1 ... ... Case Valeur2 ' sous-entendu Expression = Valeur2 ... ... Case Else ' sous-entendu Expression <> Valeur1 ET expression <> Valeur2 ... ... End Select

---

La construction Select Case permet d'éviter la répétition de l'expression lorsque les tests successifs s'appuient sur la même expression. C'est donc, dans un cas particulier, une autre façon d'écrire les If... ElseIf...


Le code Select Case vu plus haut pourrait s'écrire avec des If.. ElseIf, mais oblige alors à répéter l'expression (qui pourrait être autre chose qu'une simple variable)

Code:

---

1 2 3 4 5 6 7

If Expression = Valeur1 then ... ElseIf Expression = Valeur2 then ... Else ... End If

---

Ma remarque rejoint celle sur les boucles dont je parlais tout à l'heure. Chaque type de boucle a sa raison d'être et normalement, même si le résultat peut revenir au même, on ne les interchange pas (même si on peut le faire sur le plan technique) et on les utilise à bon escient. Les constructions If... ElseIf et Select Case ont leur raison d'être et doivent être utilisées pour ce pourquoi elles ont été créées, et ce n'est que dans certains cas particuliers qu'on peut (techniquement) les interchanger. Je pense personnellement qu'il est important d'u, bien que pense qu'il ne faut pas le faire si on veut écrire du beau code et qu'il faut utiliser les constructions de code en fonction de ce que l'on souhaite réaliser, pour écrire un code lisible et qui permet au lecteur du code (qui est peut-être la même personne que celle qui va le maintenir) de comprendre l'intention et la finalité du code produit.

My 2 cents ;)

Citation:

---

Envoyé par Igloobel

[...]
Oui Pierre si c'était organisé en colonne pas de problème mais c'est pas le cas [...]

---

Dans ce cas, la première chose à faire est de réorganiser en colonnes (comme tu le préconisais). On ne construit pas un code sur un classeur mal conçu. Il est moins coûteux de remettre le classeur d'équerre que de construire du code sur une conception qui ne tient pas la route (considération technique et pas du tout une attaque personnelle). On est toujours gagnant à procéder ainsi.

Avec la conception que je propose, on peut ajouter n'importe quelle racine de code sans RIEN MODIFIER à la structure du classeur ou au code VBA qui manipule le classeur. Avec des colonnes différentes par type, on est obligé de modifier le code VBA pour tenir compte des nouvelles colonnes lorsque de nouveaux type apparaîtrons, sauf à monter une usine à gaz en VBA.

Si l'on n'est pas responsable des données que l'on reçoit ni de leur agencement, on est responsable de la façon dont on les traite, et rien n'empêche de les remettre d'équerre avant de les traiter et de les analyser.

En procédant comme je le propose, on se donne la possibilité de profiter des outils natifs d'Excel (TCD, Power Query, Formules, ...) alors qu'en s'appuyant sur une conception fragile car non conforme, on s'oblige à créer du code qui lui aussi, sera inévitablement fragile.

Et on peut même utiliser la formule =RECHERCHEV() à condition que la liste des données soit dans l'ordre.

si la liste est triée comme ceci

Number	version
900-20	type 1
900-21	type 1
900-22	type 1
900-23	type 1
900-32	type 2
900-56	type 4
900-98	type 3
900-99	type 3

alors la formule suivante fonctionne parfaitement (j'ai testé et repris ton mini tableau Pierre)

=RECHERCHEV(E2;$A$2:$B$9;2) (avec valeur proche)

Il n'est pas nécessaire que les données soient dans l'ordre. Il suffit d'utiliser Faux comme quatrième paramètre de Recherchev. Mais je préfère index-equiv (voir mon billet de blog à ce sujet).

Citation:

---

Envoyé par Pierre Fauconnier

Il n'est pas nécessaire que les données soient dans l'ordre. Il suffit d'utiliser Faux comme quatrième paramètre de Recherchev. Mais je préfère index-equiv

---

Ah je suis désolé de te contredire mais vu que l'on a pas les même valeurs dans la colonne A et E il faut bien faire une recherche avec les valeurs proches et donc Vrai (ou omis) et donc trié.

mais cela n'enlève en rien le fait Index-Equiv c'est très bien aussi !

bonne fin de journée

Valeur proche, que ce soit avec RechercheV ou avec Equiv, sert à trouver la ligne de la plage à laquelle appartient une valeur (en recherchant la valeur inférieure la plus proche). On n'est pas dans ce cas ici, car on cherche la valeur exacte et on doit mettre FAUX, ce qui permet à la liste de ne pas être triée. La solution que je proposais avec Index/Equiv utilisait aussi la valeur exacte (troisième paramètre de Equiv = 0).

Dans le cas présent, il serait même préjudiciable d'utiliser la valeur proche car, en cas de mauvais encodage du code, on pourrait se retrouver avec un mauvais type.

Voici un exemple qui utilise RECHERCHEV avec les données non triées dans la première colonne du tableau des types.

Pièce jointe 523622

Citation:

---

Envoyé par Simon5713

Je vais essayer mais je doute que ça réponde à mon problème en finalité.

---

Perso, je n'en doute pas un instant, si tu mets en place une bonne conception de classeur. On peut réaliser le travail par vba si la manipulation s'inscrit dans un processus de préparation plus complet, mais la structure à mettre en place est celle que je préconise si tu veux pouvoir évoluer avec ton classeur à moindre coût ;)

Nul doute que des solutions full vba existent et peuvent se greffer sur ta conception avec les quatre listes séparées, mais elles seront toujours plus coûteuses qu'une bonne conception et qu'une bonne utilisation des outils natifs d'Excel, surtout pour un non-informaticien...

Comme tu le vois ici, si tu doit utiliser un nouveau préfixe avec un nouveau type, il te suffit de l'ajouter dans le tableau des types, et les codes qui vont correspondre à ce nouveau préfixe vont directement récupérer le nouveau type, sans que tu aies rien à modifier à la structure de ton classeur, et sans que tu doives utiliser ou modifier du code VBA.

Pièce jointe 523628


Qui plus est, les codes proposés utilisent le hard coding (l'utilisation de valeurs métier dans le code) et la structure de ton code dépend intimement de tes données (autant de ElseIf ou de Case qu'il y a de colonnes de types) et doit être modifiée lors de l'ajout d'un nouveau type.

Si vraiment on devait récupérer le type d'un code donné en VBA, on utiliserait alors une fonction perso qui s'appuierait sur Index/Equiv, histoire d'éviter la boucle. Attention, dans mon code, j'ai mis un _ dans la référence structurée (t_Types[_Code],0))

Code:

---

1 2 3 4 5 6

Function GetPrefix(Number As String) As String Dim Formula As String   Formula = "=INDEX(t_types[Type],MATCH(""" & Left(Number, 6) & """,t_types[_Code],0))" GetPrefix = Evaluate(Formula) End Function

---

Mais je pense qu'il faut d'abord que tu détermines si tu as besoin de VBA et si oui, à quel moment du traitement et pour faire quoi, de manière récurrente ou en one-shot...