Run-Time error '91': Object variable of With block variable not set

I've built a simple macro for excel and all is working very nicely - I've googled this error for an hour now and come up with many other people with the problem but no solutions...

the code:

Please Login or Register  to view this content.

Produces and error: "Run-Time error '91': Object variable of With block variable not set" if "txtfindvalue" is nowhere to be found. If it finds it, everything works nicely, if it doesnt.. i get that error.

Makes sense, but I'm fairly new and don't understand how to trap and error in VB. All I'd like to do is trap it and produce my own error that won't crash the program.. a simple msgbox that will make a warning sound and say "String not Found", etc.

If anyone has any experience with this, please let me know - thanks in advance!

try this:

On error goto ErrorHandler

'Your Code

exit sub

ErrorHandler:
if err.number = 91 then
msgbox "String not found"
'you have several options now. we can return to the previous code, like
this:
err.clear
resume next
'or just finish and exit like this:
exit sub
end if
'there are many things you can try if these don't work out

jammin1911 wrote:
> I've built a simple macro for excel and all is working very nicely -
> I've googled this error for an hour now and come up with many other
> people with the problem but no solutions...
>
> the code:
>
> Code:
> --------------------
>
> Cells.Find(What:=txtfindvalue, After:=ActiveCell, LookIn:=xlFormulas, _
> LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> MatchCase:=False, SearchFormat:=False).Activate
>
> --------------------
>
>
> Produces and error: "Run-Time error '91': Object variable of With block
> variable not set" if "txtfindvalue" is nowhere to be found. If it finds
> it, everything works nicely, if it doesnt.. i get that error.
>
> Makes sense, but I'm fairly new and don't understand how to trap and
> error in VB. All I'd like to do is trap it and produce my own error
> that won't crash the program.. a simple msgbox that will make a warning
> sound and say "String not Found", etc.
>
> If anyone has any experience with this, please let me know - thanks in
> advance!
>
>
> --
> jammin1911
> ------------------------------------------------------------------------
> jammin1911's Profile: http://www.excelforum.com/member.php...o&userid=35145
> View this thread: http://www.excelforum.com/showthread...hreadid=549019

You can test for the cell by:

dim FoundCell as range
set foundcell = Cells.Find(What:=txtfindvalue, After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

if foundcell is nothing then
'not found
else
foundcell.activate
end if

jammin1911 wrote:
>
> I've built a simple macro for excel and all is working very nicely -
> I've googled this error for an hour now and come up with many other
> people with the problem but no solutions...
>
> the code:
>
> Code:
> --------------------
>
> Cells.Find(What:=txtfindvalue, After:=ActiveCell, LookIn:=xlFormulas, _
> LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> MatchCase:=False, SearchFormat:=False).Activate
>
> --------------------
>
> Produces and error: "Run-Time error '91': Object variable of With block
> variable not set" if "txtfindvalue" is nowhere to be found. If it finds
> it, everything works nicely, if it doesnt.. i get that error.
>
> Makes sense, but I'm fairly new and don't understand how to trap and
> error in VB. All I'd like to do is trap it and produce my own error
> that won't crash the program.. a simple msgbox that will make a warning
> sound and say "String not Found", etc.
>
> If anyone has any experience with this, please let me know - thanks in
> advance!
>
> --
> jammin1911
> ------------------------------------------------------------------------
> jammin1911's Profile: http://www.excelforum.com/member.php...o&userid=35145
> View this thread: http://www.excelforum.com/showthread...hreadid=549019

--

Dave Peterson

this worked, thanks so much for the help guys!