find blanks crashes on error
 

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

find blanks crashes on error
 

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


"teepee" wrote:

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

find blanks crashes on error
 

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

The rest of the macro is very very long and really wouldn't help you as it
doen't reference this section

find blanks crashes on error
 

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


teepee wrote:

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


--

Dave Peterson

find blanks crashes on error
 

"Dave Peterson" wrote 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

Many thanks Dave. That was a most effective solution. I'm in your debt as
ever.