VBA Find Bug

[jlclyde]

In a user form I am trying to find the text 1234. As soon as it gets
to the find line it bugs out. Any ideas?

Private Sub Enter_Click()
If DtBx = "" And ItemNum = "" Then
MsgBox "Must Enter Item Number"
ElseIf DtBx = "" Then
Cells.Find(What:="1234", After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlWhole, SearchOrder:=xlByColumns,
SearchDirection:=xlPrevious, MatchCase:= _
False, SearchFormat:=False).Activate
End If
End Sub
Thanks,
Jay

 

[Rick Rothstein]

Can you describe "bugs out" for us? Do you mean you are getting an error
message? If so, what does it say?

 

[jlclyde]

Can you describe "bugs out" for us? Do you mean you are getting an error?

Of course I mean gettign an error. Do you see anything obvious with
the find section of the code? This is the line that is in yellow when
I click debug.

Jay

 

[Rick Rothstein]

You didn't answer the most important question I asked... If so, what does it
(the error message) say?

By the way, in response to your "of course" comment... it is possible for
your errant code to have 'just ended' without producing an error message...
that is why I asked what you meant by "bugs out", so we here would know what
was actually happening.

--
Rick (MVP - Excel)

Can you describe "bugs out" for us? Do you mean you are getting an error?

Of course I mean gettign an error. Do you see anything obvious with
the find section of the code? This is the line that is in yellow when
I click debug.

Jay

 

[Chip Pearson]

The problem most likely is that you append the Activate method to the
Find method. Find returns a Range object pointing to the cell in which
the data was found. However, if the value was not found, Find returns
Nothing, and your code still tries to Activate that. You can't do
anything with a Nothing. A better approach is:

Dim FoundCell As Range
Set FoundCell = Cells.Find(....)
If FoundCell Is Nothing Then
' value not found. do something
Else
' value was found
FoundCell.Activate
End If

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[jlclyde]

The problem most likely is that you append the Activate method to the
Find method. Find returns a Range object pointing to the cell in which
the data was found. However, if the value was not found, Find returns
Nothing, and your code still tries to Activate that. You can't do
anything with a Nothing.  A better approach is:

Dim FoundCell As Range
Set FoundCell = Cells.Find(....)
If FoundCell Is Nothing Then
        ' value not found. do something
Else
        ' value was found
        FoundCell.Activate
End If

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
    Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLCwww.cpearson.com
(email on web site)




- Show quoted text -

Thanks Chip your response was very insightful and helps me on my way
to fixing the problem.
Jay