Find generates error 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

Find generates error message
 

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

Find generates error message
 

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
|
|

Find generates error message
 

Hi gav,

On trying to emulate your problem I could only do so if the tex
"appendix" was not in sheet ("contract").

After adding "appendix" to a cell in sheet ("contract") the code ra
fine no matter the size of the range "appendix".

Regards

Seamu

--
Message posted from http://www.ExcelForum.com