Find generates error message

J

James Houston

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
 
V

Vasant Nanavati

Try:

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

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

Jim Rech

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

SOS

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
 
Top