If Not IsError sometimes works...

I have some code that only works sometimes and is not reliable.
I don't know the exact syntax to use to make sure it will work all the time.

I am basically checking the value in cell E107 and comparing it with a range of values in
A20:A29 (named SYMB).

This is the code I'm using:

If Not IsError(Application.Match(Range("E107").Value, "SYMB", 0)) Then

I have gone so far as formatting cell E107 to make sure the contents are in the same format as the contents in range SYMB. There are only text values in the range and E107.

If anyone knows better code to use, I'd really appreciate knowing what it is.

Originally Posted by p24leclerc

try this:

Public Sub test()
Dim F_ound As Range
Set F_ound = Range("SYMB").Find(What:="zxc", LookAt:=xlWhole)
If Not F_ound Is Nothing Then
   MsgBox "FOUND"
Else
   MsgBox "NOT FOUND"
End If
End Sub

It looks at the whole content of the cells so it won't match partial word as "App" could match "Apple" or "Application"

Thanks for helping out!
As I mentioned in my other reply, the Application.Match function won't work with my Excel version so it's a good thing your solution doesn't include it.

I tested out your code with a couple of adjustments. Unfortunately neither worked.
First I substituted What:="zxc" with What:="E107". E107 is the cell that contains the string I am comparing in the range named SYMB.
Then I tried What:=E107
Neither produced reliable results.

I'm sure I need to make some simple adjustment.
I'll give you a more concrete example, to show you what I'm trying to do.

If cell E107 contains AAA, then I want to check range A20:A29, (which is named SYMB) and if there is an exact match, then DO This....
It seems simple enough.

Originally Posted by protonLeah

Another suggestion:
[CODE]Option Explicit
Sub test()
Dim R...

Thanks proton, I'm trying out p24leclerc's code right now.

Just before he posted it I discovered, much to my dismay, that my version of Excel (2002) does NOT have the Application.Match function. This is probably why it wasn't working for me.
It allowed me to state the function, but it wouldn't work.

I discovered this when I typed "Application. " and the list of functions came up.

Originally Posted by p24leclerc

here is themodification you are looking for:

Set F_ound = Range("SYMB").Find(What:=Range("E107"), LookAt:=xlWhole)

Merci beaucoup!
I haven't tested it out thoroughly yet, but it seems to be working.
I think it's the right modification.