Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
where to put results of find operation in find and replace functio | Excel Worksheet Functions | |||
Despite data existing in Excel 2002 spreadsheet Find doesn't find | Excel Discussion (Misc queries) | |||
'find' somtimes can't find numbers. I folowd the 'help' instructi. | Excel Worksheet Functions | |||
How do I find a file/spreadsheet that Excel says is Already open but I can't find it? | Excel Discussion (Misc queries) |