Runtime Error 91

[HSalim[MVP]]

Hi
I am trying to use the Find method in Excel (2003)

I have appended a code snippet below, which works fine if the value can be
found in a cell. If that value is not found, Excel returns a runtime error.
Any ideas why this might be so?

Regards
Habib

----------------------

Range("E" & F & ":E" & L).Select
FrstRow = Selection.Find(What:="CIB", LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)

 

[Dave Peterson]

..find returns a range.

So FrstRow should be dimmed as a Range.

Dim FrstRow as Range

And with ranges, you'll have to use the Set statement.

Set FrstRow = selection.find(...)

Later you can tell if you found it or not:

if frstrow is nothing then
'not found
else
'it was found
end if

 

[Rowan Drummond]

You can use a range object to test if the value has been found e.g:

Dim fndCell As Range
Dim FrstRow As Long
With Range("E" & f & ":E" & l)
Set fndCell = .Find(What:="CIB", LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext _
, MatchCase:=False, SearchFormat:=False)
End With
If Not fndCell Is Nothing Then
FrstRow = fndCell.Row
'continue
Else
MsgBox "Not found"
End If

Hope this helps
Rowan

 

[HSalim[MVP]]

Hello Dave and Rowan,
Thanks for the quick response.
I did see the set syntax in the help files but I was not getting anywhere
with that either till i explicitly declared a range.

I've been experiencing intermittent difficulty with intellisense not
working - any idea why that might be happening. I am able to compile the
priject so i do not have any missing references.

Thanks again for the assistance.
Regards
Habib

 

[Rowan Drummond]

Hi Habib

It is generally a good idea to use Option Explicit at the top of every
module (if you set Tools>Options>Editor>Require Variable Declaration
this will be done automatically for you). You will then be forced to
declare all variables and you should declare them explicitly wherever
possible.

This may also explain your intellisense issues. If you don't declare the
range or declare it as a variant then you will not have access to
intellisense for that variable/object. As soon as you declare it as a
range you should have intellisense.

Hope this helps
Rowan

 

[HSalim[MVP]]

Rowan,
Thanks again!.
Regards
Habib

: Hi Habib
:
: It is generally a good idea to use Option Explicit at the top of every
: module (if you set Tools>Options>Editor>Require Variable Declaration
: this will be done automatically for you). You will then be forced to
: declare all variables and you should declare them explicitly wherever
: possible.
:
: This may also explain your intellisense issues. If you don't declare the
: range or declare it as a variant then you will not have access to
: intellisense for that variable/object. As soon as you declare it as a
: range you should have intellisense.
:
: Hope this helps
: Rowan
:
: HSalim[MVP] wrote:
: > Hello Dave and Rowan,
: > Thanks for the quick response.
: > I did see the set syntax in the help files but I was not getting
anywhere
: > with that either till i explicitly declared a range.
: >
: > I've been experiencing intermittent difficulty with intellisense not
: > working - any idea why that might be happening. I am able to compile
the
: > priject so i do not have any missing references.
: >
: > Thanks again for the assistance.
: > Regards
: > Habib
: >
: >