Discussion :

[Naunaud59]

Bonjour à toutes et tous,

J'ai besoin d'aide pour résoudre un problème que je rencontre sur une macro (je suis débutant en VBA, j'ai trouvé cette macro sur ce forum et tenté de l'adapter mais sans succès)
J'ai passé environ 8 heures en ayant d'utiliser une boucle avec Do / Loop mais la boucle semblait sans fin et faisait planter Excel j'ai donc abandonné cette idée.

Mon problème (je vous joins un fichier exemple) :

J'aimerai créer une base de donnée dans la "colonne F de la feuille 1" et avec la méthode Find trouver dans la "colonne D de la feuille 2" (si la valeur existe) et ensuite utiliser Offset pour obtenir le résultat escompté.

Dans le code ci dessous, la méthode Find s'arrête à la première valeur trouvée. J'aimerai la modifier pour qu'elle fonctionne pour toute les lignes de la colonne. Ne faites pas attention aux Range (qui sont fixe pour l'exemple), je les modifierai plus tard pour qu'elles soient variables en fonction du nombre de lignes variables de mon fichier.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Sub Macro1()
 
Dim b As Range
 
    Set W1 = Worksheets("Feuil1")
    Set W2 = Worksheets("Feuil2")
 
    Set sh1 = W1.Range("F2:F5")
    Set sh2 = W2.Range("F1:F40")
 
    For Each c In sh1
 
         Set b = sh2.Find(c)
 
        If Not b Is Nothing Then
 
            b.Offset(0, 1) = c.Offset(0, 1)
 
        End If
 
    Next
 
End Sub

Merci d'avance pour votre aide,

Naunaud59

[Invité]

Bonjour,

Il faut faire une deuxième boucle For Each.. Next sur Sh2 à l'intérieur de la première et tester le cas où C1 = C2.

[riaolle]

Bonjour,
Il faut utiliser la méthode FindNext et une boucle While ou Do Loop

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Sub Macro1()
 
Dim b As Range, W1 As Worksheet, W2 As Worksheet, sh1 As Range, sh2 As Range
Dim firstCel As String 
    Set W1 = Worksheets("Feuil1")
    Set W2 = Worksheets("Feuil2")
    Set sh1 = W1.Range("F2:F5")             '===> un peu étrange d'utiliser sh --> sh est utilisé pour sheet normalement (donc un workshet)
    Set sh2 = W2.Range("F1:F40")
 
    For Each c In sh1
         Set b = sh2.Find(c.Value)
         firstCel = b.Address
         Do
            b.Offset(0, 1) = c.Offset(0, 1)
            Set b = sh2.FindNext(b)
         Loop While Not b Is Nothing And b.Address <> firstCel
    Next c
End Sub

A tester!
Au passage tu peux économiser des variables/objets :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Set sh1 = Worksheets("Feuil1").Range("F2:F5")             '===> un peu étrange d'utiliser sh --> sh est utilisé pour sheet normalement (donc un workshet)
    Set sh2 = Worksheets("Feuil2").Range("F1:F40")

C'est pas grave, mais ça économise des lignes

[Naunaud59]

Bonjour à vous deux et merci pour votre temps.

Riaolle merci pour tes bonnes remarques et effectivement nous pouvons regrouper les objets, c'est moins le bazard ah ah.

J'avais effectivement tenter d'utiliser la méthode FindNext et une boucle Do Loop (qui ressemblait donc beaucoup à ton code) mais en vain.

J'ai testé ton code et j'ai une erreur sur firstCel = b.address <Variable objet ou variable de bloc With non définie>

J'ai rajouté Set devant firstCel = b.address et maintenant j'ai une erreur de compilation : Objet requis.

[eriiic]

Bonjour,

j'ai une erreur sur firstCel = b.address

Est-ce que b a bien été trouvé et n'est pas =Nothing ?

J'ai rajouté Set devant firstCel = b.address et maintenant j'ai une erreur de compilation : Objet requis.

firstCel est String et Set est réservé aux objets
eric

[Naunaud59]

Bonjour Eric,

Est-ce que b a bien été trouvé et n'est pas =Nothing ?

Lorsque je pointe ma souris sur firstCel, firstCel ="" et b.address = <Variable objet ou variable de bloc With non définie> .

Mais je ne comprends pas trop le résultat qu'est censé me donner b.address? Si je cherche le mot "ROUGE" dans la cellule qui contient "L'OISEAU EST ROUGE" est ce que la valeur de b.address est ROUGE ou L'OISEAU EST ROUGE?

[Invité]

Bonjour,

Je viens d'ouvrir ton fichier. Dans ton code, tu fais une recherche sur l'aire F1:F40 de l'onglet Feuil2 mais la colonne F est vide. L'aire de recherche devrait être la colonne D.

Par ailleurs, je ne comprends pas bien la logique. Il peut y avoir plusieurs valeurs correspondant à la valeur cherchée, dans ce cas quelle valeur doit être sélectionnée ?

