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

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

"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

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

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


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

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
on my spreadsheet, it doesn't work. Any ideas?

"JE McGimpsey" wrote:

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

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


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

Set the Button's TakeFocusOnClick property to False.

In article ,
"Mark1" wrote:

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
on my spreadsheet, it doesn't work. Any ideas?

Sweet mercy, it worked!!!!!!
Thanks a million!

"JE McGimpsey" wrote:

Set the Button's TakeFocusOnClick property to False.

In article ,
"Mark1" wrote:

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
on my spreadsheet, it doesn't work. Any ideas?