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

"JE McGimpsey" wrote:

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.


In article ,
"Mark1" wrote:

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