Can anyone tell me why the following code

With xlSHT.Range("c1")
Set c = .Find(sName, "c1", xlValues, , xlByColumns, xlNext)
End With

Generates the error message: "unable to get the find property of the Range
class"

xlSHT is a worksheet object that has been set to a worksheet in an open
workbook. "c" is a range object. Any help would be greatly appriciated.

Jim

Try:

Set c = .Find(sName, Range("C1"), xlValues, , xlByColumns, xlNext)

The second argument should be a range, not a range address.

--

Vasant


"James Houston" wrote in message
...
Can anyone tell me why the following code

With xlSHT.Range("c1")
Set c = .Find(sName, "c1", xlValues, , xlByColumns, xlNext)
End With

Generates the error message: "unable to get the find property of the Range
class"

xlSHT is a worksheet object that has been set to a worksheet in an open
workbook. "c" is a range object. Any help would be greatly appriciated.

Jim

You have two problems it seems to me. One is that your With statement
limits the Find to one cell: C1. I assume you want to search more than that
one cell. Second, the Find method's second argument is a range; you have a
string ("c1").

So maybe you want something like this:

Sub test()
Dim c As Range
Dim xlSHT As Worksheet
Dim sName As String
Set xlSHT = ActiveSheet
sName = "abc"
With xlSHT
Set c = Cells.Find(sName, .Range("C1"), xlValues, , xlByColumns,
xlNext)
End With
If Not c Is Nothing Then MsgBox c.Address
End Sub


--
Jim Rech
Excel MVP
"James Houston" wrote in message
...
| Can anyone tell me why the following code
|
| With xlSHT.Range("c1")
| Set c = .Find(sName, "c1", xlValues, , xlByColumns, xlNext)
| End With
|
| Generates the error message: "unable to get the find property of the Range
| class"
|
| xlSHT is a worksheet object that has been set to a worksheet in an open
| workbook. "c" is a range object. Any help would be greatly appriciated.
|
| Jim
|
|