three line program...

[rci]

Hi all...

why would this fail?

Sheets("Sheet1").Select
Range("A1").Select
Result = Cells.Find("12345") '<-error


Runtime Error 91: Object variable or With block variable not set.

Many thanks!

Mike

 

[Guest]

Find returns a range object. So you have to "Set" your variable = to that
range.

Sub test()
Dim Result As Range

Set Result = Sheet1.Cells.Find("1234")
If Not Result Is Nothing Then MsgBox Result.Value & vbTab & Result.Address
End Sub

HTH

 

[JE McGimpsey]

One reason is that you're assigning the default property (.Value) of the
range returned by the Cells.Find() method to Result. If Cells.Find()
doesn't find a cell with the target "12345", then there's no object to
get the property from. Hence the Object Variable not set error...

A second possibility is that if you run this from a button in XL97, you
need to make sure the button's Take Focus on Click property is set to
false.

Better:

Dim rFound As Range
Dim Result As Variant

Set rFound = Sheets("Sheet1").Cells.Find("12345")
If Not rFound Is Nothing Then
Result = rFound.Value
Else
MsgBox "Not Found"
End If

Note that you don't have to select anything.

 

[Chris W.]

Find returns a Range object, so you need to use the Set
statement. You might try declaring it first also. its
usually better to do early binding.

Dim Result as Range

Sheets("Sheet1").Select
Range("A1").Select
Set Result = Cells.Find("12345")