Insérer MsgBox si valeur recherchée n'est pas écrite correctement

[nicolo9]

Bonjour,

J'ai une question sur la fonction Find, fonction qui m'est très pratique dans le cadre où je fais des inscriptions et la fonction Find me sert à retrouver rapidement un nom.

Cependant, lorsque l'on ne tape pas la valeur exacte qui se trouve dans la colonne à rechercher, il met le message d'erreur suivant :
Erreur d'éxécution '91' : Variable objet ou variable de bloc With non définie.

Comment permettre à la recherche d'afficher par exemple un MsgBox si le mot recherché ne se trouve pas dans la colonne (ou alors qu'il y a une syntaxe différente).
Pour le MsgBox, par exemple on pourrait avoir :

MsgBox = "La valeur recherchée ne se situe pas dans le tableau"

Mon code est le suivant :

Set MyRange = Range("MaValeurCherchée")
     'Nom de ma case de recherche
Set x = Sheets(1).Range("A:A").Find(MyRange.Value, , xlValues, xlWhole, , , False)
     'Recherche dans la colonne A:A
If Not x Is Nothing Then Range(x.Address).Select
     'Place le curseur sur la valeur retrouvée

Ce code est bon mais si j'ai le malheur de taper par exemple : "Chloe" ou "Cloé" au lieu de "Chloé", le message d'erreur précédent s'affiche.

Merci de votre aide.

 

[nicolo9]

Good afternoon,

I have a question on the function Find, function which is me strongly practical because I make inscriptions and this function is used to find a name fastly.

However, when I don't write the exact value that is in the column to search, it displays the following error message :
Execution error '91': Object variable or With block variable not defined.

How to display, for example, a MsgBox if the searched word is'nt in the column (or if there is a different syntax).
For the MsgBox, we can have :

MsgBox = "This searched value is'nt in the table"

My code is like this :

Set MyRange = Range("MySearchedValue")
          'Name of my cell
Set x = Sheets(1).Range("A:A").Find(MyRange.Value, , xlValues, xlWhole, , , False)
          'Search in the column A:A
If Not x Is Nothing Then Range(x.Address).Select
          'Put the cursor on the searched value

This code is OK but if I write, for example : "Chloe" or "Cloé" instead of "Chloé", the previous error message displays.


Thanks.

 

[Fishboy]

Good afternoon,

I have a question on the function Find, function which is me strongly practical because I make inscriptions and this function is used to find a name fastly.

However, when I don't write the exact value that is in the column to search, it displays the following error message :
Execution error '91': Object variable or With block variable not defined.

How to display, for example, a MsgBox if the searched word is'nt in the column (or if there is a different syntax).
For the MsgBox, we can have :

MsgBox = "This searched value is'nt in the table"

My code is like this :

Set MyRange = Range("MySearchedValue")
          'Name of my cell
Set x = Sheets(1).Range("A:A").Find(MyRange.Value, , xlValues, xlWhole, , , False)
          'Search in the column A:A
If Not x Is Nothing Then Range(x.Address).Select
          'Put the cursor on the searched value

This code is OK but if I write, for example : "Chloe" or "Cloé" instead of "Chloé", the previous error message displays.


Thanks.

Hi Nicolo9,

I actually used an online service to translate your original post and even had an answer for you before I saw you post in English!

You can simply add the following line of code in to get the desired results:

Sub VotreMacro()
Set MyRange = Range("MaValeurCherchée")
     'Nom de ma case de recherche
Set x = Sheets(1).Range("A:A").Find(MyRange.Value, , xlValues, xlWhole, , , False)
     'Recherche dans la colonne A:A
If Not x Is Nothing Then Range(x.Address).Select
     'Place le curseur sur la valeur retrouvée
If x Is Nothing Then MsgBox "La valeur recherchée ne se situe pas dans le tableau"
End Sub

 

[nicolo9]

Hi!

LOL, your code is OK !!
Thank you too much.

I will test on several values to be sure.

Best.

 

[Fishboy]

Hi!

LOL, your code is OK !!
Thank you too much.

I will test on several values to be sure.

Best.

Il a été mon plaisir (I hope the online translation got that right!)

 

[nicolo9]

We says "C'était avec plaisir" in french.
It was an easy code but I found not...

 

[nicolo9]

Where I can find "Solved" or "Réslou" in French to close the discussion ?
Thanks

 

[Fishboy]

Where I can find "Solved" or "Réslou" in French to close the discussion ?
Thanks

Glad to hear this worked. There is no "Solved" or equivalent status on these forums, but hopefully your "Like" and positive feedback will help others who need similar help find the solution here

 

[nicolo9]

OK, this forum is solved.

OK, ce forum est résolu.