VBA Find Bug
 

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

VBA Find Bug
 

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

--
Rick (MVP - Excel)


"jlclyde" wrote in message
...
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

VBA Find Bug
 

On Aug 27, 1:29*pm, "Rick Rothstein"
wrote:
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

VBA Find Bug
 

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)


"jlclyde" wrote in message
...
On Aug 27, 1:29 pm, "Rick Rothstein"
wrote:
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

VBA Find Bug
 

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)


On Thu, 27 Aug 2009 11:22:57 -0700 (PDT), jlclyde
wrote:

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

VBA Find Bug
 

On Aug 27, 2:21*pm, Chip Pearson wrote:
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)

On Thu, 27 Aug 2009 11:22:57 -0700 (PDT), jlclyde



wrote:
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- Hide quoted text -

- Show quoted text -

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