Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / April 2008

Tip: Looking for answers? Try searching our database.

find blanks crashes on error

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
teepee - 16 Apr 2008 08:45 GMT
I have a piece of code that doesn't respond to error handling:

On Error GoTo question_end

 secondblank = Range("a" & firstqunw & ":a100").Find(What:="",
LookAt:=xlWhole).Row

It finds the second blank space in a column. When the column is empty it
fails (as I would expect) but then instead of error handling it crashes
with:

run-time error 91 object variable or With block variable not set

Any help or suggestions gratefully received

tp

full code is

If ActiveSheet.UsedRange.Count < 2 Then
 MsgBox "There are no results available yet for that sheet"
Else

 Dim firstblank As String
 Dim firstqunw As String
 Dim secondblank As String
 Dim firstqwei As String

 On Error GoTo question_error

 firstblank = Columns("a:a").Find(What:="", LookAt:=xlWhole).Row
 firstqunw = firstblank + 2
 Cells.FindNext(After:=ActiveCell).Activate

 On Error GoTo question_end

 secondblank = Range("a" & firstqunw & ":a100").Find(What:="",
LookAt:=xlWhole).Row
 Cells.FindNext(After:=ActiveCell).Activate
 firstqwei = secondblank + 1

 End If
Mike H - 16 Apr 2008 09:38 GMT
Hi,

The snippet you provide can't be the full code because it simply wouldn't
complile like that so you couldn't get a runtime error, can we see more of
the code

Mike

> I have a piece of code that doesn't respond to error handling:
>
[quoted text clipped - 39 lines]
>   End If
>  
teepee - 16 Apr 2008 10:07 GMT
> Hi,
>
[quoted text clipped - 3 lines]
>
> Mike

The rest of the macro is very very long and really wouldn't help you as it
doen't reference this section
Dave Peterson - 16 Apr 2008 12:39 GMT
Maybe it's something in the error handling routine that you didn't share.

Personally, I'd use something like:

dim FirstBlankCell as range
set firstblankcell _
   = Range("a" & firstqunw & ":a100").Find(What:="", LookAt:=xlWhole)
'and I'd specify all the parms to the .find

if firstblankcell is nothing then
 'not found
 firstblank = 0 '???
else
 firstblank = firstblankcell.row
end if

> I have a piece of code that doesn't respond to error handling:
>
[quoted text clipped - 38 lines]
>
>   End If

Signature

Dave Peterson

teepee - 20 Apr 2008 18:59 GMT
"Dave Peterson" <petersod@verizonXSPAM.net> wrote > Personally, I'd use
something like:

> dim FirstBlankCell as range
> set firstblankcell _
[quoted text clipped - 7 lines]
>  firstblank = firstblankcell.row
> end if

Many thanks Dave. That was a most effective solution. I'm in your debt as
ever.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.