Nb : Dans ma première réponse, je proposais de faire une deuxième boucle, sous-entendu que la valeur trouvée dans la feuille 2 était unique.

[Invité]

Est ce que ce code résout votre problème ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
Sub Macro2()
 
Dim AireDeRecherche As Range, CelluleDeRecherche As Range
Dim AireDestination As Range, CelluleDestination As Range
 
    Set AireDeRecherche = Sheets("Feuil1").Range("F2:F5")
    Set AireDestination = Sheets("Feuil2").Range("D1:D40")
 
    For Each CelluleDestination In AireDestination
        If CelluleDestination <> "" Then
           For Each CelluleDeRecherche In AireDeRecherche
               If InStr(1, LCase(CelluleDeRecherche), LCase(CelluleDestination), vbTextCompare) > 0 Then
                  CelluleDestination.Offset(0, 1) = CelluleDeRecherche.Offset(0, 1)
               End If
           Next
        End If
    Next CelluleDestination
 
    Set AireDeRecherche = Nothing
    Set AireDestination = Nothing
 
End Sub

C'est normal que le programme s'arrêtait à la première occurrence trouvée, car vous avez inversé l'aire de recherche avec l'aire de destination. Mon code ne tient pas compte de la casse, donc à modifier le cas échéant.

[Naunaud59]

Merci beaucoup pour ton aide précieuse!

Ton code fonctionne mais uniquement lorsque la valeur dans la colonne D de la feuille 2 est exacte

Si je cherche "BLEU" et que la valeur de la cellule D est "BLEU CIEL" ou "LE CIEL EST BLEU" j'aimerai que ce soit pris en compte.

Pourtant ton code (ci-dessous) semble correcte. Le 1 signifie bien que je cherche le texte dans toute la chaîne de caractère...

Tu as une idée d'ou ça peut venir?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

If InStr(1, LCase(CelluleDeRecherche), LCase(CelluleDestination), vbTextCompare) > 0 Then

[Invité]

Il faut inverser :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
               If InStr(1, LCase(CelluleDestination), LCase(CelluleDeRecherche), vbTextCompare) > 0 Then
                  CelluleDestination.Offset(0, 1) = CelluleDeRecherche.Offset(0, 1)
               End If

[Naunaud59]

Ton code s’exécute parfaitement mais une seule fois.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 Set AireDeRecherche = Nothing
 Set AireDestination = Nothing

Doit-on modifier ces deux lignes?

Merci encore, je sens qu'on y est presque !

[Invité]

Tu fais quoi quand il y a le mot "BLEU" deux fois dans la colonne D ?

[Naunaud59]

C'est peu probable mais pas impossible.

Il faudrait que ce soit également pris en compte, peut importe le nombre de "BLEU" dans la cellule.

Par "ton code s’exécute qu'une seule fois je veux dire que la macro à fonctionné une fois puis ne fonctionne plus.

Je pense que ce code en est la cause :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 Set AireDeRecherche = Nothing
 Set AireDestination = Nothing

Est-il possible que la valeur reste Nothing lorsque je réutilise la macro?

[Invité]

Voilà ce que j'obtiens :

Pièce jointe 306004

[Naunaud59]

EUREEEKAAA

Après avoir testé ton programme et voyant que ça marchait parfaitement, j'ai rajouté des données en colonne F dans mon tableau de la Feuil1 pour tester.
J'avais donc élargi la plage de données comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Set AireDeRecherche = Sheets("Feuil1").Range("F2:F50")

Mes cellules F10 à F50 étant vide, la boucle plantait!

J'ai donc modifié comme ceci afin d'aller de F2 à la dernière ligne pleine de la colonne F:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Set AireDeRecherche = Sheets("Feuil1").Range("F2:F" & Range("F65536").End(xlUp).Row)

En revanche j'ai un dernier problème. La macro ne fonctionne que si je lance depuis la Feuil1. Si la Feuil2 est activée cela me supprime toute ma colonne E.
J'ai cherché ce qui pourrai en être la cause mais je ne trouve pas...
Ce n'est pas bien grave je peux contourner le problème par le code ci-dessous mais j'aimerai tout de même comprendre... :

En me relisant j'ai compris que le problème venait d'ici :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Set AireDeRecherche = Sheets("Feuil1").Range("F2:F" & Range("F65536").End(xlUp).Row)

A modifier en :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Set AireDeRecherche = Sheets("Feuil1").Range("F2:F" & Sheets("Feuil1").Range("F65536").End(xlUp).Row)

Un énorme merci pour le temps que tu m'as accordé, tout ceci va beaucoup m'aider et pourra être repris dans plusieurs programmes à venir.

Super boulot !

RESOLU

[Invité]

En revanche j'ai un dernier problème. La macro ne fonctionne que si je lance depuis la Feuil1. Si la Feuil2 est activée cela me supprime toute ma colonne E.

Le code fourni est indépendant de l'emplacement de la cellule active. Bien entendu, c'est évident que la colonne récupérant les résultats va être rafraîchie. Pour éviter qu'une cellule déjà remplie soit modifiée, il faut en tester le cas.