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.