Variable in evaluate method and match function not being recognized

[SJ-NY]

Hi
I am using the following formula to determine if a value is in a list. The list is in a range named “Region”.</SPAN>

Sub OnTheList()</SPAN>
Dim a As Long 'variable assigned the results of the evaluate method</SPAN>
Dim b As String 'variable assigned the value from the inputbox
</SPAN>
b = InputBox("Enter the name of the sheet you want to add")
a = Evaluate("=IFERROR(MATCH(b,region,0),0)")</SPAN>

End Sub</SPAN>

The user through an inputbox is prompted to enter a value. The value is assigned to a variable called “b” Variable “b” is then used in the evaluate method in the next line of code.</SPAN>
The match formula is not recognizing the variable “b”. The evaluate formula does work when I hard code a value instead of using a variable.</SPAN>
I am obviously overlooking something, any help would be greatly appreciated</SPAN>
Thanks</SPAN>

Steve</SPAN>

 

[p45cal]

maybe

a = Evaluate("=IFERROR(MATCH(""" & b & """,region,0),0)")

?

 

[p45cal]

deleted

 

[SJ-NY]

P45Cal
thank you for responding. I tried variations of your suggestion but could not get it to work. Right know I am going to try using a helper cell so I can continue moving ahead with the procedure. But I will keep trying to get the evaluate code to work.

Thanks
Steve