VBA .Find question

M

Mark1

Can anybody tell me why I get the error "Unable to get the Find property of
the Range Class" here? I passed the variable a to MyTest5(b). I assumed
that MyTest5 would Find a in the range "Name". Help is appreciated!

Sub MyTest()

Dim a As String
Dim c

a = Worksheets("Input Sheet").Range("C3").Value
c = Application.Match(a, Worksheets("Sheet2").Range("Name"), 0)

If Not IsError(c) Then
Dim mymessage As String

mymessage = "This name has already been entered." & Chr(13) _
& "Would you like to retrieve that listing?"
Style = vbYesNo
Response = MsgBox(mymessage, Style)

If Response = vbYes Then
Call MyTest5(a)
Else: GoTo Option3
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub MyTest5(b As String)

With Worksheets(2).Range("Name")
Set d = .Find(b, , xlValues, xlWhole)
If Not d Is Nothing Then
 
J

JE McGimpsey

VBA is not finding a range named "Name" in Worksheets(2).

Note that Worksheets(2) will be different than Sheets("Sheet2") if the
sheet named "Sheet2" is not the second sheet from left to right in the
worksheet tabs.
 
M

Mark1

"Sheet2" and Sheet(2) are the same thing. There's only two sheets in the
workbook. And Column A is named "Name"
 
J

JE McGimpsey

In that case, it works for me. Here's my test workbook:

ftp://ftp.mcgimpsey.com/excel/mark1_demo.xls
 
M

Mark1

OK,
Here's something I discovered. When I run the macro from inside the VBE
using the F8 key, it works. However, when I run it using the command button
 
Top